Forum Discussion
How to unprotect Excel sheet if forgot the password
RuthDelbert For Excel 2007 and later versions, files are saved in an XML-based format. This means you can unzip the file and manually edit the XML files to remove the protection. This method requires some technical knowledge and careful handling of XML files.
How to unprotect Excel sheet without password by editing the internal xml file:
- Change the file extension of the Excel workbook from .xlsx to .zip.
- Extract the contents of the ZIP file to a folder.
- Navigate to the folder and open the xl subfolder.
- Locate the worksheets subfolder and find the XML file corresponding to the protected sheet (e.g., sheet1.xml).
- Open the XML file in a text editor (e.g., Notepad++).
- Search for the tag <sheetProtection> and delete it along with its attributes.
- Save the XML file and close the text editor.
- Compress the folder back into a ZIP file.
- Change the file extension from .zip back to .xlsx.
- Open the modified Excel file, and the sheet should be unprotected.
Thank you for the steps. This had worked for me before but if renaming the file from .XLSX to .ZIP gives you an error (unsupported format), rename it to .ARJ, and the same can be done using the BBEdit app. Simply open the ARJ file with BBEdit and follow the rest of the steps.
Thank you EnricoPenco! I had posted a similar solution where I mentioned to change the file extension to ARJ. But after reading your post, the same can be done with the standard XLSX extension. So, let me reiterate myself:
THIS IS EXCEL FOR MAC OS USERS.
- Download a FREE copy of BBEdit
- Make a copy of your password protected file: Select it, press CMD-C then CMD-V.
- Right-Click on the new file and select "Open With" then "Other..."
- On the pop-up window, next to "Enable:", change "Recommended" to "All Applications".
- Make sure NOT TO ENABLE "Always Open With".
- Select "BBEdit.app" your list of Applications.
- BBEdit will show a series of locations on the left pane. Expand "xl", and within it expand "worksheets".
- Select the "sheet" with the password.
- You can use the function to Find (CMD-F) to easily locate "<sheetProtection"
- Delete this tag from "<sheetProtection" to its tag closure "/>"
- Save the file and open it to make sure password is gone.
- very nice and simple execution, no external software nothing pure action! thanks buddy!
not working, it produce an error insteadToddSumrall
If Unsupported Format error see my solution above.
Hi I also had this issue with an error - I found the best way was to follow the Zip file process but instead of deleting the sheet protection code was to replace it with a code of a spreadsheet you know the password - or just generate a dummy spreadsheet with a password protection password and use that code
This might solve a huge problem for me so thank you.
How do I know how much of the .xml file is <sheetProtection> and all its arguments?
I've tried deleting different lengths of code but it results in a corrupt file.
Thanks
The <sheetProtection> argument end on a <something...something.../> Delete up to the "/>".
thank you, these steps are very helpful! I’m encountering a different problem trying with a password protected workbook. Is this because of excel version? When opening the renamed .xlsx as a .zip file, the folders in zip do not have “xl”. The ones there are only
[6]DataSpaces
And then two txt files
EncrytedPackage
EncrytionInfo
the subfolders from [6]DataSpaces have
DataSpaceInfo
TransformInfo
No folders or files have “xl” or “sheet protection “.
What else should be tried? Thank you in advance for the advice and help!
ToddSumrall Does the process work only for Windows or also MAC OS?
In my case (Mac OS) I see the following .xml file, so I am not sure which fields to delete.Thanks in advance for your input,
Dimitrios
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<encryption xmlns="http://schemas.microsoft.com/office/2006/encryption" xmlns:p="http://schemas.microsoft.com/office/2006/keyEncryptor/password" xmlns:c="http://schemas.microsoft.com/office/2006/keyEncryptor/certificate"><keyData saltSize="16" blockSize="16" keyBits="256" hashSize="64" cipherAlgorithm="AES" cipherChaining="ChainingModeCBC" hashAlgorithm="SHA512" saltValue="9p/eXy9YwQzD1WNXL7BZSQ=="/><dataIntegrity encryptedHmacKey="wD8+C4tULXQMSBhSBAgM9xnsFrxN++QpTb/lKwmpltsZnPW2vgfD2eY96pLFTP2Qfa4RW1UTqoELrveCAezTGg==" encryptedHmacValue="v6ibUo/F769r9Pn0OPbqctfXvUObE7eXGmWrnYM+RIPQDU1h49OnRp3PFtHljLTFw051V3Ph/+oTnxziBqtCQQ=="/><keyEncryptors><keyEncryptor uri="http://schemas.microsoft.com/office/2006/keyEncryptor/password"><p:encryptedKey spinCount="100000" saltSize="16" blockSize="16" keyBits="256" hashSize="64" cipherAlgorithm="AES" cipherChaining="ChainingModeCBC" hashAlgorithm="SHA512" saltValue="VmtFChBOLtVyZIO0PKpWYQ==" encryptedVerifierHashInput="4iCxfYahy+Rzjlt2SSoRuA==" encryptedVerifierHashValue="R8V4IrM4UfARuaLemcsLpxfIajl3vqtEiD4KHkqzBvgTfJLDv42HN7dB/9Xy1YhfETRFJOeSQCN3QDfoN6wPMg==" encryptedKeyValue="vDeqqIywPyca5i+JX9xvPXnzeP/kjx6fThMZFipNjtY="/></keyEncryptor></keyEncryptors></encryption>It does! I use Excel for Mac. See my BBEdit (app) suggestion above.
ToddSumrall ow much text needs to be deleted? I deleted the <sheetProtection> as well as text within the <__> but it didn't work... I may not be deleting enough text, or maybe too much?
Personally I prefer the mentioned method with editing the text in the XML-file. It has never failed me and the same method works for both protected sheets, protected structure AND protected Word documents.
1: Tag name for a sheet is '<sheetProtection>' in (e.g.) 'Sheet1.xml'...
2: Tag name for protected sheet structure is '<workbookProtection>' in the file 'workbook.xml'...
3: Tag name for a protected Word document is '<w: documentProtection>' in the file 'settings.xml'...
To make the code editing even easier: Open the XML file in both(!) Notepad and a web browser. Then copy the code from the web browser and replace the code in the Notepad window. The tags in the Notepad window will be broken up into entire rows, like in the browser... and selecting/deleting the right text (not more/not less) will get easier. (Word wraps in the XML file when saving it doesn't matter.)- still does not work on me, it says file corrupted
rtempleman same here, when I try to open the file it's corrupt.
ToddSumrall Hi, I am trying to follow your instructions to get rid of the password protection on an Excel worksheet, but when I zip the xml file, there are no subfolders for worksheets - it zips the whole workbook. Then when I try to open the workbook in Notepad, it's all symbols and no code I can edit. Do you know a way I can zip just the protected sheet? Thanks for any guidance!
If you upload file to a google drive and share it I can help you unlock it. But if nobody else replies on the chat I can revisit my instructions. Are you a Mac or Windows user?
im trying to follow the instruction, and also i cannot find xl folder. Only one folder name [6] Data Spaces.... im so confused
I think you're reading wrong the instructions. You ZIP a copy of the original XLSX file. The find the "sheet name" that has the password. Delete the <sheetProtection> section. Save the file and rename back to XLSX. If this works OK, then you can keep this as your main file. Save the original just in case.
- Thank you, it worked well 😊
ToddSumrall At Step 2, it says "Compressed Folder is Empty". Is there anything else I can try.
