How to Lock Cells in Excel: 3 Easy Methods

Knowing how to lock cells in Excel is a powerful way to prevent people from changing values or formulas in your worksheets. In this post, learn how.
How to Lock Cells in Excel

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

Follow these steps to lock cells in Excel.

Step 1: Select the cells that you want to lock

select cells in the Excel worksheet to lock

Step 2: Right-click and select Format Cells from the popup menu

right-click on selected cells and go to Format Cells

Step 3: Under the Protection tab on the Format Cells window, check the Locked check box and click OK

check the Locked checkbox under the protection tab in excel

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.

under home tab on ribbon click on Format and then Loc cell on 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.

uncheck the Locked checkbox to unlock cells in Excel

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

under the Review tab on ribbon click on Protect Sheet

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.

specify optional password to protect sheet

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.

check to make locked and unlocked cells selectable in excel

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.

how to select all cells on worksheet and unlock

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.

uncheck Select locked cells option to hide locked cell formulas

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.

under home tab click on Find and Select and select Formulas

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.

under Home tab click on Find and Select and select Go To Special
On Go To Special window you can select specific formula types
On the Go To Special window, you can select specific formula types

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:

Microsoft Excel protected sheet warning 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.

LEARN SOMETHING NEW

Follow Us on The KuduTek Channel

Learn about Excel and SQL and all kinds of cool stuff!