What if you want to edit a protected sheet of your Microsoft Excel workbook but have forgotten your password? The zip file method unlocks your protected Excel sheet, no password is needed. Follow these steps.

1. Change the Extension Type to a Zip File

In File Explorer, edit the file name. Delete the extension “xlsx” and change it to “zip” to make your workbook a zip file. If you cannot see the extension type in the file name, go to the “View” tab and check the box by “File name extensions.” Then, confirm the name change.

2. Open the Zip File

For this step, you need a zip program — a third-party app that allows you to view zip file contents.

Once you’ve installed a zip program, open the zip file you just created. In the dialog box, open the “xl” folder. Open the “worksheets” folder to see the XML file of each spreadsheet in your Excel file.

3. Open the Protected Sheet in Notepad

Confirm which XML file is the protected sheet before proceeding. Double-click to open the sheet, then use the find shortcut (Ctrl + F) to search for “protect.” The protected sheet will have a line of code starting with “sheetProtection,” which the unprotected sheets will not have.

Close out of the window to return to the zip program. Drag and drop the XML-protected sheet onto your desktop. Right-click on the file, hover over “Open with,” and choose “Notepad.”

4. Remove the Protection HTML Code

In Notepad, use the find shortcut to search for “protect.” Locate the code starting with “<sheetProtection.” Begin highlighting at the opening tag (<), then highlight until you see the closing tag (/>).

After making sure only this code is highlighted, press “backspace” on your keyboard to delete it. Close the window and save the Notepad file when prompted.

5. Add the Edited Sheet to the Zip File

In the zip program, select the XML-protected sheet. Use the “Delete” key on your keyboard to delete this sheet.

See also  Common PCI Violations

Add the XML sheet you just edited to the zip file by dragging and dropping. In the dialog box, click “OK” to confirm the addition. Close the zip program to return to File Explorer.

6. Change the Extension Type to an Excel File

Click on the name of the zip file and change “zip” to “xlsx” to convert it back to an Excel file. Confirm the name change when prompted.

7. Verify the Sheet is Unlocked

Open the Excel file and navigate to the sheet that was protected. You should now be able to edit it.

Learn More About Our Managed IT Services

Contact Agio to learn more about our managed IT services for the financial industry.