Step-by-Step Guide to Unprotecting Excel Workbooks

How to Unprotect Excel Workbooks

Unprotecting an Excel workbook can be straightforward if you are the owner with the password, or a bit more complex if you’ve forgotten it. This guide explains both methods, applicable to Excel 365, Excel 2019, 2016, and 2013.

Unprotecting an Excel Workbook as the Owner

Excel provides a feature to protect your files at different levels, ensuring data integrity and security. If you need to make changes to a protected workbook and you know the password, follow these steps:

1. Open the Protected Workbook: Launch the protected Excel file.
2. Navigate to the Review Tab: Click on the Review tab in the ribbon.
3. Select Unprotect Sheet: In the Changes group, click on Unprotect Sheet. Alternatively, you can right-click the protected sheet tab and choose Unprotect Sheet.
4. Enter the Password: A dialog box will prompt you to enter the password. Type in the password and click OK.

Once you’ve entered the correct password, the workbook will be unprotected, allowing you to make necessary modifications.

Unprotecting an Excel Workbook Without Knowing the Password

If you’ve forgotten the password, you can still unprotect the workbook using a Visual Basic for Applications (VBA) macro. Here’s how to do it:

1. Open the Protected Workbook: Start by opening the Excel file you want to unprotect.
2. Access the Visual Basic Code Editor:

• Press ALT + F11 to open the VBA editor, or
• Go to the Developer tab and select View Code.

1. Enter the VBA Code: In the VBA editor, enter the following code in the code window:

Sub PasswordBreaker()
    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

4. Run the Code: Press F5 or click Run in the VBA editor to execute the macro.

The macro will attempt various combinations to unprotect the sheet. This process might take a few minutes. Once complete, a message box will display a password. Click OK and your sheet will be unprotected.

FAQ

How do I protect workbooks in Excel?
To password-protect an Excel workbook, follow these steps:

1. Open the workbook.
2. Go to File > Info > Protect Workbook > Encrypt with Password.
3. Enter and confirm your desired password.

How do I protect cells in Excel?
To lock specific cells in Excel:

1. Highlight the cells you want to protect.
2. Go to the Home tab.
3. Select Format > Lock Cell.