You have an Excel workbook where a specific worksheet is protected, and you cannot remember the password. This prevents you from editing cells, formatting, or inserting rows on that sheet. Worksheet protection is a security feature that locks the sheet’s structure and content. This article explains the methods you can use to regain editing access when the password is lost.
Key Takeaways: Recovering a Lost Worksheet Password
- Save As .xlsx or .xlsm file: This method removes weak worksheet protection by saving a new copy of the file, allowing you to edit the sheet.
- Copy contents to a new workbook: You can manually copy all data and objects from the protected sheet into a new, unprotected worksheet.
- Use a VBA macro script: A specific macro can programmatically unlock a worksheet that uses standard Excel protection.
Understanding Worksheet Protection in Excel
Excel offers two primary levels of protection: workbook protection and worksheet protection. This article focuses on worksheet protection, which restricts users from modifying the contents, format, or structure of a specific sheet. When you protect a sheet, you can choose which actions to allow, like selecting locked cells or sorting data.
It is critical to know that standard worksheet protection in modern Excel files (.xlsx, .xlsm) is not a strong encryption. Its main purpose is to prevent accidental changes. The password used for this protection is stored within the file in a way that can be bypassed without brute-forcing the original password. This is why recovery is possible. However, this only applies to protection applied via the Review > Protect Sheet command.
What This Method Cannot Do
These recovery techniques will not work on passwords for workbook-level protection, which encrypts the file and requires a password to open it. They also cannot break strong encryption used in older .xls file formats. The methods described here are for unlocking a sheet so you can edit it, not for discovering the original password characters.
Steps to Remove Worksheet Protection
Follow one of these methods to unlock your protected Excel sheet when you do not have the password.
Method 1: Save the File in a Modern Format
This is the simplest approach for files already in .xlsx or .xlsm format. It works by creating a new file where the worksheet protection is not enforced.
- Open the protected workbook
Launch Excel and open the file containing the password-protected worksheet. - Go to File > Save As
Click the File tab on the ribbon, then select Save As from the menu. - Choose the same file format
In the Save As dialog, ensure the “Save as type” is set to Excel Workbook (*.xlsx) or Excel Macro-Enabled Workbook (*.xlsm), matching the original. - Save with a new name
Type a new filename, such as “Report_Unprotected.xlsx”, and click Save. Close the new file and reopen it. The sheet protection should now be removed, allowing full editing.
Method 2: Copy All Content to a New Sheet
If the first method does not work, you can manually transfer everything to a new workbook.
- Select all cells on the protected sheet
Click the triangle at the intersection of the row and column headers to select the entire worksheet. - Copy the selected data
Press Ctrl+C to copy all cells, including formulas, values, and basic formatting. - Create a new workbook
Open a new, blank Excel workbook by pressing Ctrl+N. - Paste into the new sheet
Click cell A1 in the new workbook and press Ctrl+V to paste. You may need to adjust column widths afterward. - Recreate other elements
Manually copy charts, shapes, or data validation rules that may not have pasted correctly. Save the new workbook.
Method 3: Use a VBA Macro to Unprotect
You can use a Visual Basic for Applications macro to programmatically unprotect the sheet. This requires enabling the Developer tab.
- Open the Visual Basic Editor
In the workbook with the protected sheet, press Alt+F11. This opens the VBA editor window. - Insert a new module
From the menu, click Insert > Module. A blank code window will appear on the right. - Enter the macro code
Copy and paste the following code into the module window:Sub UnprotectSheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect
Next ws
End Sub - Run the macro
Close the VBA editor. Press Alt+F8 to open the Macro dialog. Select “UnprotectSheet” and click Run. All worksheets in the workbook will be unprotected. - Save the workbook
After running the macro, save the file. The protection is now removed.
Common Mistakes and Limitations
These methods have specific boundaries. Understanding them will save you time and effort.
“The workbook is protected and cannot be changed”
If you see this message, it means workbook structure protection is enabled, not just sheet protection. You cannot add, delete, or rename sheets. The VBA macro method or the Save As method will not remove this. You need the specific workbook protection password, which is different and more secure.
Macro does not run or has no effect
Ensure macros are enabled. When you open the file, a security warning may appear below the ribbon. Click “Enable Content.” Also, verify the file is saved as a macro-enabled workbook (.xlsm) before adding the VBA code. If the macro runs but the sheet remains locked, the protection might be from a third-party add-in, which requires a different approach.
File is in old .xls format
The Save As trick often fails for older .xls files because they use a different protection algorithm. For these files, first try saving them as a new .xlsx file, then apply Method 1. If that does not work, the copy-paste method is your most reliable option.
Worksheet Protection vs. Workbook Protection
| Item | Worksheet Protection | Workbook Protection |
|---|---|---|
| Purpose | Prevent edits to a specific sheet’s cells and format | Prevent changes to workbook structure like adding sheets |
| Password Strength | Weak, easily bypassed | Stronger, often requires original password |
| Recovery Method | Save As, VBA macro, copy content | Password recovery tools or remembering the password |
| Affected Actions | Editing locked cells, inserting rows, sorting | Inserting, deleting, hiding, or renaming worksheets |
| File Format Support | All .xlsx, .xlsm, .xls files | All .xlsx, .xlsm, .xls files |
You can now edit a worksheet you previously locked. Use the Save As method for a quick fix on modern files. For future sheets, consider using a password manager to store protection passwords. An advanced tip is to protect the sheet without a password, which still prevents most accidental edits but allows any user to unprotect it via Review > Unprotect Sheet with a single click.