Sometimes you are getting Excel (2007/2010) files with password protection on some sheets or cells, but you would like to change this (probably because there is an failure in a formula of an protected cell or you would like to save the file in an different format like openoffice/libre office). Here is a way on how get rid of the PW protection:
- Rename the file from .xlsx to .zip (ignore the warning on impact of changing file extensions)
- Use 7ZIP to unzip the .zip file, you will get an directory structure with several subfolders.
- Search for the xl/worksheets directory
- Within the xl / worksheets directory you will find an xml file for each sheet of your excel file.
- Open the XML of the protected sheet and search for an XML tag sheetProtection password –> something like this <sheetProtection password=“MyTotallySecretPassword“>
- You can now delete the whole XML key to remove the password or just take the password to keep the protection and just use the password to unprotect the sheet within Excel.
- If you removed the XML tag, save the XML file
- ZIP the whole directory to an ZIP file and rename it back to .xlsx
Attention: this procedure works only with protected workbooks/sheets and not with „encrypted with password“ workbooks