And it was incredibly easy. Some background: I received a Microsoft Excel spreadsheet (xlsx file) from someone at my company that was struggling with incorrect numbers. The spreadsheet was password-protected. I noticed one of the formulas wasn’t calculating correctly and giving a percentage of a score that they had a 0%, which was incorrect. This was something they had to complete quarterly, and each tab represented a different quarter. I noticed that on the tabs for the first two quarters, the formula for calculating the percentage was completely different than the last two quarters. I thought that if I updated the formulas to correctly calculate their score, then everything would work fine. Unfortunately, the formulas were password-protected.
This wasn’t the first for coming across this issue, especially since no one knows who designed these so no one knows the password. In the past all I did was run a VBA code in the spreadsheet and it would crack the password. This worked great for old Excel files (xls) before Microsoft Excel 2010. It doesn’t work so great with Excel 2013 or newer.
I searched around for VBA scripts that would work similarly to the old days. I found a few (like this one), so I tried each of them. While it was running, I wondered if the password would be in the Excel file somewhere. Before I go on, for privacy reasons, I’m renaming the files and changing some information. The first thing I did was open the Excel file in a text editor:
Obviously the file was encrypted. But, I noticed something at the top-right:
Types, as in plural. These two hints (encryption + types) means that this is most likely an archive file. So, I changed the file extension from “.xlsx” to “.zip”.
“Yes,” I obviously chose. Opening it up you can see I was right!
I wandered into the xl folder first to see if I could see anything of interest. Immediately, I saw a “workbook.xml”. Going back to a few steps: Remember the Content_Types xml? This is what it is referring to. The workbook and all its settings are stored in separate xml files, which is basically a format used for structures (think of a dictionary).
Within the file, I searched for “Password” and “Protect”. No luck – my guess is because the whole document isn’t protected – rather, only individual sheets are. I didn’t look too much at the file, but nothing passwordy spoke to me. The next step I did was look in the Worksheets folder. I found the sheet number related to the sheet that the person was having issues with. For future reference, you can see this in Excel by pressing Alt + F11. I searched “Prot” (for protection) and found this:
I blurred some of it for obvious reasons. What this line tells me is the following:
- Excel uses SHA-512 encryption method
- It stores both the hash and salt in the file
I discussed more about encryption methods, hashes, and salts in my Lastpass Security Breach article. What this doesn’t mean is that this tells you the password. Unfortunately, I would have run hundreds of thousands of calculations to determine which combinations equal the hash and salt together (called brute force). It’s not a huge security risk, but much easier since most hackers often don’t know the salt. Decrypting the password would still be painful.
Rather than doing that, I deleted it. I deleted everything from “<sheetProtection” until you see “/>”
So this section:
<c r="G31" s="82"/><c r="H31" s="82"/></row></sheetData><sheetProtection algorithmName="SHA-512" hashValue="TZ1H4K1ykLNFDKUoTSEAtk54518Fw4czQ90YNiv1Kt8xmGlM2esYsspup7T7y1lJLoGQhbbfXxP5hwqCWB3V2Q==" saltValue="vcjJULd0LBbps9vROHJokA==" spinCount="100000" sheet="1" objects="1" scenarios="1" formatColumns="0" formatRows="0"/><mergeCells count="7">
Now looks like:
<c r="G31" s="82"/><c r="H31" s="82"/></row></sheetData><mergeCells count="7">
Then, I saved the file (sheet3.xml) I had open. Finally, I went back to the zip file, and changed the extension back to “.xlsx”.
The password was gone!
I could finally correct the formulas and send the updated spreadsheet to the person who made the initial request. By the way, the VBA script was still running in the time I did all of this.
Thanks for reading.