Suppose you’ve worked on very important financial records on a worksheet, and you’ve shared that sheet with your fellow employees which contain complex formulas and important data. What if they accidentally deleted, or edited, or changed some important data, it would compromise your whole work.
Thankfully, Microsoft Excel has a great feature that lets you lock and protect specific cells or sheets or even the whole workbook to keep them from being modified. It also prevents complex formulas and functions from being accidentally deleted or changed.
In this post, we’ll show you how to lock cells in Excel and protect them from being changed or edited. A Locked cell cannot be formatted or reformatted or deleted and the value inside cannot be edited or deleted.
Locking All Cells in Excel
By default, all the cells in excel are locked which can be seen by just right-clicking on any cell and then, select the ‘Format Cells’ option.
Click the ‘Protection’ tab, and as you can see the cells are locked by default. But, locking cells won’t work until you protect the whole worksheet.
To protect all the cells, select all the cells. This can be done by pressing ‘CTRL+A’ ( Alternatively, click on the arrow icon on the top left corner of the worksheet.
Next, go to ‘Review’ tab and click ‘Protect Sheet’ option.
A wizard window named ‘Protect Sheet’ will open, here you can enter your password. You will be asked to renter the password, enter the password and click the ‘OK’ button.
If you choose to protect the sheet without the password, it can be unlocked by anyone clicking on ‘Unprotect Sheet’ under the Review tab. You can also allow the user of any of the given accesses by checking on the box in the ‘Protect Sheet’ wizard.
By default, the first two options are checked which allow the user to select cells, but they cannot be edited or deleted. You can also remove those options by unchecking the boxes.
Now if anyone try to edit the cells, they will be shown this error message.
Locking Specific Cells in Excel
Sometimes you may want to lock only specific cells from being edited but still allow users to edit other cells in a spreadsheet. For example, you’re sharing an ’employees details’ worksheet with your employees. In that sheet, you wanted them to update their address, age, or any changes in name, but you don’t want them to edit their Employee ID. So, to prevent them from adjusting their Employee ID, you have to lock those cells that contain their ID.
To lock specific cells in Excel, you need to make sure that the whole sheet is unlocked. To do that, select all cells, and right-click and select ‘Format cells’. In the ‘Format Cells’ window, uncheck the ‘Locked’ box and click ‘OK’
Now, you may select entire rows or columns, or specific cells that you want to lock. Right-click on the selected cell and click ‘Format Cells’.
Check the ‘Locked’ checkbox and click ‘OK’.
Same as before, locking cells won’t work until you protect the worksheet, so you protect the sheet the same way you did for all cells. Click ‘Protect Sheet’, enter the password, and re-enter the password. But before clicking ‘OK’, uncheck the ‘Select locked cells’ option, this will stop the user from selecting the locked cells. If you leave this option checked, the user can still copy the cell content.
Unlocking the Locked Cells
You can unlock the cells by unprotecting the worksheet, go to ‘Review’ tab in the Ribbon and click ‘Unprotect Sheet’.
Then, enter the password and click ‘OK’.
Now, you have unlocked your sheet, you can edit the cells again.
Locking Formula Cells
You can also lock the cells that contain Complex formula. To do that, find the ‘Find & Select’ button in the top right corner of the Ribbon in the ‘Home’ tab and expand. Then, click ‘Go To Special’ in the drop-down menu.
In the windows, select ‘Formulas’ and click ‘OK’.
Excel finds and selects all your formula cells. Now you can follow the above steps for locking specific cells and lock your formula cells.
Follow this step by step guide on how to lock your cells in Excel and protect the important data that you’re sharing.