Excel Protection Features

Excel is used to perform complex calculations and as a database to store data in many companies. Many Excel files are sent across PCs and across organisations to share data. But what if the Excel file reached an unintended user? What if someone got access to my system, is my personal information saved in one of the Excel file secure? To answer this question, let's talk about Excel 'Protection' features.

Excel offers protection at multiple levels. If you just want to simply hide the contents of cells or password protect entire workbook, you are good to go with Excel. Below we will try to explore various features of Excel regarding protection.

Case 1: Suppose you are sending a file to a friend and do not want him/her to see what a particular cell contains but there is no harm if they find out what is there. Then you simply format the cell to make contents of it invisible on the face.

Select the cell(s) you want and press Ctrl+1 (or simply right click and select Format cell option). In the Number tab, under Category, select Custom and under Type, enter ;;; (3 semicolons without spaces) and hit OK. Contents of selected cells will not be visible any more in the cell. However you can still see them in formula bar. This option is best used in conditional formatting to highlight cell colours but not contents.

Case 2: You do not want your friends to see what's there in a particular cell and also in formula bar. But you are fine with them seeing the content with a couple of clicks. Then along with above step, protect your sheet.

Once the formatting to make content invisible is done, in Format cells dialogue box (Ctrl+1) under Protection tab, make sure check boxes for Locked and Hidden are ticked. Then go to Tools > Protection > Protect Sheet and hit OK (In Excel 2003) or Review > Protect Sheet and hit OK (Excel 2007 and later). This makes the selected cells locked for editing and also the content and formulas are not visible in formula bar. Your friend can un-protect the sheet and see the content in formula bar.

This option is best used when you do not want the user to see contents of the cells and formulas as well. Without steps under Case 1, only Case 2 is used to hide the formulas behind cells and display only values.

Case 3: You do not want your friend to be able see the contents without a password. In this case, use a password to protect the sheet. In Case 2 under Protect sheet dialogue box, give a password to un-protect the workbook and hit OK. Confirm the password again and hit OK. Your friend will not be able to do any modification to locked cells without knowing the password.

Apart from restricting the visibility of contents of the cell, you can change many user permissions on your file. If you notice under Protect Sheet dialogue box, you can see some check boxes available under field 'Allow all users of this worksheet to'. Let's see about each of them.

• Select locked cells: If this box is checked, user will be able to place cursor and select the locked cell. If this box is not checked, user will not be able to select it. Pressing Enter or Tab will make Excel select the next un-locked cell. By default this box is checked.
• Select unlocked cells: If this box is checked, user will be able to select unlocked cells. By default this box is checked.
• Format cells: User can apply formatting (any option in Format cells dialogue box and conditional formatting) to the cell. That is to say, user can apply formatting categories to the cell, align the contents, change the font, apply borders etc.
• Format columns: User can apply formatting to the column i.e. change width of column, hide and unhide etc.
• Fomat rows: User can apply formatting to rows i.e. height of the row, hide and unhide etc.
• Insert columns: Allows user to insert new columns
• Insert rows: Allows user to insert new rows
• Insert hyperlinks: Insert hyperlinks.
• Delete columns: Columns can be deleted. 
• Delete rows: Rows can be deleted.
• Sort: User can sort data. But note users can't sort ranges that contain locked cells on a protected worksheet, regardless of this setting
• User AutoFilter: Using the drop-down arrows to change the filter on ranges when AutoFilters are applied. Users cannot apply or remove AutoFilters on a protected worksheet, regardless of this setting.
• User PivotTable reports: Formatting, changing the layout, refreshing, or otherwise modifying PivotTable reports, or creating new reports.
• Edit objects: You can edit or update if you have any objects embedded in your file like maps, charts, shapes
• Edit scenarios: If you have any what-if scenarios in your file, users can change them.

Case 4: You want your friend to be able to edit only a part of the sheet but not able to modify all other parts. You also want a password protection for the parts your friend needs to change and have a different password for other parts.

This is where you make use of Excel's 'Allow users to Edit Ranges' option. Un-protect the sheet if its already protected. Click Tools > Protection > Allow users to Edit Ranges (in Excel 2003) or Review > Allow users to Edit Ranges (in Excel 2007 and later). In the pop up dialogue box, click New. Give a Title to the range, select the range and give a password and click OK. Confirm the password and hit OK.

Protect the sheet as in Case 3 and you are done. But remember to use a different password to protect range and for sheet. Now let the user know only that password which is needed to edit range and you are good.

You can protect multiple ranges with multiple passwords this way. There's more to it. You can even specify the names of users who can be able to edit these ranges without a password. Once you create the range to be protected or even while creating, click on Permissions button to add users who can edit. Add users directly if you know their login account or browse through your corporate directory and add them.

Case 5: You are done with protecting the contents of the sheet. But wanted to make sure users do not meddle with the file structure like name and order of the sheets etc. Can you do it in Excel?

Absolutely. Click on Tools > Protection > Protect Workbook (in Excel 2003) or Review > Protect Workbook. In the pop up dialogue box, check the appropriate check box (see below) and give a password (optional) if you wish to and hit OK.

• Protect Structure: Check this box if you want to prevent users (note that under protect sheet, in above first few cases, checking a box will allow users to give permission to do, here it is other way around) from viewing worksheets that you have hidden, moving, deleting, hiding, changing the names of worksheets, inserting new worksheets or chart sheets, moving or copying worksheets to another workbook etc.
• Protect Windows: Changing the size and position of the windows for the workbook when the workbook is opened, moving, resizing, or closing the windows. Note that users will be able to hide and unhide windows.

Now that we saw various protection features offered by Excel at different levels, let's also see some important points to note and tips.

1. When you protect sheet or windows and have any VBA code that works on the protected fields, make sure you supply un-protect password to VBA code. Otherwise your code will encounter run time error and stop executing.
2. Changing Locked and Hidden attributes under Format Cells dialogue for any cell will not have any effect unless you protect sheet.
3. Excel password is case sensitive. 'abcd' is not same as 'ABCD'.
4. Permissions available to users while protecting sheets (see case 3 above) are introduced only from Excel 2002 and later. So if you are working on a version earlier Excel 2002, you are out of luck.

Now you might be thinking that Excel offers lot of protection features and probably safe application to store any kind of sensitive and confidential data. If you are, then below section is for you.

Is Excel secure to keep confidential data?

Firstly, do not confuse protection with security. Excel protects your data from a novice user but cannot really secure it. If a professional user of Excel want to see what a protected Excel file contains, he probably will. Excel uses very simple algorithm to protect worksheets, no matter how complex password you use to protect. Excel's password protection is weak and it's relatively easy to break it. So if you really want your data be secure then Excel is not your buddy.

That said, is Excel protection useless? No. Excel protection works very well with normal usage. You can rely on Excel if you want to prevent an accidental removal of formula or restrict a novice user to follow a template.

There are many commercial password breaking tools available to break open any kind of Excel protection. You might have forgotten password of a protected file or just want to peek in other's files, it's up to you on how you use the tools.