fbpx

Unlock 4,242+ video lessons with a single membership. Get your free 7-day trial now.

How to Lock Cells in Excel

Sometimes when doing complex operations in Excel, you don’t need to edit a cell, especially when doing a lot of formulas or processes in your spreadsheet. For that kind of situation, you can protect a cell from being edited by locking them. So, to prevent moving or deleting something you don’t want, here are two basic methods to lock cells in Excel.

Lock specific cells or ranges

Before starting, you must know that Excel locks the whole worksheet when you open a new file. You can verify this by selecting a cell of a group of cells, then right-clicking and picking the “Format cells” option just below “Insert Comment” at the end of the context menu.

Note: You can enable this option by its shortcut hitting Ctrl+1.

Afterward, you’ll get another context window in which you can click the “Protection” tab (which is the last one) and notice that the option “Locked” is already checked by default. So if you want to disable a cell so as not to edit it at all, you first need to unlock all of them to lock a specific range. 

  1. Unlock all the cells:

Maybe at this point, you’re wondering why you need to unlock everything to lock something again. Well, it’s because locking and blocking cells have no effect until the worksheet is protected. Thus, you can do the following to start:

  • Selecting all.
    • Go to the very left corner between Column A and Row 1.
    • Otherwise, you can press Ctrl + A.
  • Right-click to bring up the context menu and select “Format Cells.”
  • In the “Protection” tab, uncheck the “Locked” option from the checkbox.
  1. Now, to lock a specific range of cells, you need to follow the same steps above, but this time, enable the “Locked” option only for those cells you require.
  2. Then, go to the “Review” tab from the main panel of Excel.
  3. Search the Protect Command Group on the right side.
  4. Click on “Protect Sheet”
  5. Perfect, now on the first field, you will have to specify a password to unprotect the sheet, and then you’ll need to confirm it by writing it again.

Warning: you must be sure about your password because there is no system to restore lost or forgotten passwords.

  1.  Click “OK” when you’re done.

Remember that you can edit only the unlocked cells as you consider better. However, for locked cells, you must enable the specific actions you need in the Protect Sheet dialog box, which is the same you will have to unprotect the cell.

Lock Formula Cells

There’s a way in which you can lock all cells containing formulas so that people cannot delete or adjust them in case of sharing your spreadsheet with others:

  1. First, you need to unlock all the cells.
  2. If you have several procedures, you can ask Excel to find all those cells that contain formulas by doing the following:
    • Go to the “Home” tab, which is the first one.
    • Search the Editing command group (at the right of the panel).
    • Click the “Find & Select” command.
    • In the dropdown menu, you can choose “Formulas.”
    • Excel will highlight all cells that fit into this configuration.

Note: To be more specific about the formula you need to lock, you must pick “Go to Special” in the context menu of the “Find & Select” command. Then you can choose the Formulas button and select the formulas that contain numbers, text, errors, etc.

  1. Lock the highlighted cells just as in step two of the previous method.

Note: You can also check the “Hidden” option if you don’t want someone to see the formula.

  1. Check all boxes in the Protect Sheet dialog that you can find in the Protect Command Group within the “Review” Tab.

Perfect! Now, to adjust or delete the formula cells, you should click the Unprotect sheet command and enter the password if you have one.

Access 4,242+ video lessons. Instantly.
Start Your Free Trial  →
7-day trial
No commitment

Advance your skills with thousands of professional classes made by experts

4,242+
Lessons
2,351+
Hours of video content
56+
Different skills
crossmenuarrow-right