How to Unprotect Excel Sheets and Workbooks

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we need to protect our Excel sheets or workbooks containing important data or calculations for security issues. If you remember the password then it’s quite easy to unprotect them but if you forget then it becomes a headache.

In this Excel tutorial, we’ll show how to unprotect an Excel sheet or workbook with or without a password.

Here’s an overview of unprotecting an Excel sheet.

1-Overview of unprotecting in Excel


How to Unprotect Excel Sheets?

You can unprotect an Excel sheet with or without a password, here’s how:

Case 1: When You Know the Password

To unprotect an Excel sheet with a password follow the steps below:

  1. Right-click on the sheet name and select the “Unprotect Sheet” option from the context menu.2-Using Unprotecting Sheet option
  2. Then type the password and press OK.

3-Inserting password to unprotect sheet

  • Alternatively, you can get the “Unprotect Sheet” option by clicking: Review > Protect > Unprotect Sheet.

4-Using Unprotect Sheet option from Review tab.

  • Or, by clicking: Home > Cells > Format > Unprotect Sheet.

5-Using Unprotect Sheet option from Home tab.

Case 2: When You Don’t Know the Password

You can unprotect an Excel worksheet without knowing the password in the following 2 ways:

i. Converting the XLSX File to a ZIP File

  1. Right-click on the file and select Rename from the context menu.6-Selecting Rename option
  2. Rename the extension from xlsx to zip and press Enter.7-Reanimg extension to zip format
  3. Open the zip file using any data compression tool, we used WinRAR.8-Opening with WinRAR
  4. Double-click on the xl folder to open it.9-Opening the XL folder
  5. Then open the worksheets folder by double-clicking on it.10-Opening the worksheets folderHere you will get the sheet list.
  6. Copy the sheet that is protected by pressing Ctrl + C.11-Selecting protected sheet
  7. Paste it in any outside folder by pressing Ctrl + V and open it with Notepad.12-Opening protected sheet with Notepad
  8. Press Ctrl + F to open the Find dialog box and find the text “Protect”.13-Finding line with the word Protect
  9. Now start to select from that line’s starting tag(<) which contains the text “Protect”.14-Selecting the line from starting
  10. Select till that line’s ending tag (/>).
  11. Press the Backspace key or the Enter key to delete the selection.15-Selecting the line till end tag
  12. Then save the file by clicking: File > Save or pressing Ctrl + S.16-Saving the file in Notepad
  13. Delete the existing protected sheet from the zip Explorer’s “worksheets” folder.17-Deleting existing protected sheet
  14. Drag the edited XML file from the outside folder to the zip Explorer’s “worksheets” folder.18-Dragging the edited sheet to sheets folder
  15. Change the extension from zip to xlsx and press Enter.

19-Changing the extension to xlsx

Now see the sheet is unprotected.

20-Output of unprotected sheet

ii. Using a VBA Code

You can only unprotect an Excel sheet without a password using VBA in Excel 2013 or earlier versions. That’s why, first, save the file in “Excel 97-2003 workbook (*.xls)” format if you are using any version after Excel 2013.

Here are the steps to unprotect an Excel sheet without using a password:

  1. Save the Excel file in “Excel 97-2003 workbook (*.xls)” format if you are using any version after Excel 2013. If not then skip this step.21-Saving workbook in compatibility mode
  2. Open the xls file by double-clicking on it.22-Opening xls file
  3. Press Alt + F11 to open the VBA window.
  4. Click Insert > Module to open a new module.23-Inserting new module
  5. Paste the following code in the module and run it by clicking the Run icon. Or press F5.
Sub Password_Cracker()
Dim x As Integer, y As Integer, z As Integer
Dim a As Integer, b As Integer, c As Integer
Dim x1 As Integer, x2 As Integer, x3 As Integer
Dim x4 As Integer, x5 As Integer, x6 As Integer
On Error Resume Next
For x = 65 To 66: For y = 65 To 66: For z = 65 To 66
For a = 65 To 66: For b = 65 To 66: For x1 = 65 To 66
For x2 = 65 To 66: For x3 = 65 To 66: For x4 = 65 To 66
For x5 = 65 To 66: For x6 = 65 To 66: For c = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(y) & Chr(z) & _
Chr(a) & Chr(b) & Chr(x1) & Chr(x2) & Chr(x3) & _
Chr(x4) & Chr(x5) & Chr(x6) & Chr(c)
If ActiveSheet.ProtectContents = False Then
MsgBox "New Password Is: " & Chr(i) & Chr(y) & _
Chr(z) & Chr(a) & Chr(b) & Chr(x1) & Chr(x2) & _
Chr(x3) & Chr(x4) & Chr(x5) & Chr(x6) & Chr(c)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

24-Inserting VBA code to break password

Now see, the edit mode is enabled means the sheet is unprotected.

25-Output of the unprotected sheet after breaking password


How to Unprotect Excel Workbooks?

Like Excel sheets you can unprotect an Excel workbook with a password or without a password, here’s how:

Case 1: When You Know the Password

To unprotect an Excel workbook with a password, follow the steps below:

  1. Click as follows: Review > Protect > Protect Workbook.26-Unprotecting workbook from Review tab
  2. Type the password and press OK.

27-Inserting password to unprotect workbook

Case 2: When You Don’t Know the Password

Here are the ways to unprotect an Excel workbook without a password:

i. Converting the XLSX File to a ZIP File

  1. Follow the same first 4 steps from section i of the unprotecting Excel sheet without a password.
  2. Then select and copy the workbook.xml file to an outside folder.28-Opening workbook.xml file
  3. Right-click on the file and open it with Notepad.29-Opening xml file in Notepad
  4. Open the Find dialog box by pressing Ctrl + F and search for the word “Protection”.30-Finding line with the word Protection
  5. Start to select from that line’s starting tag(<) which contains the text “Protection”31-Selecting the line from start tag
  6. Select till that line’s ending tag (/>).
  7. Press the Backspace or Enter key to delete the selection.
  8. Save the file by pressing Ctrl + S.32-Selecting the line till end tag
  9. Delete the existing workbook.xml file from the zip Explorer’s “xl” folder.
  10. Drag the edited XML file from the outside folder to the zip Explorer’s “xl” folder.33-Delete the existing workbook.xml file and copying the edited XML file to that folder
  11. Again rename the extension from zip to xlsx and press Enter.

34-Changing the extension to zip xlsx again

Now look, all the sheet option is activated, which means our workbook is now unprotected.

35-Unprotected workbook without password

ii. Using a VBA Code

This VBA will not directly break the password but rather create a duplicate copy of the Excel workbook.

Follow the below steps to unprotect an Excel workbook using VBA:

  1. Press Alt + F11 to open the VBA window.
  2. Paste the following code in a new module and run it by clicking on the Run icon:
Sub Unprotect_WorkBook()
ActiveWorkbook.Sheets.Copy
For Each sh In ActiveWorkbook.Sheets
sh.Visible = True
Next
End Sub

36-Inserting VBA code in a new module to unprotect workbook

Soon after, it will create a new unprotected workbook as in the image below.

37-Duplicate unprotected copy of workbook


Download Practice Workbook


Conclusion

In conclusion, we have covered every possible way to unprotect an Excel sheet or workbook with or without a password. While unprotecting without a password converting to zip file approach is quite safe as the VBA method isn’t compatible in some latest Excel versions.

Thanks for reading the article, we appreciate any kind of feedback or queries in the comment section.


How to Unprotect in Excel: Knowledge Hub


<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo