Can't insert worksheet in Microsoft Excel for Mac 2016

When I tried to insert another worksheet in an Excel workbook today, I found that the option to insert a new blank worksheet was grayed out when I clicked on Insert and selected Sheet.



Aiseesoft DVD Creator for Mac

Excel 2016 - insert sheet grayed out

When I tried another means of adding a new sheet by clicking on the plus sign at the right side of the list of sheets in the worksheet to add the sheet, I saw an alert message stating "Workbook is protected and cannot be changed."

Excel 2016 - workbook protected

The file was an Excel Macro-Enabled Workbook (.xlsm) file. I encountered a similar problem in the past with Excel for Mac 2011 with a .xlsx file - see Unprotecting an Excel workbook when the password is not known - so I employed a similar procedure, since .xlsm files are also Office Open XML (OpenXML), zipped, XML-based files. I changed the file extension from .xlsm to .zip then double-clicked on the file in the Finder to extract the contents of the zip file into a subdirectory within the one where the .zip file was located. I then edited the workbook.xml file in the xl subdirectory where the extracted files were stored with the vi text editor. You can also use the TextEdit application that comes with the OS X operating system to edit the file. I searched for workbookPassword and saw the following:

</mc:Choice></mc:AlternateContent><workbookProtection workbookPassword="F91C" lockStructure="1" />

I deleted the F91C, so that I had workbookPassword="", and saved the file. I then recompressed the contents of the directory into which all the files in the .zip file were extracted using the Zipdir Python script, since using the Finder or the command-line interface (CLI) zip utility that comes with OS X will not produce a zip file that Excel will be able to open if the .zip file is renamed to .xlsm.

$ python ~/Documents/bin/zipdir.py MSGRS_2018 MSGRS_2018
$

After I renamed the .zip file to be an .xlsm file, I still saw the option to insert a new sheet greyed out when I chose Insert and Sheet and I still received the same "Workbook is protected and cannot be changed" message when I clicked on the plus sign at the bottom, right side of the Excel window to add another sheet, but I was then able to click on Review, which appears in the menu bar row where Home, Insert, Page Layout, etc. appear, and then click on the Protect Workbook icon to remove the workbook protection.

Excel 2016 protect workbook

When I opened the Protect Workbook icon had a dark gray background around it whereas Protect Sheet did not. When I clicked on the Protect Workbook icon, the dark gray background disappeared and, instead, the background then looked like that for the Protect Sheet icon.

New Tricks: Easy Excel
New Tricks: Easy Excel
1x1px

Excel 2016 unprotected workbook icon

I was then able to add a new sheet to the workbook and use the new spreadsheet.

Related articles:

  1. Unprotecting an Excel workbook when the password is not known
  2. Python script to create a zip file containing all files in a directory