How to Lock Cells in Excel: A Step-by-Step Guide

When multiple people work on the same worksheet, someone can accidentally edit an important formula or value. This can easily be prevented by locking specific cells while leaving the rest of the worksheet editable. In this article, you will learn how to lock cells in Excel.

Key Takeaways:

• Lock important cells to prevent accidental edits.
• Unlock input cells before protecting the worksheet.
• Protect formulas without hiding them.
• Use passwords for additional worksheet security.
• Unprotect the sheet anytime to make changes.

 

Every cell in Excel is marked as Locked by default. However, this setting does nothing until you protect the worksheet.

Once worksheet protection is enabled:

• Locked cells cannot be edited.
• Unlocked cells remain editable.
• Formulas and important data stay protected.
• Users can still interact with allowed cells.

Think of it like locking the doors of a house. The locks already exist, but they only work after you lock the door.

 

Step-by-Step Guide to Lock Cells in Excel

STEP 1: Select all of the cells by clicking the upper left corner:

STEP 2: Right-click any cell and select Format Cells:

STEP 3: Ensure Locked is unticked.  This will unlock our entire sheet.  Click OK.

STEP 4: Right-click on our target cell and select Format Cells:

STEP 5: Ensure Locked is ticked this time. This will lock our target cell. Click OK.

STEP 6: Now it is time to protect our Excel sheet and see the locking in action!

Right-click on the Worksheet Name and select Protect Sheet (or go to the ribbon menu and select Review > Protect Sheet)

STEP 7: Type in a password and Click OK.  In our example, I typed in excel as the password.

STEP 8: Retype the password and Click OK.  

STEP 9: If you try editing your target cell now, Excel will not allow you to…And you are able to edit the other cells just fine!

 

Tips and Advanced Techniques I Use

• Locking cells in multiple sheets: If I’m working with several similar sheets, like monthly tabs in a budget, I repeat the process for each worksheet individually. There’s currently no way to lock cells across all sheets at once in Excel’s standard interface.
• Highlighting locked cells: For clarity, I sometimes use cell shading or borders to visually indicate which cells are locked. This is especially helpful when sharing templates with others.
• Using “Allow Edit Ranges” for more control: In the Review tab, I found the “Allow Users to Edit Ranges” feature. It lets me specify certain ranges that specific users can edit, even if those cells are generally locked. This is handy in collaborative environments where different team members are responsible for different sections.

• Locking only formulas: Occasionally, I use Excel’s “Go To Special” feature (Home > Find & Select > Go To Special > Formulas) to quickly select all cells with formulas, then lock them, leaving the rest of the sheet editable.

• Protecting workbook structure: If I want to prevent users from adding, deleting, or moving worksheets, I use the “Protect Workbook” feature under the Review tab.

 

Frequently Asked Questions