March 9

For Excel version 2010 and below, an easy VBA macro can be used to generate a compatible password to “un-protect” a worksheet. The macro that is floating around the internet does not actually reveal the password, but generates another password that will authenticate the same way to Excel. This was possible due to a weakness in the excel password system where it hashed passwords into a weak algorithm (Explanation Here).

For later version of Excel, “un-protecting” a worksheet became a little tougher, but still doable. The reason it’s possible to remove the sheet protection is because protection does not equal encryption, a protected sheet is not encrypted. Encryption is a totally different animal and not one that I have an easy answer for. But if you’re just looking to remove sheet protection, then chances are it can be done.

The solution lies in making excel think there’s no protection at all, instead of having to guess a password. Before we can do this, we have to understand the structure of your typical modern excel file, and that’s easy! Your typical modern excel file, like other Microsoft Office documents, is simply a zip file! So if you change the extension of your excel document from something like “xlsx” or “xlsm” to “zip”, you can easily open it in a zip-file viewer and view its contents. Personally, I prefer to use 7-zip, which lets me open the file as a compressed file, without having to change the extension.

Once you’re able to view the contents of the zip file, navigate to the “xl” folder, then the “worksheets” folder. The full path will look something like “excelfilename\xl\worksheets”. Here you will see a bunch of xml files, one for each sheet in your workbook. If you open up the xml file in a text or xml editor, notepad++ for me, you’ll be able to see the raw data, in xml format, that composes your worksheet, including settings for that worksheet. On a protected sheet, you’ll be able to search for “sheetProtection” (without quotes) and you’ll find the XML key that tells Excel that this particular sheet is protected. Removing the protection is a simple as removing this XML key. Here is a sample XML key, simply select it, and remove it completely. Your key will be different, but the idea is to look for “sheetProtection”:

<sheetProtection algorithmName=”SHA-512″ hashValue=”kCXjmrTgwRXgMLSoZjx5f3XTt8ycTWnf3DLZLUBtCKgx2VmWzN1A2DDv0bEKG+4uT+7CENmR6KTYmxU3pCIuOg==” saltValue=”dUkmxPPBbFZ8PjKxfnl1xQ==” spinCount=”100000″ sheet=”1″ objects=”1″ scenarios=”1″ formatCells=”0″ formatColumns=”0″ formatRows=”0″ insertHyperlinks=”0″ selectLockedCells=”1″ pivotTables=”0″/>

 

More info on Excel Security:

  • https://www.thespreadsheetguru.com/blog/2014/8/20/understanding-excels-password-security-methodology
  • http://www.spreadsheet1.com/sheet-protection-2013.html

 

More tips on removing Excel Security:

  • https://excel.tips.net/T002776_Unlocking_a_Worksheet_with_an_Unknown_Password.html

 

Time tested Excel Password Removal Macro and a brief explanation of why it works:

  • http://stackoverflow.com/questions/25964762/understanding-how-a-password-breaker-works

 

Another Excel Password Removal Macro (untested):

  • http://www.mcgimpsey.com/excel/removepwords.html