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

Key Takeaways

• All cells in Excel are locked by default, but locking only takes effect when you turn on worksheet protection.
• To lock specific cells in Excel while leaving others editable, unlock the cells you want open, then protect the sheet.
• For full-file security, encrypt your Excel file with a password using File > Info > Protect Workbook.
• Excel worksheet protection prevents accidental edits but is not a substitute for file encryption when handling sensitive data.

Why You Might Need to Lock Cells in Excel

We've all done it. Mis-clicked. Mis-typed. Hit enter when we meant to hit tab. 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.

proprietary formulas

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 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.

Excel addresses these scenarios through three distinct levels of protection, each designed for a different situation.

Three Levels of Excel Protection

Before diving into the steps, it helps to understand which method fits your needs. Excel offers three levels of protection, and each one serves a different purpose.

Protect Sheet and Lock Specific Cells are convenience features that prevent accidental edits but do not stop a determined user from bypassing them. File encryption provides true security by requiring a password before the workbook can even be opened. The sections below walk through each method step by step.

Protect Excel Worksheets and Cells

When you need to share worksheets, but you don't want recipients to make changes to the source data itself, you'll use Protect Sheet. This allows you to communicate information and analysis without risk of a recipient editing the information to suit their own purposes, or accidentally making changes that create errors.

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 specify the kinds of changes you wish to prevent or allow. Put a check by each kind of edit that 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.

Once you've identified which cells need protection, follow the steps below.

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 tab
4. UN-check the Locked checkbox. 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—one of many Excel shortcuts and tips worth knowing.

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.

Apply Excel File Encryption

Information in today's corporate environment is as important as raw materials are to a manufacturer or labor is to the service industry. Organizations collect data from consumers, vendors, employees and even competitors every day. When that data contains sensitive information, then it is in the organization's best interests to aggressively protect it.

What is "sensitive" information? The following categories are considered sensitive by most businesses and regulatory agencies:

1. Health Information
2. Financial Information
3. Educational Information
4. Trade or Business Secrets
5. Biological Information (things like facial recognition or fingerprint recognition)
6. Personal Information

Password Protect an Excel File

Here are the steps to encrypt an Excel workbook:

1) Open the workbook you wish to protect, then click the File Tab.

2) On the Info tab, click Protect Workbook.

3) Choose Encrypt with Password from the dropdown list to open the Encrypt Document dialog box.

4) Type a password. Click OK.

5) Type the password again in the Confirm password dialog box. Click OK. Protect Workbook will change color and a notice that a password is required will be displayed.

6) Save and close the workbook. The next time you access it, Excel will prompt you to enter the password before opening the file.

7) To remove password protection:

• Open the workbook and follow steps 1-3 above.
• Erase the password in the Encrypt Document dialog box and click OK.
• Save and close the workbook. A password will no longer be required.

How Secure is Excel Password Protection?

Older versions of Excel employed weak encryption that was easily overcome. Microsoft Office now uses the AES-256 encryption algorithm, which security experts generally regard as statistically unbreakable in a reasonable amount of time. This refers to the ability of someone to break the encryption and read the file without entering the correct password.

However, there is a weak link in this system: The password itself. While malicious actors are unlikely to break a file's encryption, anyone can find software that simply tries hundreds of passwords over and over until it finds the correct one. Password crackers are very clever at trying common words and phrases first. The best encryption in the world won't protect your file if the password is "password".

The quality of your password can be key to protecting your data. Here is a quick guide to creating a secure one:

1. Avoid words you can find in a dictionary
2. Never include personal information such as birthdays or anniversaries
3. Include a mix of upper- and lower-case letters as well as numbers and symbols (*!>/ etc.)
4. Longer is always better!

And don't forget to manage how the password is shared. If you send the password in the same email as the attachment, it's not particularly secure. Some industry experts recommend distributing passwords through different channels, such as text or instant message apps, instead of email.

It is important to pay close attention to how and when data that your organization collects is used and distributed. But, this doesn't mean you must be paranoid and encrypt every file ever created. Careful consideration and well-communicated privacy practices should be enough to ensure that your data is used correctly. And if you are concerned about legal liability for certain kinds of information, reach out to your own legal counsel to put your mind at ease.