Unprotecting an Excel workbook when the password is not known

When I attempted to add a new worksheet to an Excel .xlsx workbook I was editing in Microsoft Excel for Mac 2011, I saw the message "Workbook is protected and cannot be changed."

Excel workbook protected

You can protect a workbook in Excel for Mac 2001 by clicking on the Review tab and then clicking on the Workbook icon, which is a spreadsheet with a padlock over it, which will prompt you for an optional password. The default protection option is to "Protect sturcture", which means that sheets cannot be moved, deleted, hidden, unhidden, or renamed. New sheets cannot be inserted.

Excel password protect workbook

I never intentionally protected the workbook, but you can follow the procedure below for Microsoft Excel for Mac 2011 to unprotect a workbook.

  1. Click on the Review tab at the top of the document.

    Excel for Mac 2011 Review tab

  2. Then click on the Workbook icon on the tool bar, which is a padlock over top of a spreadsheet. If you are prompted for a password, provide the password.

But what if you don't know the password? If you have a .xlsx file, then the file is stored in an Office Open XML (OpenXML) zipped, XML-based file format developed by Microsoft for spreadsheets, charts, presentations and word processing documents. You can change the file extension to .zip by renaming the file in the OS X Finder application. When you attempt to change the extension for the file you will be prompted to confirm that you wish to do so; click on the "Use .zip" button.

Rename .xlsx to .zip

You can then double-click on the file to extract the files contained within the zip file. A new directory will be created with the name of the file and within that directory will be subdirectories. If you navigate to the xl folder within that directory, you should see a file named workbook.xml . You can right click on that file and choose Open With and then TexEdit.app. Look for the text <workbookProtection workbookPassword="xxxx" lockStructure="1"/> ; xxxx will be an encrypted password. You won't be able to simply use what use see for xxxx as the password, so simply delete whatever text appears between the double quotes after workbookPassword. Then click on File and Save.

You might expect that you could recompress the folder where the files were extracted using the OS X Finder application by clicking on the directory and then clicking on File and Compress in the Finder to produce a new zip file and then rename that newly produced zip file to be a .xlsx file which you could then open with Excel. That won't work, however. If you do that or right click on the directory containing the extracted files and choose Compress dirname where dirname is the relevant directory name, that will produce a new zip file. E.g., if the file you started from was Example.xls and you renamed it to Example.zip and then extracted the contents of the zip file to the directory Example, you would now have a Example.zip file again when you recompressed the directory and its contents. You can right-click on it and rename the extension back to .xlsx, but if you try to open that file with Excel you will see the message "file format is not valid". You will also have the same problem if you use the command line zip program, e.g., if you used zip -r Example.zip Example/*. The zip file produced by those methods on an Apple OS X system does not match what Excel is expecting - see Zipping and unzipping Excel xlsx files for further details. But you can recompress the directory and its contents using the Python script zipdir.

After employing that script to create a new zip file that you then rename to have a .xlsx extension, you can open the workbook in Excel. If you attempt to add a new worksheet, you will still see the "Workbook is protected and cannot be changed" message, but you can click on the Review tab then click on the Workbook icon (the one with the padlock over a spreadsheet) to unprotect the workbook. Then, if you click on the "+" next to an existing sheet to add a new worksheet, a new one will be added.


TechRabbit ad 300x250 newegg.com

Justdeals Daily Electronics Deals1x1 px