Tech Community Live: Windows edition
Jun 05 2024, 07:30 AM - 11:30 AM (PDT)
Microsoft Tech Community

How can I unlock an Excel spreadsheet that is locked for editing?

Copper Contributor

Hello everyone,

 

I'm in a bit of a bind and need some help here. I have an Excel spreadsheet that is currently locked for editing, and I need to unlock the sheet to make some necessary updates. Unfortunately, I don't have the password, and the person who originally locked it is not available to provide it.

 

I'm looking for methods or tools that can help me unlock this spreadsheet.  Has anyone here faced a similar situation and found a solution? I'm open to suggestions. The spreadsheet is work-related, and I have the right to access and edit it.

 

Any advice would be greatly appreciated!

 

Thank you in advance!

 

Best regards,
Apollo

6 Replies
Excel Workbook is a zip file, to unlock a protected sheet, I usually do following
(1) use zip tools to open Excel workbook
(2) find your sheet under path "xl\worksheets\", which shall be named by its sheetname with ".xml" suffix
(3) use text editor open it and find a tag named "sheetProtection", memo the contents of the tag , delete it from the file
(4) save the xml and replace it in your workbook using zip tools
(5) you can edit the sheet freely
(6) after editing, paste back "sheetProtection" using same approach above, the sheet will be protected by same password again (note, sheetprotection should be placed after </sheetData>)

Note:
this approach is not suitable for password protected workbooks, as they are encrypted zip files

@Apollofernie 

Hello, locked out of an Excel sheet? Been there, done that. It's like forgetting your keys but for data, right? Okay, let's try to get you back in the game without the password:

  1. Backup First: Before you try anything, make a copy of that spreadsheet. Better safe than sorry!

  2. Try Easy Fixes: If it's just the cells that are locked, and not the whole file, you can try to select all cells (Ctrl+A) and right-click to see if you can unprotect them directly.

  3. Google Fu: Time to put your search skills to good use. Look for "Excel password recovery tools". There are a bunch of free ones out there that can crack simple protection.

  4. VBA Magic: If you're feeling a bit techy, you can create a new macro in Excel and use some VBA code that's floating around the internet to unlock sheets. Not rocket science, but it does the trick.

  5. Zip It: Change the file extension to .zip, unzip it, and look for the workbook protection settings in the XML files. This one's a bit more advanced, so it might be a last resort.

  6. Ask Around: Maybe someone in your office has been through this Excel rodeo before? They might have some tips or even the password if you're lucky.

Remember, only unlock sheets you've got the okay to access. Hope one of these tricks does the job for you! Good luck! 

@bill-xu I renamed the .xlsx to .zip and managed to find the sheet1.xml from /xl/worksheets/. And only one .xml under this folder.

 

However, when I opened sheet1.xml file,  there is no tag named sheetProtection. Only a sheetView tag shows up.

 

P.S. This xlsx file was created by Excel 2019.

@Apollofernie 

this becomes a little complicated...

there may be a tag has two attributes "algorithmName" and "hashValue", the tag's name could be "sheetProtection" or something else depending on the protection mode (maybe?).

you can backup your file first and make a try.

please note, excel workbook contains blob (binary data) in zip file which is not able to be repacked.

so don't decompress its contents into flat files, just use zip tools to edit the file.

winzip, 7zip, etc. can help you replace file inside a zip file without repacking it. (mean just dragging a file from outside into the zip file)

 

here is a link about ms open spec for office documents in which sheetProtection is explained.

[MS-OI29500]: Part 1 Section 18.3.1.85, sheetProtection (Sheet Protection Options) | Microsoft Learn

 

Just for one update. Renaming the file to zip does not work for Excel 2016 and later. A lot of folks recommend this trick but never tried out, lol.

@Jorge_Peony 

This trick is only for removing protection configured from Review menu.

If somebody wants to remove encryption which is configured from File menu, he/she should fail. The encryption is implemented using sha256 (or sha1? ), which currently is not breakable.

So, maybe those failed guys are the latter.

======================

Following are a step-by-step guide of how to do this trick. Not difficult, but BACKUP YOUR WORKBOOK FIRST BEFORE TRYING.

Step 0: have a write protected workbook

step_0.jpg

Step 1: open workbook in 7zip, find and edit "xl/worksheets/sheet1.xml"

step_1.jpg

Step 2: find and delete tag "<sheetProtection .... />", save and close

step_2.jpg

Step 3: click ok when 7zip asks if update, close 7zip

step_3.jpg

Step 4: open workbook in excel, the sheet protection is gone

step_4.jpg

Workbook protection configured from Review menu can be removed as the same as above, the only difference is that the file to be edited is "xl/workbook.xml" rather than the sheet1.xml