Some Excel files are the result of months of development and you may not want to pass this work to everyone lightly. Be it for 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.

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. Let’s take a look at how you can protect your spreadsheets and limit certain features to your users.

"Very" Hidden Worksheets

‘very’ hidden worksheet might look like a typo, but it’s just what Excel calls this method. First, let’s take a look at the “not-so-very” hide option. You may already know that you can hide a worksheet by right-clicking the sheet name and clicking Hide.

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.

So, what is with the “very” hidden? This is an alternative method to hide a worksheet, and worksheets hidden this way can't be made visible again by right-clicking and unhiding. Excel calls this worksheet state “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

Using the Protect feature is a pretty straightforward way to password protect Excel files by creating read-only spreadsheets. With this, you can prevent users from changing any cell, worksheet, or workbook elements by password protecting. The Protect feature can be found under the REVIEW tab in Changes section.

Protect Sheet menu gives you several options to configure the end-user experience. Here, you can fine-tune workbook access by selecting allowed features from the list.

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 the Protect Sheet feature, Protect Workbook window gives you two options, Structure and Windows. By protecting the Structure of a workbook, you can prevent users from modifying sheet names, position, or visibility. This will also lock the ability to add or delete any 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

Check File Location

Even if you have a modify password set up, other users can still open your file in read-only mode and save it somewhere else. 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 code compares the current location of the file with where it’s supposed to be. If the location is correct, it will then run a code under the name of ProtectMe, otherwise it will send a message to the user saying that the file is in the wrong directory.

If you place the LocationCheck sub routine under Workbook_Open or Workbook_BeforeSave events, you can password protect Excel on the ‘file open step’ and before save. Workbook_Open or Workbook_BeforeSave events can be created under this code (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

Changes will take effect when you close and then re-open the workbook, you will be prompted with a password screen.

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.

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.

Follow the same steps from the Modify/Read-Only Password method, but this time, enter a password into Password to open: textbox.