×

The Various Ways To Password Protect Excel Worksheets

By Chris Newman •  Updated: 09/17/14 •  7 min read
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 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:

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

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 do not appear to be any password requirements when entering 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 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.


Keep Learning

Chris Newman

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.