How to Unprotect Excel Sheet If Forgot Password (4 Effective Methods)

Get FREE Advanced Excel Exercises with Solutions!

This tutorial illustrates how to unprotect an Excel sheet if we forgot the password. To keep our worksheet or workbook confidential, we set a password. The password protection prevents the other users from making changes to our worksheet. But, there is a possibility that we may forget the password after setting it. However, if we forget the password, we will not be able to read or edit the exit file. To unprotect an Excel sheet without a password follow this article.


How to Unprotect Excel Sheet If Forgot Password: 4 Effective Methods

Throughout this article, we will show 4 effective methods to unprotect an Excel sheet if we forgot the password. To illustrate the methods we will use the following dataset that contains several different food types and their average price. Now notice the ribbon under the Home tab. We can see that many commands under the Home tab are not available as the worksheet is password-protected.

4 Effective Methods to Unprotect Excel Sheet If Forgot Password

More specifically, if we try to make any changes to the worksheet, a message box like the following image will appear. It gives us the warning that the worksheet is protected.


1. Unprotect Excel Sheet with VBA If Forgot Password

First and foremost, we will use a VBA code to unprotect an Excel sheet if we forget the password. We can use the code of this method directly in Microsoft Excel 2010 or earlier versions. But, if we are using the later versions of Microsoft Excel 2010, we have to convert the file in Excel 97-2003 workbook (*.xls) format first. Then we will apply the VBA code in the new format. Let’s see the steps to perform this method.

STEPS:

  • To begin with, go to the Developer tab. Select the option Visual Basic.

Unprotect Excel Sheet with VBA If Password Is Forgotten

  • The above command will open the Visual Basic window.
  • In addition, go to the Insert tab.
  • Furthermore, right-click on the sheet name. Select Insert > Module.

Unprotect Excel Sheet with VBA If Password Is Forgotten

  • Afterward, a blank VBA code window will appear.
  • Next, type 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
  • Now, click on the Run button or press the F5 key to run the code.

Unprotect Excel Sheet with VBA If Password Is Forgotten

  • As a result, 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. Just press OK.

  • Finally, we got our worksheet unprotected. Now, like in the following image, we will be able to edit the value.

Note: If a workbook contains several protected sheets, run the VBA code for each sheet separately.


2. Use Zip Option to Unprotect Excel Sheet without Password

Changing the file extension is another approach to unprotecting an Excel sheet without a password. We will change the extension of the file from .xlsx to .zip. This strategy is pretty challenging. Just follow the below steps to perform this method.

STEPS:

  • Firstly, go to Control Panel > Appearance and Personalization > File Explorer Options.

Use Zip Option to Unprotect Excel Sheet without Password

  • The above commands open a dialogue box named ‘File Explorer Options’.
  • Secondly, in the dialogue box go to the View Check the option ‘Hide extensions for known file types’, and click on Apply.

Use Zip Option to Unprotect Excel Sheet without Password

  • Thirdly, change the extension of the .xlsx file in the .zip file using the rename option.

Use Zip Option to Unprotect Excel Sheet without Password

  • A warning message will appear. Select Yes to go ahead.

Use Zip Option to Unprotect Excel Sheet without Password

  • Now we can see the file is zipped.

Use Zip Option to Unprotect Excel Sheet without Password

  • Next, right-click on the .zip file and select Extract All.

Use Zip Option to Unprotect Excel Sheet without Password

  • Then open the folder named xl.

Use Zip Option to Unprotect Excel Sheet without Password

  • Afterward, open the folder named worksheets.

Use Zip Option to Unprotect Excel Sheet without Password

  • Furthermore, select and right-click on sheet1.xml. Open that file with Notepad.

  • In addition, press Ctrl + F to open the Find Type the text protection in the Find what text field and click on Find Next.

  • The above command will highlight the term protection.
  • The most vital part is to delete the entire line including the term protection inside the <> symbol. Here’s what the line is:
<sheetProtection algorithmName=”SHA-512″ hashValue=”Jte4g3uzxM3+5TRAVyCimSDBkUYC7SR/QlnbWnQ3zW1ya4D6Sv15Cgq8okeDe55neOZK5etHia7fYJZA5g4cyA==” saltValue=”VdRksUGfqQ06FZTTQ+0JBQ==” spinCount=”100000″ sheet=”1″ objects=”1″ scenarios=”1″/>

  • Moreover, zip the files again.
  • After that, change the extension from .zip to .xlsx.

  • A warning message will appear. Select Yes to proceed further.

  • Lastly, open the .xlsx We can edit the new file like the following image.


3. Unprotect Excel Sheet Using Google Sheet If Anyone Forgot Password

In the third method, we will use Google Sheets to unprotect an Excel sheet if we forgot the password. This method is easy and does not contain any complex steps. Just follow the below steps to perform this method.

STEPS:

  • First, open a blank spreadsheet in Google Sheets.
  • Next, go to the File tab and select the option Import.

Unprotect Excel Sheet Using Google Sheet If Anyone Forgot Password

  • Then, go to the Upload option and drag the protected Excel workbook into the box.

Unprotect Excel Sheet Using Google Sheet If Anyone Forgot Password

  • A new dialogue box appears. Click on the option Import tab.

Unprotect Excel Sheet Using Google Sheet If Anyone Forgot Password

  • As a result, we can see the data of the protected Excel sheet in Google Sheets. Also, we can make changes in the data of Google Sheets.

Unprotect Excel Sheet Using Google Sheet If Anyone Forgot Password

  • After that, go to the File Download the file in Microsoft Excel (.xlsx) format.

  • In the end, the Excel file gets unprotected. We can edit the file now like the following image.


4. Copy Contents of Protected Sheet to Another When Password Is Forgotten

Another method to unprotect an Excel sheet when the password is forgotten is to copy the content of the sheet. We won’t be able to crack the password here. However, you can copy and paste the contents of an Excel sheet onto a new sheet. We’ll utilize the same dataset as before. Let’s look at the steps below to learn more.

STEPS:

  • In the first place, open the password-protected sheet.
  • Next, press Shift + Ctrl + End or click on the triangle icon at the bottom left corner to select all used cells.
  • Then, press Ctrl + C to copy the cells.

Copy Contents of Protected Sheet to Another When Password Is Forgotten

  • Furthermore, open a new Excel sheet and select cell A1.

  • After that, press Ctrl + V.
  • Lastly, we can see the following file is unprotected.

Copy Contents of Protected Sheet to Another When Password Is Forgotten

Note: You can use this method if the protected sheet allows you to select locked and unlocked cells.


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

In conclusion, this tutorial demonstrates how to unprotect an Excel sheet if you forgot the password. Download the practice worksheet contained in this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to respond to your message as soon as possible. Keep an eye out for more inventive Microsoft Excel solutions in the future.


Related Articles


<< Go Back to  Unprotect excel sheet | How to Unprotect in Excel | 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.
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

8 Comments
  1. 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.

    • Reply Avatar photo
      Rubayed Razib Suprov Aug 14, 2022 at 1:38 PM

      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.

  2. Who did this? 1 suggestion and 1st try it works properly, amazing. thank you, who made this.

  3. The google sheet method probably is the easiest method, works like a charm for me ^_^. Thank you

  4. How long does the first method generally take? It seems like it’s been running forever and still not finished.

    • Reply Avatar photo
      Saquib Ahmad Shuvo Jun 15, 2023 at 12:07 PM

      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.

  5. Dude! The zip change method worked fantastically, thank you very much!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo