The Various Ways To Password Protect Excel Worksheets
Restricting Your User’s Abilities
Microsoft Excel’s worksheet password protection options have been known to trip people up from time to time. Mainly because there are just so many option combinations to choose from! Hopefully, by the end of this article, you will be comfortable with all the various restrictions you can place on other users who may be working in or viewing your spreadsheets.
How Protected Is Your Worksheet?
From now on I want you to throw out the word PROTECTED and use the word RESTRICTED. Why do I want you to do this? Because if your goal is to prevent someone else from seeing your top-secret numbers or your highly proprietary formulas, then you are using the WRONG program. The Excel developers did not design their program to be a bullet-proof safe haven for your treasures. To put it bluntly: it is simply too easy for others to get past the security setup inside an Excel worksheet.
I in no way am harping on the very talented Excel developers and telling you they did an inadequate job. I am plainly trying to get across that Excel was not DESIGNED with tight-locked security at the forefront. The protection was designed as a way to deter others from accidentally messing up the structure of your Excel file not to keep them from understanding it. If you’re interested, you can learn more about how Excel’s password security is set up by checking out a very interesting (at least I thought it was interesting) article I wrote a while back entitled Understanding Excel’s Password Security Methodology. I learned a lot while researching for that blog post and I’m sure it will open your eyes to computer passwords in general if you have never delved into that topic before.
So now that you understand what Excel’s worksheet security is meant to do, we can now dive into all the different restrictions that you can put in place to prevent formulas from being destroyed, graphs from accidentally getting deleted, and well…essentially making your reports & models “Idiot-Proof”.
Determining Locked & Unlocked States
Spreadsheet Cells
Any cell on your spreadsheet can be toggled to be in either a locked or unlocked status. The status of a particular cell determines whether the respective cell will be allowed to be modified or not if the worksheet protection is enabled. In order to determine the status of a cell, you can follow these simple steps:
- Right-click on the spreadsheet cell or cells (if you want to handle a bunch of cells at the same time)
- Go to Format Cells
- Select the Protection tab
- Locate the Locked checkbox
Objects
Pretty much anything that is not embedded in a cell is considered an object. This includes charts, shapes, images, textboxes, & form controls. In order to toggle on the Locked state for a particular object, you must do the following:
- Select the object (make you are not selecting just text)
- Right-click on the object
- Click Size and Properties from the menu
- Select the Properties section
- Locate the Locked & Lock Text checkboxes
Scenarios
Scenarios in Excel allow you to perform advanced situational modeling and statistics. These can be very powerful decision-making tools if you know how to use them. However, they can be messed up pretty easily if someone doesn’t know what they are doing. In cases where your scenario outputs might reach the hands of someone who likes to fiddle around, you may want to place some restrictions on just how much fiddling they can do in your worksheet. To navigate to the Scenario Manager do the following:
- Navigate to the Data tab in Excel’s Ribbon
- Click the What-If Analysis drop down button
- Select Scenario Manager
- The click the Add button
You will notice that there are two protection checkboxes. The first allows you to prevent users from changing anything in the scenario (when worksheet password protection is enabled). The second checkbox allows you to hide the scenario from view when worksheet protection is enabled.
The Different Ways to Restrict Your User’s Capabilities
There are 15 different types of restrictions that Excel’s password protection possesses. These restrictions can be turned off by checking the box associated with the restriction. To bring up the Protect Sheet dialog box you need to do the following:
- Navigate to the Review tab in the Excel Ribbon
- Click Protect Sheet in the Changes group
Once you have access to the dialog box you can enter in a password of your choice (you can also leave this blank if you don’t want to require a password to unlock the spreadsheet) and check any boxes that apply to the restrictions you DON’T want to be placed on the spreadsheet. For example, if you want your users to be able to format cells, you would want to check the Format Cells checkbox.
| Restriction | What This Allows User To Do If Checked |
| Select Locked Cells | Excel user can select any cell with a Locked status |
| Select Unlocked Cells | Excel user can select any cell with an Unlocked status |
| Format Cells | Excel user has ability to format cells (ie Fill, Font, Border, etc…) |
| Format Columns | Excel user has ability to format column properties (ie column width, grouping, etc…) |
| Format Rows | Excel user has ability to format row properties (ie row height, grouping, etc…) |
| Insert Columns | Excel user can insert columns |
| Insert Rows | Excel user can insert rows |
| Insert Hyperlinks | Excel user can add/remove hyperlinks |
| Delete Columns | Excel user can delete columns |
| Delete Rows | Excel user can delete rows |
| Sort | Excel user can sort data |
| Use AutoFilter | Excel user can Filter on data sets (cannot enable/disable filtering from data) |
| Use PivotTable Reports | Excel user can modify PivotTable….. |
| Edit Objects | Excel user can select & make modifications to spreadsheet Objects (ie Shapes, Graphs, Textboxes, etc…) |
| Edit Scenarios | Excel users can view or modify scenarios (depending on scenario protection settings) |
Password Requirements
After quite a bit of testing, I found that there do not appear to be any password requirements when entering a prospective worksheet password string.
- You can have as many characters as you want
- You can use symbols and numbers,
- You can even use spaces!
If you are really lax you don’t even have to enter a password to apply your desired restrictions. I find myself doing this quite often when I just want to prevent novice Excel users from accidentally breaking something.
Go Forth & Protect Your Worksheet’s Functionality
Hopefully, after reading this article I was able to ingrain in your head that you shouldn’t be relying on the in-house Excel password protection to keep prying eyes from proprietary information. If you absolutely want to go down that road, there are companies that offer software for better spreadsheet security. If you’ve used any software that does this please leave a comment below so we can learn from your experiences.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.