Feb
15

Protection options in Excel: mark as "Final", encrypt with password, protect woksheet and workbook structure, add digital signatures

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!

Firstly, most protection options are now available in one handy place under File>Info>Permissions, as follows:
Protection options are now available under File>Info>Permissions

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!

See the protect sheet option on the Review tab


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.

See the protection features in this 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.

Click the Mark as Final button and receive this 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.

You will receive this information reminder if you use the Mark as Final feature for the first time

Press OK again and note that this protection option is now listed back under File>Info on your file.

Protection options are now available under File>Info>Permissions

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.

Click this button to edit the spreadsheet


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.

Click the Encrypt with Password button and you'll receive this 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:

Protection options are now available under File &gt Info &gt Permissions

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.

Clicking the General Options button to open up this 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:

  1. Highlight the cells you'd like to reformat.
  2. Choose Home>Format>Format Cells to open up the below cell formatting dialogue box.
  3. Choose the Protection tab & toggle between Hidden and/or Locked, or completely open, as you wish :-)
    Change the protection formatting of a cell using this window

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.

Excel Protect Sheet dialog box

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.

Protect Structure and Windows dialog 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.

Sign dialogue box

  1. You can optionally enter a purpose for signing the document.
  2. You can choose to 'change' your digital signature from the default user.
  3. Once you're content with the set-up, press the Sign box to receive the below reminder.
    Signature Confirmation dialog box
  4. Press OK and notice the Digital signature listed back under File>Info.
    See 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.
See this dialog box after editing a file


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

  1. Choose File>Info>Permissions>Encrypt with Password to open up the Encrypt Document dialogue box.

    Select this option to open up the Encrypt Document dialogue box

  2. Literally delete any password shown, and press OK.

    Click the Encrypt with Password button and you'll receive this password prompt

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 :-)

See also

3 Responses to "Protection options in Excel: mark as "Final", encrypt with password, protect woksheet and workbook structure, add digital signatures"

  1. Dawn says:

    Is there a way to prevent a worksheet being used as a template from being overwritten by incorrectly hitting the save button -- but protect it so that changes can only be saved with the "save as" mode after data is entered, keeping the original in tact?

  2. kumarapush says:

    Good Article explaining in detail about most of available options to protect Excel.

    Digital signature is really something new to me. To add additional value to this topic, the below article can be referred to know more about how to add passwords to Excel through VBA. http://officetricks.com/password-protect-ms-office-documents/

    I am just wondering whether adding a digital signature is possible through VBA, in case if any company needs to add signature to lot of documents within its network.

  3. ian says:

    Microsoft claim that excel spreadsheet are secure because their protection functions work with Excel.
    Other .xlsx readers bypass their workbook protection as if they weren't there.
    But they still claim Excel is secure because they don't make these other products.

    I Spent a whole day trying to explain why my data was at risk, Is it a difficult concept?
    Now I have to scrap weeks of work and scrap all MS products for ever!

Post a comment



Ultimate Suite for Excel Professionals
 
 
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard