How to Lock Cells in Excel

How to Lock Cells in Excel

There are times that you will need to lock a cell in Excel so that it cannot be edited, or it can only be edited by certain people. For example, you may have a calculation that you do not want someone to change the formula for or there may be data that you do not want to be changed. Regardless of the reason, I am going to show you how to lock cells in excel both with and without a password.

How to Lock A Cell in Excel

First you will start out with a dataset. I have a general account balance showing the type of transaction, if it was a credit (+) or a debit (-) and to the right what the current balance is. The top row is frozen (if you need to know how to do this click here to read my tutorial on that) so the balance will always be visible.

Graphical user interface, application, table Description automatically generated

I have formatted the current balance (F1) to automatically update when an amount is entered and I do not want that formula altered.

Step 1: Select the entire workbook.

Step 2: Right click and select Format Cells.

Graphical user interface, text, application Description automatically generated

Step 3: From the Format Cells dialog box click on the Protection tab.

Graphical user interface, text, application, email Description automatically generated

Step 4: You will notice that the Locked selection is already checked. Uncheck it and click on OK.

Graphical user interface Description automatically generated with medium confidence

This will allow you to edit the rest of the spreadsheet but be able to lock a cell in Excel.

Step 5: Select the cell that you want to protect. In our dataset I have highlighted F1.

Step 6: Right click and select Format Cells.

Graphical user interface, text, application Description automatically generated

Step 7: From the Format Cells dialog box click on the Protection tab.

Graphical user interface, text, application, email Description automatically generated

Step 8: You will notice that the Locked selection is not checked now since we unchecked it for the entire workbook previously. Check it and click on OK. As an option you can also check Hidden here and it will hide your formula and only show the result.

Graphical user interface Description automatically generated with medium confidence

This will allow you to edit the rest of the spreadsheet but be able to lock this cell in Excel.

Step 9: From the ribbon select the review tab and then click on Protect Sheet.

Graphical user interface, application, Teams Description automatically generated

Step 10: From the Protect Sheet menu. By default, the Select locked cells and Select unlocked cells are selected. You will click OK.

Graphical user interface, text, application Description automatically generated

If someone should attempt to change the contents of the cell they will get an error message:

Graphical user interface, text, application Description automatically generated

You will now notice that on the Protect Sheet area now reads Unprotect Sheet.

A picture containing table Description automatically generated

All a user would have to do is click on this icon and the cell(s) would be unprotected again. To avoid this we will add just one additional step.

Step 11 (Optional): In step 10 there an option to enter a Password to unprotect sheet.

Graphical user interface, text, application Description automatically generated

It will then ask you to Reenter password to proceed.

Graphical user interface, text, application, chat or text message Description automatically generated

If a user were to click on Unprotect Sheet now they will be presented with a prompt to enter a password.

Graphical user interface, text, application Description automatically generated

Conclusion

Now that I have shown you how to lock cells in Excel, how will you use it? In the meantime, stay tuned for tomorrow’s tutorial on how to remove conditional formatting in Excel. Until next time…


Leave a Comment