Protecting your Excel files is important, especially when they represent months of dedicated work. Whether you're protecting your intellectual property, monetizing your data model, or simply preventing user errors, there are several reasons why you might want to password protect Excel workbooks.
Microsoft Excel offers various ways to password protect your spreadsheets in the form of built-in features and VBA code. Although there really is no cookie-cutter approach to absolutely make sure a workbook is completely secure, the methods we’re going to cover in this article are a good start. The biggest culprits in this half-promise are the availability of brute-force password decryption tools, and the way Excel keeps data in files.
In this blog post, we will show how to protect Excel spreadsheets and limit certain features to your users.
Protect Excel Files with "Very" Hidden Worksheets
Discover the hidden potential of Excel with the unconventional but powerful feature known as "very" hidden worksheets. This might sound like a typo, but it's an Excel term that can significantly enhance your data protection strategies.
Before we dive into the depths of "very" hidden worksheets, let's revisit the conventional hiding method. You probably already know that you can hide a worksheet by simply right-clicking on the sheet name and selecting the 'Hide' option.
Simple, right? It’s also just as easy to unhide a sheet. All you need to do is right click again and select Unhide instead.
Now, what sets "very" hidden worksheets apart? Unlike the traditional hiding method, worksheets concealed as "very" hidden cannot be easily revealed by right-clicking and unhiding. Excel labels this unique worksheet state as "very hidden" under the constant name xlVeryHidden.
This property must be set using the Visual Basic for Applications (VBA) module of Excel. By default, this feature is disabled and need to be activated from the Option menu. To open VBA window, press the Alt + F11 key combination. You will see a new window with sheet names and properties at the left-side panel. Click on a sheet name you want to hide (in the example below we selected Sheet2) and select 2 – xlSheetVeryHidden.
Once a sheet is made ‘very hidden’, the option to unhide it will not be available in the sheet options menu when you right click.
Even if there is a ‘normally’ hidden sheet, we don't see our ‘very hidden’ sheet at the list.
This is a very useful feature for when you want to truly hide a worksheet, because the sheet will not be accessible to most Excel users. However, this method will only offer protection against those who are not familiar with the "very hidden" worksheet feature and VBA. There is another method that allows hiding worksheets in VBA window as well, but we will come to that later.
Protect Worksheet and Workbook
When it comes to safeguarding your Excel files, employing the Protect feature is a fundamental step towards creating read-only spreadsheets.
This technique effectively prevents unauthorized users from altering cells, worksheets, or workbook elements by implementing password protection. Access the Protect feature conveniently under the REVIEW tab in the Changes section.
The Protect Sheet menu provides a range of options to fine-tune the end-user experience and secure workbook access. This involves locking only specific cells marked as Locked. Excel will display warning icons on cells with unlocked formulas, offering visual cues for enhanced security.
Protect Sheet feature locks only cells that are marked as Locked. If your cells are not locked, they will allow editing by anyone. To assist you with this process, Excel will place warning icons on cells with formulas that are not locked.
You check or lock/unlock cells from the Protection tab in the Format Cells window. The Format Cells window can be accessed by right-clicking a cell, and then clicking the Format Cells option.
In addition to Protect Sheet, the Protect Workbook window offers crucial options—Structure and Windows. Protecting the Structure prevents users from modifying sheet names, positions, or visibility, while also restricting the addition or deletion of worksheets.
Password protecting will lock also "normal hidden sheets". The "very hidden sheets" and VBA methods become redundant at this point, because all worksheet related options will be disabled.
The Windows option is available only in Excel 2007, Excel 2010, Excel for Mac 2011, and Excel 2016 for Mac. This option prevents users from moving, resizing, closing, and hiding/unhiding workbook windows.
You might be thinking that these measures will keep your workbook safe. However, it’s always safe to assume that Protect Sheet and Protect Workbook passwords can be cracked. There are 2 known ways to do it:
- Brute Force Method
- Modifying file XML
The Brute Force Method is the most straightforward way of cracking password protected Excel files. This essentially means trying every possible password combination until you get the right one, very much like forcing a regular lock. A long and complex password with special characters will make it longer to crack as the combinations that need to be tested increase rapidly. However, it’s not a good idea to assume that your password is unbreakable.
Excel uses a hash algorithm to store passwords. The hash algorithm encrypts the password to make it not look like your actual password when it’s stored in your file. The reason behind this is to block users from reaching password databases or the XML file to find the actual password characters. The encrypted password can be decrypted only by using the same hash algorithm that was used to encrypt it.
The flaw in this approach is that different passwords may generate the same hash values. Essentially, Excel's hash algorithm isn’t very sophisticated to allow for better performance. The image below shows how a password is kept inside the workbook.
Note: Regardless of obvious threats, we recommend using a long complex password that contains special characters, because doing so will certainly increase the time it takes for anyone to crack it.
Modifying XML method is where you really crack the password not try to predict it. Microsoft Office files have started using new file formats with additional letters at the end (i.e. *.xlsx, *xlsm, *.docx). These new file types work very much like compressed archive files. If you haven’t already, try renaming an XLSX to change its file extension to ZIP and then open it. You will see folders and files just like in a regular compressed content folder.
Workbook.xml file contains the workbook data, while files under the worksheets folder contain the XML files. If you open an XML file with a word processor (i.e. Notepad) you will see the XML tags like from our encrypted password example before. You can even modify XML files directly from here. If you delete the sheetProtection tag in the XML file, save the file somewhere else, and replace with the original one, you’ve got yourself an unprotected file!
Modify/Read-Only Password
You can save your file with a password to ensure that the file remains in read-only mode. The difference between this approach and Protect method is that while Protect method allows you to lock specific cells or actions, Modify Password method prevents all kinds of updates (except for the Save As feature). To add a password and only allow modifying the workbook,
- Open the Save As
- Click General Options under the Tools menu
- Type in the password into the Password to modify box
- Click OK and then re-enter your password
- Save
Protect Excel Files with Check File Location
While a Modify/Read-Only Password adds an extra layer of protection, users may still attempt to open your file in read-only mode and save it elsewhere. Although this method requires a bit of VBA knowledge, you can use the code below to prevent users from copying your workbook.
Sub LocationCheck ()
Dim MyPath As String
MyPath = "C:\User\Desktop\Workbook Security"
If Application.ThisWorkbook.Path = MyPath Then
UnProtectMe
Else
MsgBox "File location is wrong."
End If
End Sub
This VBA code compares the current file location with the intended one. If the locations match, the code proceeds to execute under the name of ProtectMe; otherwise, it alerts the user about the incorrect directory.
You can enhance this protection by placing the LocationCheck sub-routine under Workbook_Open or Workbook_BeforeSave events, effectively password-protecting Excel during file open and before saving. These events can be created under the code section (ThisWorkbook).
When using the VBA methods, all options mentioned under the Protect Worksheet and Workbook section can be applied here. All of these options are only reliable if other users do not know how to use VBA. To take it one step further let’s take a look at how to password protect Excel via VBA code and properties. In VBA window,
- Click Tools
- Click VBAProject Properties (VBAProject is a default name for a project, it can be edited in the same window)
- Go to the Protection tab
- Check Lock project for viewing
- Enter your password into the Password field
- Click OK to save
These changes become effective upon closing and reopening the workbook, triggering a password prompt.
As we mentioned in a previous section, VBA codes and passwords are kept in a compressed file format. The difference is that they are not stored in XML files but inside a BIN file (i.e. vbaProject.bin). BIN files are encoded in the HEX system and need a special HEX editor to read and edit.
Our encrypted password come after DPB=. If someone changes DPB to DPx and update the BIN file in the compressed archive, Excel will give an error saying that file contains an invalid key.
Click Yes, save the file and re-open it. The VBA code will be available.
Protect Excel Files with Open Password
All other methods we’ve covered in this article are for limiting access of another user. These methods are good for the situations where we want users to see our file, but not copy or change it. If you want prevent all access to your file (including seeing its contents) you can use the Open Password feature to password protect Excel files, which prevents opening the file without a password. Excel encrypts the ZIP archive as well, therefore users cannot modify the XML or BIN files to crack the password. Although this is one of the best methods available to protect files, please keep in mind that this method is still vulnerable to brute force hacking.
To implement the Open Password feature, follow the same steps outlined in the Modify/Read-Only Password method, but this time, enter a password into the "Password to open" textbox:
- Open Save As
- Click on General Options under Tools
- Fill the Password to open box with password
- Click OK and re-enter your password
- Save
When you re-open the workbook, you will get a password prompt and no other options.
By applying an Open Password, Excel not only encrypts the ZIP archive but also secures XML and BIN files, making it challenging for users to modify these files to crack the password. It stands as one of the most robust methods available for safeguarding your Excel files.
By integrating these advanced security measures, you can significantly enhance the protection of your Excel files, catering to various levels of user expertise and safeguarding against potential threats.
