Locking cells in Excel is a great way to safeguard data in your spreadsheet. To protect cells, the user needs to check the Locked property on those cells and then protect the sheet. Combining these two steps will ensure you lock and secure the cells from user changes. Password protection can also be used.
In this article, you will learn how to lock cells in Excel on an entire sheet. We will also look at how to lock cells in Excel for specific cells and learn how to lock formula cells.
Table of Contents
- How to Lock Cells in Excel
- How to Lock All Cells in Excel
- How to Lock Specific Cells in Excel
- How to Lock Formula Cells in Excel
- Conclusion
- Frequently Asked Questions
- Closing Remarks
- Recommended Articles
How to Lock Cells in Excel
Follow these steps to lock cells in Excel.
Step 1: Select the cells that you want to lock
Step 2: Right-click and select Format Cells from the popup menu
Step 3: Under the Protection tab on the Format Cells window, check the Locked check box and click OK
Instead of right-clicking, you can also navigate to the Home tab on the ribbon, click on the Format tab in the Cells group, and select Lock Cell from the drop-down menu.
How to unlock cells in Excel
Use the steps above under the section How to Lock Cells in Excel to unlock cells in Excel, but uncheck the Locked check box when using the right-click method above.
How to Lock All Cells in Excel
All Excel cells already have the Locked property checked by default. Hence to protect all the cells in an Excel worksheet, you just need to protect the sheet.
How To Protect a Worksheet in Excel
Follow these steps to protect a worksheet:
Step 1: Navigate to the Review tab on the ribbon
Step 2: In the Protect group, click on the Protect Sheet tab
Step 3: On the Protect Sheet window, specify an optional password that is required to unprotect the sheet and to protect the worksheet and contents of cells.
Step 4: On the same Protect Sheet window, check all the actions you want to prevent on the sheet and whether you want to allow the user to select locked and unlocked cells on the worksheet.
Instead of Step 1 and Step 2 above, you can right-click on the sheet tab and click Protect Sheet from the popup menu.
Now all the cells in the worksheet are locked and protected. Read the section How to Lock Specific Cells in Excel below if you want to know how to only lock and protect specific cells.
How to Protect a Workbook
Notice if you followed the steps above to protect a sheet under the Protect group on the ribbon, there is also a Protect Workbook tab. Here are two posts explaining how to protect a workbook or how to protect an Excel file.
Not to digress, the focus of this article is to explain how to lock cells in Excel. So continue reading.
How to Lock Specific Cells in Excel
You can accomplish locking specific cells on a sheet in two ways. Because, by default, all the cells in Excel are locked, you can either unlock all the cells and then select only specific cells on the sheet, or you can keep the cells locked and select only the cells you wish to unlock.
How do I unlock all cells on a sheet?
Right-click on the top right corner of the sheet, click Format Cells to open the Format Cells window, and then check the Locked checkbox until the checkbox is completely unchecked.
Alternatively, select any cell and then press Ctrl+A to select all. Then right-click and choose Format Cells or navigate to and click on the Format tab on the ribbon under the Home tab.
You can use the same procedure to lock the cells on the entire Excel worksheet.
How to Lock Formula Cells in Excel
You may want to lock cells in Excel if you have formulas in those cells and want to prevent your users from changing those formulas by making those formula cells non-editable. In some cases, you might also want to prevent them from seeing the formulas in some or all of the formula cells.
How to prevent users from altering formulas
Step 1: Lock the cells containing formulas (see below for easily selecting all or some cells with formulas).
Step 2: Navigate to the Review tab on the ribbon, and in the Protect group, click Protect Sheet (or right-click on the worksheet tab and select Protect Sheet from the popup menu).
Step 3: Enter a password if you wish to require the user to enter a valid password to unprotect the sheet. If you do not wish to add a password, leave the password box blank.
Step 4: By default, leave the Select locked cells checkbox checked and click OK.
How to prevent users from seeing formulas in cells
To prevent a user from seeing the formula in a selected cell, you need to prevent the user from being able to select the cell that contains the formula.
Follow the same steps as above to prevent a user from altering formulas, but in Step 4 above, uncheck the Select locked cells checkbox and click OK.
How to select all cells with formulas on a worksheet
Step 1: Navigate to the Home tab on the ribbon, and under the Editing group on the right side of the ribbon, click on the Find & Select tab.
Step 2: On the popup menu, select formulas. This will highlight and select all the formula cells on the worksheet.
Note, on the Find & Select drop-down menu, you can also click on Go To Special, and from there, you can select the specific formula types you want.
How to select certain cells with formulas
If you know exactly which cells on the worksheet contain formulas you want to protect, or if you only want to protect select formulas and not all the formulas in specific cells, you can follow the same steps as above in the section How to Lock Specific Cells in Excel.
Conclusion
Knowing how to lock cells in Excel is a powerful way to prevent people from inadvertently changing values or formulas in your worksheets and thus prevent them from breaking your workbook solution.
Frequently Asked Questions
What is the difference between a locked cell and an unlocked cell?
When protecting a worksheet, you can specify whether users can select the locked and unlocked cells. If you uncheck those options, they will be unable to select the cells on the sheet regardless of their Locked status.
If you allow them to select locked cells, then on a protected sheet, when the user tries to change the value of such a cell, Microsoft Excel will not allow the change and show the following message:
If you allow the users to select unlocked cells on a protected worksheet, these will be editable cells that the user can change.
Closing Remarks
In this article, we have seen how to lock cells in Excel and how this is used for protecting Excel content. When using cell locking strategically in your spreadsheets, you can control where data can be changed and prevent the user from breaking your complex workbook solution.
