Last updated on July 29th, 2018
We have already covered worksheet level protection and workbook level protection extensively in a previous tutorial. We are now going to cover securing your Excel files using a password and encryption. We will compare all the different options available for safeguarding sensitive data and workbooks in Excel.
So, let’s get started with a brief recap of worksheet level protection and workbook level protection and then move on to the advanced actual security options for securing your data – namely requiring a password to open your Excel file and encrypting your Excel file with a password.
Table of Contents
- Worksheet Level Protection
- Workbook Level Protection
- The Level of Protection Offered By Worksheet Level and Workbook Level Protection Debate
- Setting a Password in Order to Control who can Open Your Workbook
- Encrypting your Excel File
- The Security Hierarchy
- Download Zone
- Useful Protection related Links from ExcelDemy
- Review Section: Test your Understanding
- Read More…
Worksheet Level Protection
Excel worksheets frequently contain certain information or data/formatting/functions which you would not want the users, viewing the workbook, to edit or change. Worksheet level protection happens at the cell level. In order to protect an Excel worksheet, go to Review>Changes>Protect Sheet.
By default, all cells in one’s worksheet are locked. You can unlock certain cells or keep all the cells in your worksheet locked. When protecting your sheet you can choose whether or not to password protect the sheet, and exactly which permissions you will allow your users to have. For example, you can specify that users are only allowed to select locked cells or select unlocked cells but they cannot format cells for example if you want to protect the formatting. You can also ensure that they are not allowed to delete columns or rows by making sure that this option is unchecked.
You can also choose to uncheck both the Select locked cells option and the Select unlocked cells option and in this way, the user will not be allowed to even select the locked cells or unlocked cells in your worksheet. They will basically only be able to view the data in the worksheet. It is advisable to always set a password, since if you do not – any user will be able to toggle sheet protection off easily by going to Review>Changes>Unprotect sheet.
You can also use this method to hide formulas, if let’s say you want a particular formula, in a certain cell to be hidden in the Formula bar. Right-click the cell of interest that contains the formula and choose Format Cells…>Select the Protection tab and then check Hidden. Once you protect your worksheet, the formula in that cell will be hidden and not viewable from the Formula bar.
To edit the worksheet freely again and view the hidden formulas, you will need to unprotect the sheet and enter the password (if a password was specified).
*Remember when formulating any password to follow best practices and ensure that the password is strong and contains at least 10 characters, with a mix of uppercase letters, lowercase letters, numbers and special characters. Your password should also not be based on any previous passwords you have used, nor infer to background details that can be found online such as your former high school or college.
You can also use a password generator to generate a password for you, and there are a number of good free online options available*
So, just remember that locking one’s worksheet involves cell level and cell range level restrictions. Also, remember that when you protect your worksheet in this manner, users will still be able to open your Excel workbook and view the data in it. Also, the passwords set in this manner can be easily breached using VBA code, then your sheet could potentially be unlocked by someone that did not have access to the password and thus edited accordingly.
Workbook Level Protection
Workbook level protection is actually all about worksheets. Yes, that can be a little bit confusing when first learning about worksheet level versus workbook level protection. However, in order to prevent users from deleting worksheets in your workbook, viewing hidden sheets, adding, moving or renaming sheets – you have to protect your workbook or more accurately the structure of your workbook.
In order to protect the structure of your workbook, you simply go to Review>Changes and then select Protect Workbook. You can enter a password or choose to not enter a password (and as in the case above if you do not enter a password this protection can then easily be toggled off). If you enter a password the same good practices should be followed as listed above. Your workbook structure is then protected, and the options to add, remove, rename or delete sheets will now be unavailable. Again the passwords can be easily breached using VBA code. So let’s look at a situation where you would use workbook level protection.
So, let’s say you have all your named ranges stored on one sheet (that you are accessing user data validation on another sheet), and you would not like the average user to view this sheet. You can hide the sheet containing all your named ranges and then protect your workbook with a password in order to ensure that – that specific sheet is not viewable by the mainstream user.
If you want to get really fancy in terms of hiding sheets and make sure that a certain sheet cannot at all be unhidden from the Excel user interface, you can use the Visual Basic Editor (VBE). We have a source workbook with three sheets as shown below, we are going to hide the second worksheet in the normal way and then we are going to use the VBE option and set the very hidden property, in order to ensure that the third sheet cannot be unhidden from the Excel interface.
1) In order to hide the sheet called SecondSheet in the standard way, right-click this sheet and select Hide as shown below.
2) The sheet will now be hidden as shown below.
3) You can unhide this sheet easily (assuming your workbook has not been protected – but if it has been protected – unprotect your workbook), by right-clicking on one of the visible sheets and selecting Unhide… then choose the sheet you’d like to Unhide using the Unhide Dialog Box and click Ok.
4) The second sheet is now visible again.
5) In order to use the VBE to hide the sheet called ThirdSheet, so that it cannot be unhidden from the Excel user interface, press Alt + F11 in order to enter the VBE.
6) Select the sheet called ThirdSheet using the VBAProject window as shown below.
7) Using the Properties Window change the Visible Property to xlSheetVeryHidden.
8) Now return to the workbook by pressing Alt + F11. You will find that your third sheet has been very hidden. In terms of, if you try and unhide it using the method we used above – by right-clicking the second sheet, the Unhide… option won’t even appear there. You will only be able to unhide the third sheet using the VBE. Plus you can save the workbook as a normal Excel file and not a macro-enabled file and the third sheet will still stay very hidden.
9) So, that is just an extra little tip in order to really hide your sheets using the VBE.
The Level of Protection Offered By Worksheet Level and Workbook Level Protection Debate[Note: Thanks to commentator Agnius who added the following notes with respect to worksheet and workbook level protection:
“This kind of protection can be easily breached by VBA and thus is not in any way safe. However, that is something one has to live with when facing a situation where user input is needed. A better way to save Excel files from unwanted editing is by encrypting it so that a person who does not know the password for the workbook cannot even open it, or can only open it as a read-only file. This kind of protection cannot be broken by VBA or any other means. It can be done by accessing the General Options in the Save As dialog window”]
It is indeed true that the protection offered by worksheet level and workbook level protection is elementary and rudimentary. However, my counter-point at the time is that it really depends on your security/editing needs. If the data needing to be protected from editing is not that sensitive and it’s a surface level protection required, that is more psychological than technological, then worksheet and workbook level is fairly adequate. However, as mentioned worksheet and workbook level protection are not regarded as real security by most experts. So let’s dive into more advanced methods of safeguarding your Excel workbooks.
Read More: How to Make an Excel File Read Only
Setting a Password in Order to Control who can Open Your Workbook
We have a hypothetical company called ABC, that stores the salary data of all its employees in an Excel workbook. Now, needless to say, this information is sensitive and the managers would not want just anybody to be able to open the workbook. The source workbook is shown below.
1) In order to set a password to open the workbook, go to File>Save As.
2) Select a location, in this case we’ll choose Computer and select the My Documents Folder.
3) The Save As Dialog Box should appear, click on the drop-down arrow next to Tools.
4) Select General Options…
Read More: About Macro Security in Excel
5) In the General Options Dialog Box, in the Password to open: textbox set the password. In this case, we’ll set a password of #Welcome1! and then click Ok. (If you want to set a password for modifying – so users without the password can’t modify and edit the workbook– you can set this password here using the Password to modify: textbox)
6) You will be asked to re-enter the password, so re-enter the above password and then click Ok.
7) Now click Save.
8) Now close the workbook and navigate to the folder you stored the workbook in and open it.
9) You will now be asked, for the needed password, in order to open the workbook as shown below.
10) Now, unfortunately, this method is fairly easy to crack using the many passwords cracking programmes available online. While it does provide actual security and is better than protecting one’s worksheet and workbook, it is still not that secure. One other method that is often utilized involves removing the password totally, by first converting the file extension to a zip file and then editing the XML options.
Read More: 3D Referencing & External Reference in Excel
Encrypting your Excel File
A more secure way of preventing access to your Excel workbook is through encryption. So, let’s look at how to encrypt your Excel files. Our source workbook is shown below, in addition to the salary data of employees at our hypothetical company ABC, this workbook also contains sensitive employee ratings.
1) In order to encrypt this workbook, and prevent users without the password from opening it, go to File>Info>Protect Workbook and choose Encrypt with Password from the options given.
2) Using the Encrypt Document Dialog Box, enter the following password: #haPPseen@!2
3) You will be asked to confirm the password, so re-enter the password and click Ok.
4) You will now be informed that a password is required in order to open the workbook. You can close the workbook.
5) When you try and open the workbook, you will get the following message box.
6) Enter the password and click ok in order to open the workbook.
The Security Hierarchy
Protecting one’s worksheet and workbook is not regarded as real security since this kind of protection is easily breached using VBA or password cracking software. This kind of protection still allows users to view the workbook at hand for the most part. In fact, this is surface level protection rather than true security. However, you do need to have at least a passing familiarity with it, if you intend to do certification exams and what it is used to do.
It does have its place in protecting and safeguarding data, since if you basically just want to stop users from editing, viewing certain formulas, formatting and deleting sheets accidentally and the actual data is not that sensitive then this should be your go-to option.
In addition, if you do forget the password you used to protect your worksheet/workbook, password-cracking software is easily available and you can use this to retrieve your password or use VBA code.
Okay then onto the next level of true security, you can specify that your Excel workbook can only be opened if the user has the password- specified by using the Save As option. However, this method can be cracked using password cracking software and by converting your Excel file extension to a zip file extension, extracting and then editing the XML options.
When one encrypts the actual Excel file, one needs more advanced high-level password cracking techniques – and it takes much longer. In addition, the zip file method will not work if you actually encrypt your workbook.
The choice you make really depends on the type of data you are storing in your workbook, for non-sensitive data and in the case where you want to stop users from accidentally deleting sheets and you want to hide formulas– use worksheet and workbook level protection. Once you have sensitive data such as salary data – use the encryption option rather. Only remember that if you do encrypt and forget your password, you won’t be able to access the data either. Also, remember for highly sensitive data – such as all the security codes to a biosafety facility, Excel would not be the go-to option for such storage purposes. So, remember that if the nature of the data you are storing is highly sensitive in nature then an Excel workbook is not the option for you.
Also, bear in mind that older versions of Excel have weaker encryption than later versions of Excel. Microsoft improved the encryption technique used for encrypting Excel files in 2013 and later versions, so if you are working with an earlier version of Excel, consider additional security features for sensitive data.
Microsoft has provided– pretty secure means of safeguarding one’s data through encryption, in later versions of Excel. The downside is, of course, if you lose the password you won’t easily be able to retrieve the data. So choose carefully and wisely.
Please feel free to comment and tell us which options you use to prevent editing and/or securing your data.
Review Section: Test your Understanding
1) If you want to hide the formula in a certain cell, should you (after making sure the cell’s hidden option is checked in the Protection tab of the Format Cells…Dialog Box), protect your worksheet or protect your workbook?
2) If your workbook does not contain sensitive data but you just want to stop users from accidentally deleting worksheets – what level of protection should you use?
3) What does setting the visible property of a sheet to xlVeryHidden in the VBE do?
4) What is the difference between using the Save As the method to set a password to open your workbook, and setting a password using encryption to open your workbook?
5) Is the encryption method used, the same for all versions of Excel?