Unprotect an Excel sheet when you have lost the password

If Excel opens the workbook and only refuses edits (Review → Unprotect Sheet asks for a password), the protection is a flag in the file, not encryption. The cells are stored in plain XML inside the .xlsx, and the password is kept only as a hash for Excel to compare against. Delete the flag from a copy and the sheet is unprotected; no password is found or needed. If Excel asks for a password before it will show anything at all, the file is encrypted, and without the password there is no honest way in. OFFICEKEY takes the flag off a copy of your own file in the browser tab, and says plainly that it does not guess passwords.

Which kind you have

Microsoft’s own help says the first three are not security: sheet and workbook protection exist to stop accidental changes, and it warns that they do not protect sensitive data. Only the fourth encrypts.

Why sheet protection comes off

An .xlsx is a zip of XML files. Each worksheet is a part such as xl/worksheets/sheet1.xml, and a protected one carries an element like this:

<sheetProtection algorithmName="SHA-512" hashValue="…" saltValue="…" spinCount="100000" sheet="1" objects="1" scenarios="1"/>

The hash is of the password, so Excel can check what you type. It protects nothing else: the cell values sit a few lines further down, unencrypted, readable by any program that opens the zip. Workbook structure protection is the same idea, a workbookProtection element in xl/workbook.xml.

Older files, and some written by other programs, store a 16-bit legacy hash instead, password="CC1A" or similar. A 16-bit hash has only tens of thousands of possible values, so a great many different passwords match any one of them. That is why the widely copied VBA macro "finds" a password like AAABBBAAAAB^ that is nothing like yours: it tries short strings until one gives the same hash. On a modern SHA-512 hash that macro does not work, which is why the method that always works is removing the element.

Doing it by hand

1. Make a copy of the workbook. Work only on the copy.
2. Rename the copy from .xlsx to .zip. (A macro workbook, .xlsm, works the same way.)
3. Open it with 7-Zip or another zip program that can edit in place, go to xl/worksheets, and open the protected sheet’s XML in a text editor. Sheets are numbered in the order they were created, which may not be the order of the tabs.
4. Delete the whole <sheetProtection … /> element, from < to />, and save the file back into the zip.
5. Rename it back to .xlsx and open it in Excel.

The same edit in xl/workbook.xml, removing <workbookProtection … />, lifts structure protection. Windows’ built-in zip folder view will show the parts but not let you edit one in place, which is where most attempts at this go wrong.

Copying the cells into a new workbook, which is often suggested, loses validation, comments and formulas that refer to other sheets, and fails outright if the author also set locked cells not to be selectable.

Doing it in the browser

Drop the file on OFFICEKEY. It reads the .docx, .xlsx or .pptx in the tab, lists the restrictions it finds (sheet protection on each sheet, workbook protection, Word’s restrict editing, read-only recommended, a password to modify) and saves a copy with them taken out, leaving everything else in the file as it was. The file is read on your own device and not sent anywhere.

It handles the formats Office has used since 2007. The older binary .xls uses different schemes it does not read; open the .xls in Excel and save it as .xlsx first, which Excel allows because protection does not stop Save As.

Opening and checking the file is free. Saving the unprotected copy is part of OBSCURA Pro, or US$1 to save from one tool for a day.

When the file asks for a password to open

That is encryption. Excel 2010 and later use AES with the key derived from the password by 100,000 rounds of SHA-512, and the contents of the file are unreadable without it. Tools that claim to "remove" this password are guessing it, trying lists and patterns, which can take anywhere from seconds for a short dictionary word to far longer than anyone will wait for a strong one. OFFICEKEY does not guess. If you do know the password and want it gone, give it the password and it saves a decrypted copy; or in Excel, File → Info → Protect Workbook → Encrypt with Password, clear the box and save.

Whose file it is

These steps are for workbooks you own or have the right to change: your own old files, a template your organisation owns whose password left with the person who set it. A protected sheet is often a message from its author about which cells not to touch. If it is somebody else’s workbook, ask them; the protection took seconds to add and will take seconds for them to lift.