We will use the following dataset that contains several different food types and their average price. Many commands under the Home tab are not available as the worksheet is password-protected.
If we try to make any changes to the worksheet, a message box like the following image will appear. It warns that the worksheet is protected.
Method 1 – Unprotect Excel Sheet with VBA for a Forgotten Password
This method works in Microsoft Excel 2010 or earlier versions. When using newer Excel versions, the file needs to be in the Excel 97-2003 workbook (*.xls) format.
STEPS:
- Go to the Developer tab.
- Select the option Visual Basic.
- This will open the Visual Basic window.
- Right-click on the sheet name. Select Insert and choose Module.
- A blank VBA code window will appear.
- Insert the following VBA code in that blank code window:
Sub Unprotect_Sheet_If_Password_Forgotten()
Dim a As Integer, b As Integer, c As Integer
Dim x As Integer, y As Integer, z As Integer
Dim z1 As Integer, z2 As Integer, z3 As Integer
Dim z4 As Integer, z5 As Integer, z6 As Integer
On Error Resume Next
For a = 65 To 66: For b = 65 To 66: For c = 65 To 66
For x = 65 To 66: For y = 65 To 66: For z1 = 65 To 66
For z2 = 65 To 66: For z3 = 65 To 66: For z4 = 65 To 66
For z5 = 65 To 66: For z6 = 65 To 66: For z = 32 To 126
ActiveSheet.Unprotect Chr(a) & Chr(b) & Chr(c) & _
Chr(x) & Chr(y) & Chr(z1) & Chr(z2) & Chr(z3) & _
Chr(z4) & Chr(z5) & Chr(z6) & Chr(z)
If ActiveSheet.ProtectContents = False Then
MsgBox "The Password is " & Chr(a) & Chr(b) & _
Chr(c) & Chr(x) & Chr(y) & Chr(z1) & Chr(z2) & _
Chr(z3) & Chr(z4) & Chr(z5) & Chr(z6) & Chr(z)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub- Click on the Run button or press the F5 key to run the code.
- A message box like the following image appears. This message box contains a fake password. We do not need to copy or remember the password. Press OK.
- The worksheet is unprotected and we will be able to edit the values.
Note: If a workbook contains several protected sheets, run the VBA code for each sheet separately.
Method 2 – Use the Zip Option to Unprotect an Excel Sheet without Password
Steps:
- Go to the Control Panel, choose Appearance and Personalization, and select File Explorer Options.
- This opens a dialog box named File Explorer Options.
- Go to the View tab.
- Check the option Hide extensions for known file types and click on Apply.
- Change the extension of the .xlsx file in the .zip file using the rename option.
- A warning message will appear. Select Yes to go ahead.
- The file is zipped.
- Right-click on the .zip file and select Extract All.
- Open the folder named xl.
- Open the folder named worksheets.
- Select and right-click on sheet1.xml. Open that file with Notepad.
- Press Ctrl + F to open the Find dialog.
- Type the text protection in the Find what text field and click on Find Next.
- The above command will highlight the term protection.
- Delete the entire line including the term protection inside the <> symbol. Here’s what the line looks in the sample file.
<sheetProtection algorithmName=”SHA-512″ hashValue=”Jte4g3uzxM3+5TRAVyCimSDBkUYC7SR/QlnbWnQ3zW1ya4D6Sv15Cgq8okeDe55neOZK5etHia7fYJZA5g4cyA==” saltValue=”VdRksUGfqQ06FZTTQ+0JBQ==” spinCount=”100000″ sheet=”1″ objects=”1″ scenarios=”1″/>
- zip the files again.
- Change the extension from .zip to .xlsx.
- A warning message will appear. Select Yes to proceed.
- Open the .xlsx file in Excel.
- We can edit the new file like the following image.
Method 3 – Unprotect an Excel Sheet Using Google Sheets If Anyone Forgot the Password
Steps:
- Open a blank spreadsheet in Google Sheets.
- Go to the File tab and select the option Import.
- Go to the Upload option and drag the protected Excel workbook into the box.
- A new dialog box appears. Click on the option Import data.
- We can see the data of the protected Excel sheet in Google Sheets. We can make changes in the data of Google Sheets.
- Go to the File tab.
- Download the file in Microsoft Excel (.xlsx) format.
- The Excel file gets unprotected. We can edit the file now like the following image.
Method 4 – Copy the Contents of Protected Sheet to Another When the Password Is Forgotten
Steps:
- Open the password-protected sheet.
- Press Shift + Ctrl + End or click on the triangle icon at the bottom left corner to select all used cells.
- Press Ctrl + C to copy the cells.
- Open a new Excel sheet and select cell A1.
- Press Ctrl + V.
- We can see the following file is unprotected.
Note: You can use this method if the protected sheet allows you to select locked cells.
Download the Practice Workbook
Related Articles
- How to Unprotect Excel Sheet with Password
- How to Unprotect Excel Sheet Without Password
- How to Unlock Excel Sheet for Editing
- How to Unlock Grayed out Menus in Excel
<< Go Back to Unprotect excel sheet | How to Unprotect in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Neither method 1 or 2 work for me.
For method 1, the macro runs for 10 mins and then displays the message ‘Not responding’.
For method 2 I get the message:
“We found a problem with some content in ‘Unprotect-Excel-Sheet.xlsm’. Do you want us to try and recover as much as we can? If you trust the source of this workbook. click Yes”
After clicking Yes:
“This workbook cannot be opened or repaired by Microsoft Excel because it is corrupt.”
Only method 1 and 2 are feasible as the protected sheet doesn’t allow you to select locked and unlocked cells.
Any help would be appreciated.
Thanks for your question James, I am not quite sure in which step you get the first error. You can better opt for an alternative approach.
1. The procedure above should be followed until the first extension change. After the extension is changed to .zip, use the 7-zip application to open the archive, not to extract the zip.
2. Then inside the archive, go to xl>worksheet. and double click on the sheet1.xml to edit the text as mentioned in the post.
3. you can use a regular notepad application for opening the XML file. Or you can also opt for an XML notepad(https://microsoft.github.io/XmlNotepad/##_top) instead of a regular notepad. using the XML notepad, you can delete the protection part directly as a folder, as shown in the image.
4. After deleting the protection part, save the XML file.
5. And then change the extension part back to xls.
6. You will see that the Excel file is now unprotected.
Last but not the least, try to use Microsoft Office 365 instead of regular version of Excel.That way you will always be ensured with the latest updates.
Who did this? 1 suggestion and 1st try it works properly, amazing. thank you, who made this.
The google sheet method probably is the easiest method, works like a charm for me ^_^. Thank you
How long does the first method generally take? It seems like it’s been running forever and still not finished.
Dear CHRIS B.
Greetings. Thank you for your inquiry. I am not quite sure in which step you get the first error. You might be experiencing that issue as a result of improper code application.
Dude! The zip change method worked fantastically, thank you very much!
Dear Dave,
You are most welcome.
Regards
ExcelDemy