In this article Terri explains all the different protection options available in the new Microsoft Excel 2010
Employing and managing Microsoft Excel protection features couldn’t be easier since the introduction of the new Excel ribbon!
- What protection features are available?
- Mark Excel document as Final
- Protect Excel files with a password
- Excel Worksheet Protection
- Protect the entire workbook
- Add a Digital Signature to Excel file
- How to remove protection
- Remove encryption password from Excel file
Firstly, most protection options are now available in one handy place under File>Info>Permissions, as follows:
And secondly, as we’ll see further below, this same screen also doubles up as a handy protection manager centre where all initiated protection features are listed in one happy tidy place.
Unfortunately, there’s one major drawback I’ve encountered so far, being that we can no longer allocate a separate password for file opening & file modifications (which I often use in my day to day Excel work). But of course the option is still available under the Microsoft Excel Review tab, so I for one will keep applying this feature from there, whilst considering the possibility of investing slightly more trust into the people I share my spreadsheets with!
So what exactly are Microsoft Excel protection techiques?
Microsoft Excel protection techniques do exactly what it says on the tin, they protect and thus validate your data.
There are a whole range of options available, from cell locking & formulae hiding, worksheet locking and entire workbook encrypting. You can also employ a mix of techniques, basically, however you see fit!
In any event, these protection techniques are somewhat rudimentary it’s fair to say, quite simply because there are too many loop-holes and cracking programs available out there! So, if it’s one hundred percent guaranteed data security you’re after, I’d advise you to seek alternative data security tools and not rely entirely on Microsoft Excel!
If however, you simply want to discourage accidental changes, validate your shared workbooks or deter prying eyes, then Microsoft Excel improved protection features may well be all the data protection features you’ll ever need!
What protection features are available?
There are five Excel protection techniques available by clicking File>Info>Permissions on the above screen. Once you do this you’ll receive the below list.
In the next section I’ll give an explanation, instruction, keyboard shortcuts and examples for each technique.
Mark Excel document as Final
The first of the new protection techniques, using this option literally marks a file as final and turns off all editing rights. It’s useful for preventing accidental modifications by other users, and also yourself!
To use the feature simply click the above ‘Mark as Final’ button and receive the below prompt reminder.
Press OK to the prompt reminder and, if this is the first time you’ve used the feature, you may then receive the below information reminder.
Press OK again and note that this protection option is now listed back under File>Info on your file.
If, after marking as final, you’d like to make some further edits to your file, simply choose to edit the file by pressing the ‘Edit Anyway’ button in the information bar along the top of your workbook.
Protect Excel files with a password
Probably the most popular of Excel protection techniques, especially useful to deter prying eyes, or when sharing confidential information by email, is to password encrypt your file.
This option prompts the user to enter a password in order to open the file.
To apply this feature simply click the ‘Encrypt with Password’ button on the above File>Info>Permissions screen and you’ll receive the following password prompt.
You must take serious heed of the password protection caution, as it is entirely impossible to recover a lost password through Excel!
Click OK and confirm the password again. Then note that the password protection option is marked back under File>Info, as follows:
Of course you can still initiate password protection when you save your file, and you might prefer to stick to the old fashioned way if you’re as big a fan of password protecting workbook modifications as I am!
Employing encryption and marking as final techniques together achieve a similar result as putting a password on workbook modifications. However, you can still opt to unmark the file, make changes and re-mark the file as final. I’d therefore recommend the old fashioned way of encrypting the file and workbook modifications under two separate passwords, if you feel you need the added protection!
To use the old fashioned password technique, simply click File>Save As on the Microsoft Excel ribbon and then click on the ‘General Options’ next to the save button to open up the below General Options dialogue box.
The first tick-box allows you to specify that a back-up copy of the file be automatically generated upon future opening.
You can then enter passwords to open and/or to modify your file, and as mentioned these needn’t be identical!
And finally, you can also choose to recommend opening in read-only mode, but be aware that this is only a recommendation and the end-user can choose to ignore it if they so please!
Excel Worksheet Protection
This option protects all the cells in an individual worksheet to the style in which they are allocated when the function is activated.
By default all cells are locked, but you could allocate a mix of cell formatting features, as required.
This technique is especially useful for Excel based input templates, where you’d like to collect certain data in certain cells but keep all of the other cells around ‘locked’ for user input.
To change the protection formatting of a cell:
- Highlight the cells you’d like to reformat.
- Choose Home>Format>Format Cells to open up the below cell formatting dialogue box.
- Choose the Protection tab & toggle between Hidden and/or Locked, or completely open, as you wish
By default all cells are locked. This means that nothing can be changed once the sheet is protected.
By ticking the Hidden box you can also select to hide cell formulae.
And of course, if you’d like the cells to remain editable after protecting your worksheet, then you can choose this by un-ticking both box’s!
If you’re working with locked, hidden and open cells on the same worksheet, then you might want to consider employing Microsoft Excel styles for usability ease! See here for the Microsoft Excel styles instructional article.
And as is so often the case in Microsoft Excel, the exact identical feature can also be initiated under Review>Changes>Protect Worksheet on the Microsoft Excel ribbon.
The Excel keyboard shortcut to protect or unprotect a worksheet is ALT + TPP.
Protect the entire workbook
This option protects the set-up of the workbook i.e. the sheet order, hidden sheet management and the ability to delete or include new worksheets.
To activate the workbook protection feature simply click the above ‘Protect Workbook Structure’ button (found under File>Info on the Microsoft Excel ribbon) to open up the below Protect Structure and Windows dialogue box.
You can choose to protect the structure and/or the windows, they’re not mutually exclusive.
Protecting the structure protects the order of sheets, prevents hiding and un-hiding of sheets and deletion or inclusion of new sheets.
Protecting the Windows will protect the viewing options in place i.e. view and review options.
And as per previous, a protection password is optional.
You can also access this option the old fashioned way under Review>Changes>Protect Workbook on the Microsoft Excel ribbon.
The Excel keyboard shortcut to protect your workbooks structure is ALT + TPW.
Add a Digital Signature to Excel file
The second of the new options added to the protection selection and the final option available here is to add a digital signature (also known as a Digital ID) to your file.
To fully employ this technique you’ll need a ‘signing certificate’ issued by an e-certification authority, and for further information about e-certification authorities, it’s advisable to consult the Microsoft office website.
Essentially this option provides validity assurances on authenticity, integrity, non-repudiation and notarization of your file by adding your digital signature.
To activate the function, you simply click on the ‘Add a Digital Signature’ option found under File>Info on the Microsoft Excel ribbon to receive the Sign dialogue box displayed below.
- You can optionally enter a purpose for signing the document.
- You can choose to ‘change’ your digital signature from the default user.
- Once you’re content with the set-up, press the Sign box to receive the below reminder.
- Press OK and notice the Digital signature listed back under File>Info.
You should also notice that the file has also been marked as final, because essentially, adding a digital signature ensures nothing has changed since the signature was added!
the moment a digitally signed file is edited in any way, the digital signature will be removed and the below warning may be shown.
How to remove protection
To remove any of these Microsoft Excel protection features, simply reverse the steps taken above, starting at the File>Info tab.
Example: remove encryption password from Excel file
Choose File>Info>Permissions>Encrypt with Password to open up the Encrypt Document dialogue box.
Literally delete any password shown, and press OK.
I hope that this short article has helped clarify the various protection techniques now available in Microsoft Excel.
Personally I think the improved positioning of protection features in the one area is especially useful, except (as mentioned) the unavailability of the modification password features. Of course I’ll now be reconsidering my own use of Excel protection features as, through writing this article I am left wondering if I’m somewhat over-zealous about my files!
How and why do you use Microsoft Excel protection features?
And, as per usual, if you have any further ideas or suggestions, please feel free to leave your comments below
- Excel Charts tips, tricks and techniques
- How to use lookup functions in Excel
- Merge Excel worksheets wizard