Contents
In this article, the author talks about the various security settings available in Microsoft Excel 2010. Applying and managing security tools in Microsoft Excel just got a whole lot easier with the introduction of the Menu Ribbon!
First, most protection options are now available in one place, on the tab Fillet (File) > Info (Details) > Permissions (book protection), as seen in the figure below:
Secondly, this menu is a convenient protection control center, where all activated protection elements are listed in a very accessible way.
Unfortunately, there is one serious drawback that I have encountered: now we cannot assign separate passwords for opening and editing a file (I often use this opportunity in my daily work with Excel). Of course, such an opportunity exists, and it is available on the tab Review (Review). In some cases, I block access to editing a file with a password, but remove protection from opening it, thereby making life easier for people with whom I share my spreadsheets!
What are the protection tools in Microsoft Excel?
Microsoft Excel security tools do exactly what their name says: they protect your data. The whole range of possibilities is available: protecting cells and hiding formulas, encrypting a sheet and an entire workbook. You can combine any instruments to your liking!
To be honest, all protection tools in MS Excel are quite primitive, they have a lot of holes and are easy to crack! Therefore, if the information requires a 100% protection guarantee, then I advise you not to rely entirely on Excel and look for alternative data protection tools.
If you just want to protect your work from accidental changes, verify the authenticity of books you share, or protect yourself from prying eyes, then Microsoft Excel’s protection tools will be more than enough.
What protection methods are available?
There are five security techniques in Excel that can be accessed by clicking Fillet (File) > Info (Details) > Permissions (Protection of the book). After you do this, the dialog box shown in the figure below will open.
In the following parts of the article, I will give explanations, instructions, keyboard shortcuts, and examples for each technique.
Mark an Excel document as final
Your first protection mechanism is to declare the file “completed” and disable all editing rights. It is useful for protecting the book from accidental changes by users, including you!
To use this tool, select Mark as Final (Mark as final) and confirm your decision in the warning window that appears, as shown in the figure below.
Press OK. If you are using this tool for the first time, you may see the following information window:
Click again OK and note that this protection mode is now displayed under Info (Details) tab Fillet (File) in your book.
If, after marking a file as final, you want to make some changes to it, just click the button Edit Anyway (Edit Anyway) in the information bar that appears along the top edge of the workspace when a book is opened.
Password protect Excel files
Probably the most popular Excel file protection tool is password encryption. This tool is especially useful for protecting against prying eyes or when transmitting sensitive information via email. In this mode, the user must enter a password to open the file.
To use this mechanism, select Encrypt with Password (Encrypt with password) in the window Fillet (File) > Info (Details) > Permissions (Protection of the book), and you will see the following window asking for a password:
You should take the warning about the need to store your passwords securely seriously, because it is completely impossible to recover a forgotten password in Excel!
Press OKto confirm the password. Please note that the information that the password protection mode is currently active is now displayed in the section Info (Details) tab Fillet (File):
You can still enable password protection when you save a file, and you might be doing the old fashioned way if, like me, you like to password protect your files from being changed.
To use the good old password protection technique, click Fillet (File) > Save As (Save As) and then click General Options (General Options) next to the button Save (Save) to open the dialog General Options (Common parameters).
The first option in this window allows you to create a backup copy of the file every time you open it in the future. Next, you can enter passwords to open and/or modify the file, and as already mentioned, they do not have to be the same!
And in the end, you can recommend opening this file in read-only mode, but be careful – this is only a recommendation for the end user, who can ignore it if they want!
Excel sheet protection
This tool protects all cells on the selected sheet and keeps them as they were when protection was enabled.
By default, all cells are blocked, but if necessary, you can set the desired set of available formatting tools. This tool is especially useful for creating data entry forms in Excel when you want information to be entered only in certain cells.
To change cell security:
- Select the cells in which you want to change protection.
- Press Home (Home) > Size (Format) > Format Cells (Format Cells) to open a dialog box as shown below.
- Click the tab Protection (Protect) and check/uncheck the options Hidden (Hide formulas) and Locked (Protected cell).
By default, all cells are blocked. This means that if the sheet is protected, then nothing can be changed. If you check the box next to the option Hidden (Hide formulas), the formulas in the cells will be hidden.
If you want to keep the ability to edit cells after the sheet is protected, then uncheck both options!
If you decide to use protected, hidden and open cells on one sheet, then it would be more appropriate to use MS Excel styles. For more detailed instructions on applying styles, see the corresponding article.
You can also access this tool from the tab Review (Review) > Changes (Changes) > Protect Worksheet (Protect sheet).
In addition, to install and remove protection from an Excel sheet, there is a quick key combination – Alt+TPP (Alt+RZT).
Protecting the entire workbook
This tool serves to protect the settings of the book, i.e. the order of the sheets, the ability to manage hidden sheets, and the ability to delete or add new sheets.
To activate workbook protection, click Fillet (File) > Info (Details) > Protect Workbook Structure (Protect book structure). A dialog box will open Protect Structure and Windows (Protection of structure and windows):
Note: You can enable frame and/or window protection, they are not mutually exclusive.
- Structure Protection – this is the protection of the sequence of sheets, protection from hiding and showing hidden sheets, deleting and adding new sheets.
- Window protection – This is protection against changes in the size and position of windows. For example, protection from moving, resizing and closing windows.
As in the previous case, you can set a password if you wish.
These settings can be accessed the old way: Review (Review) > Changes (Changes) > Protect Workbook (Protect the book).
Also, to set workbook protection in Excel, there is a shortcut key Alt+TPW (Alt+RZB).
Adding a Digital Signature to an Excel File
One of the new options added to the security portfolio is adding a digital signature (also known as a Digital ID) to your file.
To fully use this tool, you will need a digital ID issued by an e-certificate authority. For more information about e-certification bodies, please consult the Microsoft Office website.
This tool guarantees the authenticity, integrity, non-repudiation and notarizes your file by adding a digital signature to it.
To use this feature, click Add a Digital Signature (Add Digital Signature) tab Fillet (File) in section Info (Intelligence). A dialog box for working with a digital signature will open, as shown below.
- You can specify the purpose of attaching a digital signature to a document (optional).
- You can change the default digital signature offered to the user.
- When you are happy with your settings, click Sign (Sign) to receive this reminder:
- Press OK and note that the digital signature information has appeared on the tab Fillet (File) in section Info (Intelligence).
Also, note that the file was also marked as final, since it goes without saying that the digital signature indicates that nothing has changed since it was added.
If a file with a digital signature is changed in any way, the digital signature will be removed and the following warning may appear:
How to remove protection
The following example shows how to remove encryption protection from an Excel file. To remove any of the security options in Microsoft Excel, follow the steps in the following example only for the security option you want.
Example: Removing the Encryption Password from an Excel File
- Press Fillet (File) > Info (Details) > Permissions (Protect books) > Encrypt with Password (Encrypt with password), a dialog box will appear Encrypt Document (Document encryption).
- Delete any passwords you see there and click OK.
I hope this short article has made more clear the various protections available today in Microsoft Excel.
I find it very convenient to have all the protection tools in one place, except for the fact that there is no password protection from making changes. Of course, now I am reconsidering my attitude to working with protection tools in Excel, and in the process of writing this article, the thought came to me: am I too jealous of my files?!
How and why do you use Microsoft Excel protection tools? If you have any ideas or suggestions, please leave them in the comments.