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.
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.
Then from the Folder Options dialog box, click on the View tab and then find and Uncheck the “Hide extensions for known file types”.
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.
Your new file will now look like this in the File Explorer window with a different icon.
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.
If you open the uncompressed zip file, your files will now look like this:
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.
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.
Your new XML file will now look like this and you can exit and save the 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
Give the new zip file a good name and your Compressed ZIP file will now look like this:
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.
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.
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
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.
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.
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.
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.
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.