UPDATE: I’ve created a video that covers this topic here

One of the Excel-related support calls that I get on a regular basis is along the following lines… “Mike, I need to <insert action here> and it seems the workbook/worksheet is protected and it’s asking for a password and the person who set it up left the company months ago and I don’t know the password. Can you help please.”

When a user enables Workbook Protection or Worksheet Protection in an Excel file, they are offered the opportunity to set a password. If they or someone else tries to turn off protection (in order to make changes), they will be prompted to enter the password. But what if they don’t know the password? Read on…

Workbook protection affects the workbook structure. When Workbook Protection is on, certain operations are unavailable including adding, deleting, renaming, moving/copying hiding/unhiding worksheets.

Worksheet protection has many options (the full range of functionality that is unavailable in a protected worksheet is too much for this post) but the most common use is to prevent users typing into cells that they shouldn’t be typing into, overwriting formulas, for example. Worksheet protection can be turned on or off on a per-sheet basis.

But back to the point of this tutorial. When you set a protection password (worksheet or workbook) it does warn you “if you lose or forget the password, it cannot be recovered”, however that is not 100% true. Sure you can’t recover the password via Excel but there is a way…

Whether you need to turn off workbook or worksheet protection where a password has been set, the starting point is the same. Using File Explorer (Windows users) or Finder (Mac users) change the extension of the Excel file to ZIP

Then open the ZIP file by double clicking it. What happens next will depend on which platform you are using and what application you have configured to open ZIP files. On my Windows PC it opens File Explorer and displays the files and folders stored inside the ZIP file.

Removing Workbook Protection

• Open the folder called xl

• Find the file called workbook.xml and drag it to the Desktop
• Open workbook.xml using any text editor (Notepad on Windows, TextEdit on Mac will do just fine)
• Delete the WorkbookProtection section. The easiest way to do this is to use the Find feature of the text editor to search for WorkbookProtection, highlight the following text and press delete:

<workbookProtection workbookAlgorithmName="SHA-512" workbookHashValue="K+NkvQ9dsYiY089FtvRNo//jxtyU8X5in3mkGwBu2Bnk9dkUdLh0FLzIMKFIbFwFtGwX7Vh+cAmt59QBFxpKLg==" workbookSaltValue="uAlFbkAZAc5s6KCEa2vO/w==" workbookSpinCount="100000" lockStructure="1"/>

• Save and close the file and drag it from your Desktop back into the xl folder, replacing the existing version. The XML file on the Desktop can then be deleted
• Close the ZIP file and change file extension back to XLSX (or XLSM if it contains macros)
• Open the Excel file and you should find that workbook protection is no longer enabled for this file

Removing Worksheet Protection

Worksheet protection (and the associated password) is set on a per-sheet basis. So the first thing to determine is which sheet(s) needs to have the protection removed.

In the xl folder in the zip file is another folder called worksheets. Inside this folder you will find one XML file for each worksheet. Unfortunately the filenames do not necessarily match the names displayed on the worksheet tabs in Excel. The filenames will always be sheet1, sheet2, sheet3 etc.

The workbook.xml file located in the xl folder will help identify which of the Sheet XML files needs to be edited. Open workbook.xml directly from the zip file there’s no need to drag it onto the Desktop this time as no changes need to be made) and search the file for the word <sheets>

The Sheets section of the file displays the “tab name” and “internal sheet name “(known as the SheetID) of each worksheet so in the example code below, Sheet1.xml represents the sheet that has Manchester as it’s tab name.

<sheets><sheet name="Manchester" sheetId="1" r:id="rId1"/><sheet name="London" sheetId="2" r:id="rId2"/><sheet name="Glasgow" sheetId="3" r:id="rId3"/></sheets>

Once you have identified which worksheet you need to remove protection from:

• Close the workbook.xml file
• Open the folder called Worksheets located inside the xl folder
• Drag the appropriate XML file onto the Desktop and open it in a text editor
• Delete the SheetProtection section. The easiest way to do this is to use the Find feature of the text editor to search SheetProtection, highlight the following text and press delete:

<sheetProtection algorithmName=”SHA-512″ hashValue=”q4wZO1jcfB6bYaRvYli37e2Tcxp2zBahWXaF2aQe3E2G1SslCRcyLNgewDTqJJI+KVi6nsD5oYFm4Y7fks96VQ==” saltValue=”bWVSwEjbX5od/vEYrQzJgQ==” spinCount=”100000″ sheet=”1″ objects=”1″ scenarios=”1″/>

• Save and close the file and drag it from your Desktop back into the xl > worksheets folder, replacing the previous version. The XML file on the Desktop can be deleted now
• Close the ZIP file and change file extension back to XLSX (or XLSM if it contains macros)
• Open the Excel file and you should find that worksheet protection is no longer enabled for the selected worksheet