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.
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.
Step 3: From the Format Cells dialog box click on the Protection tab.
Step 4: You will notice that the Locked selection is already checked. Uncheck it and click on OK.
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.
Step 7: From the Format Cells dialog box click on the Protection tab.
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.
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.
Step 10: From the Protect Sheet menu. By default, the Select locked cells and Select unlocked cells are selected. You will click OK.
If someone should attempt to change the contents of the cell they will get an error message:
You will now notice that on the Protect Sheet area now reads Unprotect Sheet.
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.
It will then ask you to Reenter password to proceed.
If a user were to click on Unprotect Sheet now they will be presented with a prompt to enter a password.
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…