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 fore-front. 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 setup 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 whilte researching for that blog post and I'm sure it will open your eyes about 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
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
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 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 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)|
After quite a bit of testing I found that there does not appear to be any password requirements when entering in 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 in 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.
You May Also Want To Check Out...
My Password Recovery add-in will allow you to recover any lost password that was protected within Excel 2010 and prior. The add-in runs in Excel 2007 - 2013 and can even remove passwords from multiple worksheet tabs at the same time! Proceeds from this add-in go towards paying the costs to run this website and keeping it AD-FREE! So check out the promotional page by clicking the button below to see if this add-in can ease your pain of dealing with forgotten worksheet passwords.
Share This Post!
Did you find this post helpful? Do you want to support this blog because you're just that awesome?! By sharing this post on Facebook, Twitter, or Google+ you are not only providing others with great information, you are creating exposure for The Spreadsheet Guru website. Growing this community is my number one goal as I have found learning to increase exponentially when lots of people are contributing to the conversation. Learning is the whole reason why this blog exists! If you want to spread the word just click on the Share button right below this paragraph (next to the Like button -- I enjoy "likes" too!) or on the Floating Share Bar to the left and select your preferred social medium. Thank you so much for reading and I hope I can continue to provide you with great content in the future! Cheers!