So, you’ve been sent an Excel Spreadsheet and there are changes that you have to make but discover that the sheet or workbook is protected with a password.
The instructions on this article will explain how to get around that and unlock the worksheets. The instructions are for individual sheets. To apply to an entire workbook (Excel sheet with multiple pages) you will have to repeat these steps for every sheet within the book.
This is not a hack! Be sure that you have permission to edit these files. This removes password protection from individual sheets that the author didn’t want you to alter.
- Additional software required on Windows = none.
- Tech-savvy level = 5/10
Difference Between A Sheet & Workbook?
In this article, I’ll refer to workbooks and worksheets. A workbook is the entire Excel file, a worksheet are the pages (tabs) within the file.
Workbook
Definition: A workbook is an Excel file that contains one or more sheets (or worksheets). It serves as a container for all the data, charts, and other content.
File Extension: Commonly .xlsx or .xls for older versions.
Function: It organizes and stores related data in a single file, allowing for easy management and navigation of multiple sheets.
Sheet (or Worksheet)
Definition: A sheet, or worksheet, is a single page within a workbook where you can enter and manipulate data. Each sheet consists of a grid of cells arranged in rows and columns.
Tabs: Sheets are represented by tabs at the bottom of the workbook window. You can add, delete, rename, and move sheets within a workbook.
Function: Sheets are used to organize data, perform calculations, create charts, and analyze information within the broader context of the workbook.
Step-by-Step Instructions To:
Remove Password Protection From Excel:
Let’s begin…
Backup Your Excel File:
- Before you start, make a copy of your Excel file to ensure you have a backup in case anything goes wrong. Excel sheets can be easily broken. Have a master copy as a backup and the copied version to run the following instructions.
Change File Extension to .zip:
- Locate your Excel file (e.g., example.xlsx).
- Right-click on the file and select “Rename.”
- Change the extension from .xlsx to .zip (e.g., example.zip).
Note: In Windows 11 you may have to click View tab / Show / File Name Extensions
Extract the Zip File:
- Right-click the renamed .zip file and select “Extract All” or use a zip file extraction tool to extract the contents to a folder.
Locate the Relevant XML File:
- Navigate to the extracted folder. Inside, you’ll find several subfolders and files.
- Go to the xl folder, then the worksheets subfolder.
- You’ll see files like sheet1.xml, sheet2.xml, etc., corresponding to the sheets in your workbook.
Edit the XML File in Notepad:
- Choose the sheet that is password-protected (e.g., sheet1.xml).
- Right-click the file and open it with Notepad or any text editor.
- Search for a tag like <sheetProtection … /> within the XML file.
- This tag contains attributes that enforce protection, such as password, sheet, objects, scenarios, etc.
Remove the Sheet Protection Tag:
- Delete the entire <sheetProtection … /> tag.
- Save the changes and close the text editor.
Recompress the Files into a Zip Archive:
- Select all the files and folders that were extracted.
- Right-click and choose “Send to” > “Compressed (zipped) folder.”
- Name the new zip file (e.g., example.zip).
Rename the Zip File to Excel:
- Right-click the new zip file and select “Rename.”
- Change the extension from .zip back to .xlsx (e.g., example.xlsx).
Open the Modified Excel File:
- Double-click the renamed Excel file to open it in Excel.
- The sheet protection should now be removed.
That’s It. However:
This method works for Excel files where sheet protection is applied without a password or with a simple one.
It does not work for files protected with strong encryption.
Always make sure that you’ve got a backup of your original file before making changes.
This approach might not be compliant with the terms of use of some organizations or violate intellectual property policies, so ensure you have the right to modify the file. If this relates to work, make sure that you have permission to make these changes.
Hopefully this has helped you to remove the password protection from your Excel sheet.
End