How to Lock Cells in Excel to Protect Your Work from Changes

We’ve all done it.  Mis-clicked. Mis-typed. Hit enter when we meant to hit tab. And while it’s frustrating to make mistakes in a spreadsheet of our own data, when managing important company assets those mistakes can have expensive consequences. When many people work on the same spreadsheet, these normal opportunities for errors are multiplied.

This is why Excel offers several tools for restricting or limiting access to data. This can be done at the file level with tools such as File encryption that keeps users from opening a workbook without a password. You may have times, however, when you don’t need to prevent people from opening your data, you just need to keep them from making changes to it. You will use worksheet-level protection tools for this.

Here are the steps to protect an entire worksheet from user changes:

1. Select the sheet you want to protect
2. Click the Review tab
3. Click Protect Sheet

This opens a dialog that allows you to enter a password if you want to restrict who can turn protection off. This is not required. You are also shown editing options that let you to specify the kinds of changes you wish to prevent or allow. Put a check by each kind of edit those users will be ALLOWED to do.

In this example, users would be allowed to sort and use AutoFilter on the data in the worksheet but would be prevented from formatting and inserting rows or columns.

Why You Might Need to Lock Cells

Protection at the worksheet level can also be useful when you want to collaborate with others and only protect specific cells from unwanted or inadvertent changes.

The reasons you might want to lock specific cells in an Excel spreadsheet and not others are as many as the reasons for creating a spreadsheet in the first place. Here are some examples, however, that can generate ideas for your own uses:

• You have several people entering records in a database, but you don’t want anyone to add fields or change the field column names.
• You have created a mortgage payments calculator for your agents to use when providing quotes to customers. You want them to be able to enter variables such as loan amounts and term but want the interest rates and calculations to remain fixed.
• You prepare a report each month for your department that depends on others entering the newest data. You want to protect all past entries so that older data is not overwritten and new data is put into the correct cells.

Here are the steps to lock only specific cells in an Excel spreadsheet:

1. Select the cells you want to remain OPEN and editable. Use Ctrl+Click to select multiple cells that don’t touch.
2. Right-click and select Format Cells from the menu.
3. Click the Protection
4. UN-check the Locked This tells Excel that when the worksheet is protected, the cells you un-checked will NOT be locked.
5. Click OK.

Now, use the steps above to Protect the worksheet. The cells that you unlocked will remain editable, all others will be locked.

Hot Tip: You can also quickly lock and unlock cells from the Format dropdown menu on the Home tab!

See Also: How to Hide Formulas in Excel and Protect Your Spreadsheet

To turn off worksheet protection, click the Protect Sheet button again and enter the password as needed.

Can You Lock Cells in Excel Without Protecting the Sheet?

Sometimes the question is asked: Can a cell be locked without turning on worksheet protection? Excel does not allow you to lock cells outside of the Protect setting, but there are two ways you can conceal data that you would prefer not to be seen:

• Hide Rows and Columns – This is useful if you are presenting large spreadsheets and only some of the information needs to be viewed.
• Hide Worksheets – Many workbooks will contain sheets of raw data that dashboards, analysis and formulas are built upon. You can hide these source worksheets for easier navigation through the completed work.

These methods will not prevent someone from unhiding the content unless protection is turned on, so they are only useful when you remain in control of the file.

It is unusual to need the ability to lock cells without turning on worksheet protection, so take a little time to play with this very useful feature and then enjoy the benefits. You’ll find worksheet protection extremely useful for preventing mistakes and reducing the risk of lost data.