Sheet Protection
Allows to protect data from unintended users or restrict editing. In some cases user might forget the password for the sheet which he used to protect. In this article we will see steps to recover or remove password for a specific password protected sheet.
Example workbook
Step 1: Rename file
The very first task is to rename file extension with Zip as shown below, to do so, right click on the file for you wish to perform unprotect sheet operation in Windows Explorer and rename to .zip or you can open command prompt and run REN command as mentioned here.
Before
After
Step 2: Open zip to browse XML
Double click or right click and Open zipped file and navigate to xl\worksheets directory as shown below where you will find sheet1.xml file (it can be any other sheet as per your workbook. In my file I have only one sheet hence I can see only sheet1.xml).
Step 3: Copy sheet1.xml file
Now extract sheet1.xml file somewhere else in windows to make changes. Once it is copied open file with any XML editing compatible tools like Note++ or standard notepad and your file will look like as shown below:
Step 4: Remove Protection
Since the file is in XML format and xml builds based on tagging hence, you need to look for “<SheetProtection>” tag. Now remove everything between SheetProtection tag and final tag would look like as below:
Step 5: Save and zip
Once you made changes as suggested in step 4, save the file and copy and paste in the same zip browser from where we copied the file. It will prompt for zip and override action say OK.
Step 6: Rename from zip to xlsx
Once you have overridden file as mentioned in step 5, you can undo zip to xlsx file extension by reversing step 1 and file will be turned as normal excel file.
Step 7: Verify your job
Next you can open the file and try typing anything in the sheet and you will see sheet protection is gone as shown below:
