“Unprotect” a sheet or a workbook in Excel

Microsoft developers’ ignorance has hit jackpot one more time. If you “protect” a sheet or workbook, Excel only places a special tag inside .xlsx file. Changing it and thus “unprotecting” such file is a piece of cake.

You can remove it in a matter of two minutes using software like Total Commander or 7-Zip. By doing so, you can gain access to “protected” sheet or workbook without knowing a password. Something that in theory should never happen.

In previous versions of Excel (Office) that were using old .xls file you had to work with some nasty, multi-level macro to unprotect a protected file. Now they have made even easier. Instead of using some dully macros all you need is a text editor and a file to unpack and repack archive.

You need:

• an “protected” Excel file, in .xlsx format,
• Total Commander or 7-Zip or any other tool that allows you to edit .zip files on-the-fly.

Yes, .zip files, because (in case you wouldn’t know) any Office file is actually a .zip archive.

Preparation

Follow these steps to both “unprotect” a sheet or an entire workbook:

1. If you’re using a Total Commander:
   • if you have Office installed and .xlsx files associated with it:
     • select a file and hit Alt + F9 to unpack its content to some folder,
     • make necessary modifications (see next two sections),
     • select all unpacked files, including modified one and pack it back with Alt + F5,
     • rename created archive from .zip to .xlsx.
   • if you don’t have Office installed of .xlsx files associated with, simply hit Enter to view contents of archive.
2. If you’re using a 7-Zip, right-click any .xlsx file and select 7-Zip –> Open archive.

You should see a [Content_Types].xml file and three folders: _rels, docProps and xl. Open it up.

Unprotecting a sheet

1. Open up xl folder and a worksheets subfolder. Locate file which name corresponds to sheet’s name, i.e. sheet1.xml.
2. Hit F4 (in both cases: i.e. in Total Commander and in 7-Zip) to edit this file.
3. Find a piece of text that starts with <sheetProtection algorithmName= and ends with scenarios=”1” />.
4. Select and remove entire sheetProtection tag (piece of text).
5. Save changes to modified file and exit editor.
6. Confirm archive repacking, if working on-the-fly or pack back file, as described above.

Unprotecting an entire workbook

1. Open up xl folder and locate a workbook.xml file.
2. Hit F4 (in both cases: i.e. in Total Commander and in 7-Zip) to edit it.
3. Find a text that starts with <workbookProtection algorithmName= and ends with lockStructure=”1” />.
4. Select and remove entire workbookProtection tag (piece of text).
5. Save changes to modified file and exit editor.
6. Confirm archive repacking, if working on-the-fly or pack back file, as described above.

Even more dirty work

Methods described in two above sections permanently remove “protection” from a single sheet or entire workbook. If you will won’t to protect it again, you’ll have to use a standard solutions in Excel — Protect Sheet or Protect Workbook from Review tab.

However, you’ll have to use your own password in this case. And it will be 99.99% different than used previously by originating workbook’s author. Which is bad for some nifty, dirty things.

But, you can remove protection temporarily, modify a sheet or a workbook and bring protection back.

To do so:

1. Follow above steps to remove protection of a sheet or a workbook.
2. In step 4 above carefully remember position of a sheetProtection or workbookProtection tag.
3. Copy this piece of text to clipboard or some other file instead of just removing it.
4. Save modified file and repack archive to get working .xlsx file.
5. Open this file in Excel and make all the changes you wish. Save the file.
6. Extract contents of .xlsx file to some folder or make on-the-fly edit again.
7. Locate position from which you copied sheetProtection or workbookProtection tag.
8. Paste copied piece of text from clipboard or some other file.
9. Save modified file and repack archive once more.

After this operation you should end up with a workbook or a sheet with your modifications, being “protected” with the same password as previously.

If you handle it back to anyone, who knows the original protection password, that person should be able to open protected file and see no difference (except for the content that you’ve modified).

Final words

The .xlsx format is available starting with Excel / Office 2007. Meaning that method described above is well known for about thirteen or so years. And Microsoft dumb asses did nothing to fix this so far. #loveMicrosoft

Credits for this article goes to: cBr & hintmine.com.