Unlock Excel Spreadsheets

If you are regularly using Microsoft Excel, maybe at work or at home, someone, sometime, somewhere, must have sent you a locked spreadsheet.

Locked spreadsheets can be useful to keep people from editing parts of the spreadsheet that they are not meant to or accidentally deleting important formulas. However when you are trying to get things done a locked spreadsheet is nothing but an annoyance. Especially when even the copy function is locked and you can’t transfer the data to a clean unlocked spreadsheet.

In this post we will unlock these spreadsheets to make life easier!

Note: The method below works for spreadsheets that are locked for editing after you open them. Not for password protected spreadsheets. For those, password cracking methods are required which are not the scope of this post.

It’s actually quite simple to do. All you need is to run some VBA code and the spreadsheet will unlock. 

This method however is not 100% guaranteed. Some spreadsheets will not unlock using this method. The version of Microsoft Excel plays a major part in this. I’ve had great success with spreadsheets created using older versions of Excel (older than Office 365 and Excel 2019).

VBA Code

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

How it Works

First thing is to open the spreadsheet you want to unlock.

Then click ALT+F11 to enter the VBA environment.

After double click on the worksheet you wish to unlock from the list on the left to get the VBA code console.

You then need to paste the VBA code above and click on the green “Play” icon to run the code.

The spreadsheet should now be unlocked and you should also get a popup with a usable code.

If the worksheet is not automatically unlocked, you can try the generated code to unlock it manually by navigating to the “Review” section of the Excel tool ribbon and selecting “Unprotect Sheet”.