How to Lock Cells in Microsoft Excel

Microsoft Excel is a powerful collaborative tool, but sharing a complex spreadsheet with a colleague often leads to disaster. You might spend hours crafting intricate formulas to calculate quarterly projections, only for a coworker to accidentally click the wrong cell, hit ‘Delete’, and ruin the entire sheet. To prevent this, you need to protect your work. However, you can’t simply lock the entire document, because your coworker still needs to input their raw data. The solution is to Lock specific cells containing your formulas and headers, while explicitly leaving the data-entry cells unlocked and editable.

The Counter-Intuitive Logic of Excel Locks

The process of locking cells in Excel confuses many beginners because it operates backward from what you might expect. By default, every single cell in a brand new Excel workbook is already locked. However, that lock does absolutely nothing until you turn on the global “Protect Sheet” switch. Therefore, to protect only some cells, you must first unlock the cells you want people to edit, and then throw the global switch to lock everything else down.

Step 1: Unlocking the Editable Cells

First, we must identify the specific cells where your coworker is allowed to type (for example, a column for ‘January Sales’).

1. Use your mouse to highlight all the cells you want to remain editable. You can hold down the Ctrl key (or Command on Mac) to highlight multiple non-adjacent blocks of cells.
2. Right-click anywhere inside the highlighted area.
3. Select Format Cells… from the drop-down menu.
4. A dialog box will appear. Click the tab labeled Protection on the far right.
5. You will see a checkbox labeled Locked. It will be checked by default. Uncheck the box.
6. Click OK.

Visually, nothing on your spreadsheet has changed, but those specific cells are now vulnerable when the global lock is applied.

Step 2: Engaging the Global Sheet Protection

Now that your editable cells are unlocked, you must activate the shield that protects the rest of your hard work.

1. Navigate to the Review tab on the ribbon at the top of the screen.
2. In the ‘Protect’ group, click the Protect Sheet button.
3. A new dialog box will appear. At the very top, ensure the box labeled “Protect worksheet and contents of locked cells” is checked.
4. (Optional but Recommended): In the “Password to unprotect sheet” box, type a password. If you leave this blank, anyone can simply click the ‘Unprotect Sheet’ button and break your locks.
5. Look at the scrolling list of permissions below. By default, “Select locked cells” and “Select unlocked cells” are checked. This is usually fine, as it allows users to click around, but prevents them from typing in the locked areas.
6. Click OK. (If you entered a password, Excel will ask you to retype it to confirm).

The lock is now active. If your coworker tries to type over your formulas, Excel will instantly block them with a harsh warning popup. They will only be able to enter data into the specific cells you unlocked in Step 1.