Ever had that heart-stopping moment when you accidentally messed up a crucial Excel sheet? It’s a common pain point, especially when dealing with complex formulas and important data. To stop accidental edits, Excel provides a way to lock and protect your cells.
Locking cells prevents them from being changed, formatted, or deleted. Here’s a breakdown of how to lock cells and safeguard your work.
Locking specific cells
If you only want to protect some cells, like employee IDs in a shared document while allowing users to update other info, this is the way to go.
-
First, unlock the entire worksheet so you can pick and choose which cells to lock. Select all cells by pressing
CTRL+A, then right-click and choose Format Cells. In the Protection tab, uncheck the Locked box and hit OK.
-
Now, select the specific cells you want to lock, such as the cells containing Employee IDs. Right-click and select Format Cells.
-
In the Protection tab, check the Locked box, and click OK to lock these cells.
-
Protect the worksheet by going to the Review tab and clicking on Protect Sheet. You can set a password to prevent others from unprotecting it.
Before you protect, think about unchecking the “Select locked cells” option in the dialog box. This stops users from even selecting locked cells, making it even safer. If you leave it checked, they can select but not modify them.
Locking all cells
By default, all cells in an Excel sheet are locked, however, this lock isn’t active until you protect the sheet. You can confirm this by right-clicking a cell, selecting Format Cells, and checking the Protection tab. The “Locked” option should be checked.
- To protect all cells, enable sheet protection. First select all cells by pressing CTRL+A or by clicking the top-left corner arrow.
- Go to the Review tab, and click on Protect Sheet.
- In the Protect Sheet window, you can set a password to prevent others from unprotecting the sheet. Click OK.
If you skip the password, anyone can unprotect the sheet via the Unprotect Sheet button under the Review tab. Also, you can choose what users can do on the sheet by selecting options before you finalize the protection.
Normally, users can select locked and unlocked cells, but not edit them. If someone tries to edit a locked cell, they’ll get an error message.
Locking cells containing formulas
If you want to protect formulas from accidental edits, here’s how to lock only the formula cells.
-
Go to the Home tab, then find Find & Select (usually on the far right). Click it and choose Go To Special…
-
In the Go To Special dialog box, select Formulas and click OK. This will highlight all cells with formulas.
-
Right-click on any selected cell, choose Format Cells, go to the Protection tab, make sure the Locked option is checked, and click OK.
-
Protect the sheet under the Review tab with the Protect Sheet option. Set a password if desired, and click OK.
Now, your formula cells are protected from unwanted changes.
Unlocking protected cells
If you need to modify locked cells later, you can unlock them by unprotecting the sheet.
-
Go to the Review tab and click Unprotect Sheet.
-
Enter your password, if you set one, and click OK.
Once unprotected, all cells are editable again.