The Various Ways To Password Protect Excel Worksheets

Microsoft Excel Worksheet Password Security Protection

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

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:

  1. Right-click on the spreadsheet cell or cells (if you want to handle a bunch of cells at the same time)
  2. Go to Format Cells
  3. Select the Protection tab
  4. Locate the Locked checkbox
Format Cells Dialog Box Protection 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:

  1. Select the object (make you are not selecting just text)
  2. Right-click on the object
  3. Click Size and Properties from the menu
  4. Select the Properties section
  5. Locate the Locked  & Lock Text checkboxes
 
Access through the Size and Properties Dialog Box in 2007-2010

Access through the Size and Properties Dialog Box in 2007-2010

Access through one of the Format Panes in Excel 2013

Access through one of the Format Panes in Excel 2013

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:

  1. Navigate to the Data tab in Excel's Ribbon
  2. Click the What-If Analysis drop down button
  3. Select Scenario Manager
  4. The click the Add button
Scenario Manager Password Protection Prevent Changes Hide Checkboxes

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:

  1. Navigate to the Review tab in the Excel Ribbon
  2. 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.

Microsoft Excel Worksheet Password Security Protection
RestrictionWhat This Allows User To Do If Checked
Select Locked CellsExcel user can select any cell with a Locked status
Select Unlocked CellsExcel user can select any cell with an Unlocked status
Format CellsExcel user has ability to format cells (ie Fill, Font, Border, etc…)
Format ColumnsExcel user has ability to format column properties (ie column width, grouping, etc…)
Format RowsExcel user has ability to format row properties (ie row height, grouping, etc…)
Insert ColumnsExcel user can insert columns
Insert RowsExcel user can insert rows
Insert HyperlinksExcel user can add/remove hyperlinks
Delete ColumnsExcel user can delete columns
Delete RowsExcel user can delete rows
SortExcel user can sort data
Use AutoFilterExcel user can Filter on data sets (cannot enable/disable filtering from data)
Use PivotTable ReportsExcel user can modify PivotTable…..
Edit ObjectsExcel user can select & make modifications to spreadsheet Objects (ie Shapes, Graphs, Textboxes, etc…)
Edit ScenariosExcel users can view or modify scenarios (depending on scenario protection settings)

Password Requirements

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.  

  1. You can have as many characters as you want
  2. You can use symbols and numbers,
  3. 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 FacebookTwitter, 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!