How to Lock Cells in Excel

When you build a complex spreadsheet in Microsoft Excel—complete with intricate formulas, custom formatting, and linked data—the last thing you want is for a colleague to accidentally type over a crucial calculation. Protecting your work is essential when sharing documents. Excel provides a robust security feature that allows you to “lock” specific cells. When a cell is locked, users cannot edit the text, delete the formula, or change the formatting. They can only view the data, ensuring the mathematical integrity of your spreadsheet remains intact.

The Counter-Intuitive Nature of Excel Protection

The most confusing aspect of locking cells in Excel is the default behavior: By default, every single cell in a brand new Excel workbook is already designated as “Locked.”

However, this lock does absolutely nothing until you turn on the overarching “Protect Sheet” feature. Therefore, locking specific cells is a two-part process: first, you must unlock the cells you want people to type in (like data entry fields), and second, you turn on the master switch to protect the sheet, which activates the lock on everything else.

Step-by-Step: Locking Specific Cells

Imagine you have an invoice template. You want the user to type in the “Client Name” and “Amount” (Columns A and B), but you absolutely do not want them touching the “Total Tax” formula in Column C.

Part 1: Unlocking the Input Cells

1. Select the entire spreadsheet by clicking the small triangle in the extreme top-left corner of the grid (where row 1 and column A intersect), or press Ctrl + A.
2. Right-click anywhere on the spreadsheet and select Format Cells.
3. In the dialogue box that appears, navigate to the Protection tab on the far right.
4. Uncheck the box next to Locked. (This unlocks the entire spreadsheet).
5. Click OK.

Now, every cell is completely open. Next, you will lock only the specific formula cells.

Part 2: Locking the Formula Cells

1. Highlight the specific cells you want to protect (e.g., select all of Column C).
2. Right-click the highlighted area and select Format Cells again.
3. Go to the Protection tab.
4. Check the box next to Locked.
5. Click OK.

At this point, you have designated Column C as locked, but the lock is not yet active. You can still delete the formulas.

Part 3: Activating the Protection

1. Go to the Review tab on the ribbon at the top of the screen.
2. In the ‘Protect’ group, click Protect Sheet.
3. A dialogue box will appear asking for a password. Typing a password is optional. (If you don’t use a password, anyone can simply click ‘Unprotect Sheet’ to turn the lock off. If you use a password, they cannot bypass it).
4. Below the password field, there is a list of permissions. By default, “Select locked cells” and “Select unlocked cells” are checked. Leave these as they are.
5. Click OK (and re-enter your password to confirm if you chose one).

Testing Your Protection

Your spreadsheet is now secure. Test it out. Click on a cell in Column A (the unlocked area) and type a new name; Excel allows it. Now, click on your formula in Column C and press the Delete key. Excel will instantly pop up a warning message stating, “The cell or chart you’re trying to change is on a protected sheet.”

To make changes to your formulas in the future, you must go back to the Review tab, click Unprotect Sheet (enter the password if prompted), make your edits, and then re-protect the sheet before sending it out.