How-to Unprotect an Excel Worksheet If You Forgot the Passcode

Unprotect Worksheet Buttons
Unprotect Worksheet Buttons

Several years ago, I created an Excel workbook and I “Protected” a worksheet, but, I forgot the passcode the next day.  Try and try, I couldn’t remember the password.  Therefore, I could never update the worksheet.  It was really upsetting.

Excel Unprotect Worksheet Button
Button Unprotect Worksheet

But never fear, there is a fix if you forget your worksheet protection passcode so that you can unprotect the worksheet or workbook.  In this tutorial, I will show you how to do this.  It isn’t too hard, but it will take a few steps and you may want to download some free software to help make it easier.  Also, there is a sample protected file below so that you can download and practice.

 

The Breakdown

1) Unhide File Extensions





2) Change Excel File Extension from .xlsx to .zip

3) Extract All from Zip File

4) Edit XML

5) Re-Zip File

6) Change Zip File Extension from .zip to .xlsx

 

Step-by-Step

1) Unhide File Extensions

First things first, we need to open up File Explorer so that we can rename your Excel file.  File Explorer is where you see all your files listed on your computer.

If you haven’t already, you will want to unhide file extensions.  To do this, in File Explorer, open the View ribbon and then click on Options or Folder Options.





File Explorer View Folder Options
File Explorer

Then from the Folder Options dialog box, click on the View tab and then find and Uncheck the “Hide extensions for known file types”.

Change Folder Options to Uncheck Hide File Extensions
Folder Options Uncheck Hide File Extensions

 

2) Change Excel File Extension from .xlsx to .zip

Now you may not know this, but Excel files are made up of a whole bunch of files.  They are all zipped up in a compressed zip file.  So if we change the file extension from .xlsx to .zip, we can see all these files that make up the Excel file.  From File Explorer, you should now see the extensions on the file name and you will want to rename the file from .xlsx to .zip.  You will see a warning pop-up asking if you really want to do this, but click on yes to make the change take effect.





If you found the website and tutorials helpful, please consider donating to keep the lights on.

Donate with PayPal here:





Change the file Extension from .xlsx to .zip
Change Excel Extension from .xlsx to .zip

Your new file will now look like this in the File Explorer window with a different icon.

Zip Extension
Final Zip Extension

 

3) Extract All from Zip File

In order to edit the files, you will need to Extract the .zip file into all of its base files.

Extract Zip File
Extract Zip File

If you open the uncompressed zip file, your files will now look like this:

Extracted Excel File
Zip Extracted Files

 





4) Edit XML

In order to do this step, I recommend that you download an XML editor.  XML is basically text, but it can be easier to use the editor to quickly modify the file.  I recommend XML Toolbar as you see in the video below.  Some people also use Notepad++ as well.

Once you have an XML editor open, you will need to navigate to the sheet with the protection and open that file.  The worksheet files are located in the “xl” folder.

Path of XML Worksheets
Find XML Worksheet Location

Now you will need to find the Sheet Protection under Worksheets.  Select it and press the delete key.  If you do this in another application, you will need to make sure you select the entire section.

Delete Sheet Protection with XML Toolbar
XML Toolbar Delete Sheet Protection

Your new XML file will now look like this and you can exit and save the file.

XML Final File
Final XML File

 

5) Re-Zip File

We will now need to re-zip the files into a new compressed .zip file.  To do this, you will need to go to the root of the uncompressed files and highlight all the files.  Then right click and go to the Share ribbon and click on ZIP button or right click on the selected files and click on Send To>Compressed (zip) Folder

Create Compressed Zip File
Create a Zip File

Give the new zip file a good name and your Compressed ZIP file will now look like this:

Final Zip File
Final Compressed Zip File

 





6) Change Zip File Extension from .zip to .xlsx

The final step is to reverse step 2 and change the new ZIP file extension back to an Excel extension of .xlsx

From File Explorer rename the file from .xlsx to .zip.  You will see a warning pop-up asking if you really want to do this, but click on yes to make the change take effect.

Change from zip Extension to xlsx
ChangezipExtensiontoXlsx

Your file will now show an extension of .xlsx and when you open it, the hidden worksheet will no longer be protected.  Try it out with the sample file below.

Excel File Extension

 

Video Tutorial

Sample protected file

Unhide the Sheet “Pics” and remove the protection using the above tutorial:





Break-Protection-Sample.xlsx File

 

If you found this helpful, please let me know if this helped you in the comments below.  Thank you.

Steve=True





9 COMMENTS

  1. Hi. That ‘sheetProtection’ folder is not there in my XML file for the password protected worksheet. What am I missing here? Thanks

    • Hi there. This doesn’t work on Excel 2003. Also, check all the sheets. Finally, it may be at the workbook level.

  2. I think below code would be helpful,

    Sub PasswordBreaker()
    ‘Breaks worksheet password protection.
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    On Error Resume Next
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
    ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
    Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
    Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
    If ActiveSheet.ProtectContents = False Then
    MsgBox “One usable password is ” & Chr(i) & Chr(j) & _
    Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
    Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
    Exit Sub
    End If
    Next: Next: Next: Next: Next: Next
    Next: Next: Next: Next: Next: Next
    End Sub

    May take some time to generate a usable pswd.




  3. If you found the website and tutorials helpful, please consider donating to keep the lights on.

    Donate with PayPal here:




  4. I lost password for excel document, so I followed the above steps to unlock the document and I converted my xlsx to zip, however, I couldn’t find the XL folder in my zip folder.

    • Hi Anil, this is to unprotect a worksheet. If you mean that you password protected the entire document (sometimes called Encrypt with Passcode under protect workbook), then this process will not work. Only thing i have seen is to buy a hacking program to decrypt the file and break the passcode.

  5. Hi there,
    I did everything like you write, but when I opened xl file thetre are not xml files but only .bid files and I can not find why.

    • Strange. Works for most. Maybe you have an encrypted file vs worksheet protection? ALso check every file in the zip file for the text. Good luck.

  6. Hi! Did not work in 97-2003 worksheet. I followed the steps but when i open the zip file it says the compressed file is invalid. Any additional help please.. thanks.

    • you should save it first as a 2013 or later file and retry. I think that 2003 was a different format before html friendly file.

LEAVE A REPLY

Please enter your comment!
Please enter your name here