Excel 2016 - Workbook Protected

I wanted to add another sheet to an Excel workbook, but when I clicked on the "+" to add the additional worksheet, I saw the message "Workbook is protected and cannot be changed."

Excel Shortcuts and Productivity Hacks 2.0
Excel Shortcuts and Productivity Hacks 2.0
1x1px

Excel 2016 - workbook protected

I didn't know what the password might be because I had never set a password for the workbook, which I had created in a prior version of Excel; I'm using Microsoft Office 2016 on a MacBook Pro running OS X El Capitan (10.11.6) at the moment.

The document was an .xlsx file, which is 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 and unzip it the way you would any other zip file. When you attempt to change the extension for the file, though the Finder, you will be prompted to confirm that you wish to do so; click on the "Use .zip" button. Or you can rename the file through a command line interface, e.g. a Terminal window.

$ cp ../MSGRS.xlsx MSGRS.zip
$ unzip MSGRS.zip
Archive:  MSGRS.zip
  inflating: [Content_Types].xml     
  inflating: _rels/.rels             
  inflating: xl/_rels/workbook.xml.rels  
  inflating: xl/workbook.xml         
  inflating: xl/worksheets/sheet4.xml  
  inflating: xl/worksheets/sheet1.xml  
  inflating: xl/worksheets/sheet3.xml  
  inflating: xl/worksheets/sheet2.xml  
  inflating: xl/worksheets/_rels/sheet1.xml.rels  
  inflating: xl/worksheets/_rels/sheet2.xml.rels  
  inflating: xl/worksheets/_rels/sheet3.xml.rels  
  inflating: xl/worksheets/_rels/sheet4.xml.rels  
  inflating: xl/styles.xml           
  inflating: xl/sharedStrings.xml    
  inflating: xl/worksheets/sheet12.xml  
  inflating: xl/worksheets/sheet8.xml  
  inflating: xl/worksheets/sheet7.xml  
  inflating: xl/worksheets/sheet6.xml  
  inflating: xl/worksheets/sheet5.xml  
  inflating: xl/worksheets/sheet9.xml  
  inflating: xl/worksheets/sheet10.xml  
  inflating: xl/worksheets/sheet11.xml  
  inflating: xl/theme/theme1.xml     
  inflating: docProps/custom.xml     
  inflating: docProps/core.xml       
  inflating: docProps/app.xml        
  inflating: xl/calcChain.xml        
$ rm MSGRS.zip
$

To unprotect the file edit the xl/workbook.xml file. Within it you should see the following 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.

After you've deleted the password, you need to create a new zip file from the files you extracted from the original one. But you can't use the Finder or the zip utility available from the command line. 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. Make sure you aren't including the original zip file in the new zip file, just the directories and files you extracted from the original zip file. Rename the new .zip file to the original file name extension, .xlsx.

$ python zipdir.py MSGRS_New ~/Documents/Work/MSGRS/temporary
$ mv MSGRS_New.zip MSGRS_New.xlsx
$

If you try to add a new sheet, you will again be warned that the workbook is protected and can not be changed. To unprotect the workbook, click on Tools, select Protection, then Unprotect.

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



Excel: Learn basic and advanced formulas quick and easy
Excel: Learn basic and advanced
formulas quick and easy
1x1px



Excel - beginner to HIRED!
Excel - beginner to HIRED!
0x0

Excel 2016 unprotect workbook

You should then be able to add a new sheet to the spreadsheet.

References:

  1. Unprotecting an Excel workbook when the password is not known
    Date: November 15, 2016 MoonPoint Support
  2. Python script to create a zip file containing all files in a directory
    Date: November 13, 2016
    MoonPoint Support