I never intentionally protected the workbook, but you can follow the procedure below for Microsoft Excel for Mac 2011 to unprotect a workbook.
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.
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.