How to Unprotect an Excel spreadsheet if you have lost your password 29


How to Unprotect an Excel Spreadsheet if you have lost your password.

I recently had the circumstance where a client sent me a copy of a spreadsheet that was password protected, however the client no longer had the password to unlock the content. They were wondering if there was anything we could do…

So how do you unprotect an Excel spreadsheet when you don’t have the password? Unfortunately, there is no quick and simple way for your to view or recover a lost password.

 

Microsoft’s official stand on this matter is:

[URL] – Microsoft

Recover a password to open a workbook or worksheet

Excel lets you password protect your workbooks, and your worksheets. But, it’s easy to forget or misplace your passwords. Unfortunately, if that’s happened to you, we don’t have a way to help you recover a lost password.

Excel doesn’t store passwords where you or Microsoft can look them up. That’s also true for the other Office programs that let you protect files. That’s why it’s always a good idea to store your passwords someplace safe.

Some third-party companies offer programs for unlocking files. For legal reasons, we can’t recommend those programs. You can try them, but at your own risk.

 

So what do you do if you have lost the password  to unprotect an excel workbook or worksheet?

The following method MAY, we re-iterate MAY, work for you, but then again it may not as well.  We have had a good amount of success using this method but it has not worked 100% of the time. Having said that, the method does not harm your spreadsheet nor data, so you have nothing to lose from trying it.

 

How to unprotect a password protected worksheet.

 

So what do you do…

In the case of a password protected worksheet where you are unable to Edit the data you can try the following steps to unprotect your worksheet.

image - unprotect worksheet

 

Step 1 Press ALT +  F11 or click View Code on the Developers Tab
UnprotectWorksheet_02

 

Step 2 Double click on the worksheet that is password protected.
UnprotectWorksheet_03
Step 3 Copy and paste the code below into the (Code) window. Do not change any of the code, just copy and paste it.
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
Step 4 Click on the Run Button or press F5.
UnprotectWorksheet_04

And there you have it.

 

If this method worked and was able to unprotect your worksheet you’ll a small pop-up window saying “One usable password is XXXYYYZZZXXXY”.

 

Continue the Discussion

Have you had to unprotect a password protected spreadsheet? If so, how do you do it? Dis you use a similar format to above or another method? Continue the discussion and add your thoughts in the comments section at the bottom of this article.

Please Share

If you liked this article or know someone who could benefit from this information, please feel free to share it with your friends and colleagues and spread the word on Facebook, Twitter and/or Linkedin.

 

  • I tried to use this method to unprotect a worksheet in Excel 2013 and it didn’t work for me. I asked “Mr Google” and apparently this code will work with earliest versions of Excel, but will not work with Excel 2013.

  • Phil

    Worked for me thanks a lot, I use office 2010

    • Glad it worked for you Phil – the code should work for Excel 2010 or earlier and “MAY” work on later versions

  • Steve

    Hi, how can view what the password is that is protecting the sheet?
    I can remove the PW but I need to know what the actual PW is as its the same PW that has been used to “open” a document too.

    • Hi Steve, this method will only give you a possible password that you can use to unprotect an excel worksheet, it unfortunately will not tell you what the original password actually is. The reason being Microsoft documents with password protection are now ACTUALLY encrypted, which means you either need to break the password (as per above) or break the encryption (not easy).

  • waqas

    I lost my excel file’s password , please advise that how i got again or process of forget it ?

  • Omart

    did not work. Tried in Excel 2010 and 2014 and no usable password popped up…. Help

    • Hi Omart,

      As we say in the article…

      “The following method MAY, we re-iterate MAY, work for you, but then again it may not as well. We have had a good amount of success using this method but it has not worked 100% of the time. Having said that, the method does not harm your spreadsheet nor data, so you have nothing to lose from trying it.”

      It is not a 100% guaranteed method, but we have found on more occasions than not that it works for us.

  • M3l5t3r

    Thank you!!! Just used it for a sheet in excel 2016 and the message that popped up after I ran it displayed, “One usable password is AABBBABABBB>”, then when I clicked “OK” the sheet was already unlocked without me even entering the password it showed. There were multiple sheets that I needed unlocked and the password it showed worked for every sheet, I could’ve also ran the code on every sheet to unlock them as well, however it only works on one sheet at a time (you cannot run it on several sheet at once).

  • A-T

    this is Fab just unlock my sheet. Thanks.

    • Hi A-T,

      Glad it worked for you. Cheers,Brian

  • Nettie

    I just used the method to unprotect the worksheet and it gave me a password and when I type it in and say ok it shows unprotected but still won’t let me do anything with the grayed out areas? Any advice?

  • Bruce Anger

    You used to be able to just open the spreadsheet in “open office” because the password only worked in excel, I’m not sure if that is still true.

    • Thanks for sharing Bruce. Don’t think that is the case anymore as in the later versions of Excel Microsoft now have stronger encryption to try and protect spreadsheets

  • PowerWheelz #BB10

    I am using Excel 2016 (Office365)
    and I get a compile error on run

    “Syntax error”

    It was do to the copy/paste created a new line after “Breaks” which cause “worksheet password protection.” to be uncommented.

    ‘Breaks
    worksheet password protection.

    Just thought I would share in case someone else encounters.
    I am still trying to run it but I may be one of the unlucky ones. its been spinning for 5 minutes and VB says its Not Responding

    • Fahad Shokat

      guys just convert the .xlsx sheet to .xls it will convert to older version, open it run the code and later convert it back to .xlsx.

      nothing major changes except the graphical enhancements would alter only.

      • Hi Fahad, Thanks for your comment. Yes converting to *.xls then running the code can sometimes help (unfortunately not 100% gauranteed). But also no harm in giving it a try…

        • Fahad Shokat

          well if you still dont wanna do this. there is another way as well. ;). convert to zip. find folder ” xl ” then folder ” worksheets “. then in this you will find all your spreadsheets. if you have few then you are lucky if alot than its a tedious job.

          soo open the xlm file in notepad find this line.

          this is the new encryption from microsoft 😀 they worked sooo hard for it, lolz delete this line. save and place your files in the zip file again. make sure your ziped files are modified, dont save in your temp folder 😛

          and than rename .zip file back to .xlsx and tadaaaaaaaaaa 🙂 your worksheets are unprotected completely 😉

          • Fahad Shokat

            PS: Brian this is 100% tested and guaranteed. though took me an hour to break, damn should have done computer engineering 🙁

            but i did electrical engineering 😀

          • Tim Carter

            Thank you Fahad this worked so very perfectly 🙂

          • Fahad Shokat

            your welcome tim. Lolz i last time broke the way to install HALO 2, posted the way to crack it on someones youtube. Microsoft came in and tagged my comment as spam, no wonder many people came and said that this really works and its sad some are tagging it as spam.

          • Vikram

            This is the best possible & easiest way to remove ‘Password’ from the protected sheet. Thanks Fahad

  • Mark Duffy

    Fab and worked in Excel 15.32v for MAC. perfect for getting me access to an old spreadsheet. Thanks

    • Glad it worked for you Mark.

  • Eric Quevedo

    Try this. In your MS Excel, save as MS Excel 5.0/95 workbook. Open the MS Excel 5.0/95 workbook in LibreOffice Calc and save as your MS Excel version (xls or xlsx). Open the last (xls or xlsx) file in your current MS Excel version. Worksheet should now be editable.

  • Jack

    Brain how to break the passwords in excel sheet i forgot my password

    • Hi Jack, Simply apply the principles shown in the article post. Plus there are a number of other suggestions in the comments you can try as well.

  • surethom

    No does not work on Excel 2016. Compile error on this
    For i2 = 65 To 66: For3 i3 = 65 To 66: For i4 = 65 To 66