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.
The method you use to secure your workbook will depend upon how sensitive your data is, and how you plan for the data to be used. If you merely wish to prevent changes to your information but do not mind if others can open and view the workbook, you can use the Protect Sheet or Protect Workbook commands located on the Review tab. This is useful if you want to share information without inviting accidental changes to the data or revealing proprietary formulas. It also does not prevent viewers from copying your data into their own spreadsheets. These features should only be used for convenience, not to secure important or sensitive data.
When you need to prevent unwanted users from viewing, or even opening your workbook, you will instead need to Encrypt your entire file with a password.
Let’s look at each of these options:
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 to 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.
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.
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 tab. 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!
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.
