How to unprotect an Excel sheet (remove password)

by

Anyone who has ever worked in an office has gone through something like this: when accessing an Excel spreadsheet with important data, the user discovers that the creator of the file has protected it with a password, preventing others from changing it. And often, the author (or the user himself) has forgotten the password or has already changed jobs.

Still, you can unprotect an Excel spreadsheet and have access to raw data, either by dribbling the password (in some cases) or by directly tinkering with developer options, with macros that can overcome the barrier. Here, we teach two ways of doing this, assuming you do not know what the file’s password is.

How to unprotect an Excel worksheet: Copying data

If the Excel file has been saved in .xlsx format, which is the default version of Office 2010, the simplest method to unprotect a worksheet is to copy the data to another file. By default, the lock options offered by Excel allow the user to select the cells (for copying), which makes this method viable.

  1. Open an Excel protected worksheet;
  2. Click the Select All button to select all cells in the worksheet;
  3. Go to the Home menu and click Copy, or use the shortcut Ctrl + C (in MacOS, Command + C );
  4. Click File, and then click New ;
  5. In the blank worksheet, click the Paste button on the Start menu or use the shortcut Ctrl + V (in MacOS, Command + V ).

And ready.

In this way, all the information in the protected worksheet will be copied to a new file, which you can edit.

You can also copy the data and paste it into a Google Docs spreadsheet, doing the same procedure up to step 3.

After that:

  1. Go to the Google Docs spreadsheets site;
  2. Enter your account information and password;
    If you are not logged in or create a new account;
  3. Under Start a new worksheet, click the Blank option;
  4. Click Edit and then on Paste or use the shortcut Ctrl + V/Command + V.

And ready.

How to unprotect an Excel worksheet: using macros

There are cases, however, that the above method may not work: if the spreadsheet’s author blocked all editing options for the document, including the cell selection, it will not be very different from a PDF file, allowing only the user without access the password can read it and nothing more.

To get the password out of fully protected files, you need to create a macro that is capable of showing it, and this is only possible through the Office developer options.

However, this procedure only works with .xls files, the default up to Office 2010. You can save an .xlsx file as .xls, but you risk losing data and functions. Knowing this, and having already saved the file in the old format, just move on.

Enabling Developer Options on the Excel Menu

First of all, you have to have Excel display the developer options in the main menu:

  1. Click the File menu, and then click Options (in MacOS, in the Excel and Options menu);
  2. Click Ribbon and Toolbar;
  3. On the right, under Top Tabs, check the Developer box and click Save.

Once this is done, the Developer menu will be available.

Creating a macro to reveal passwords

Now comes the longest part of the process, which is to create a macro capable of revealing the passwords of protected worksheets.

  1. Select the Developer tab and click Save Macro;
  2. Under Save macro in, choose the Macro Personal Workbook option ;
  3. Click OK;
  4. on the Developer tab, click Stop Recording, and then in Visual Basic;
  5. Double-click PERSONAL.XLSB, double-click the Modules folder, and then double-click Module1;
  6. Delete any information that is in the window and replace with the following code (copy and paste):
Sub PasswordBreaker()
    'Author unknown
    '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

Once this is done, click the Save button (the floppy disk icon), and then close Visual Basic.

To remove passwords:

  1. With the file open, go to the Developer menu and click Macros;
  2. Select the macro you just created (in this case, PasswordBreaker ) and click Run;
  3. The process may take some time, but done so, the macro will show the document password. Write it down;
  4. On the Review menu, click Unprotect Sheet;
  5. Enter the password that the macro revealed and click OK.

And ready, your file is unprotected.

Leave a Comment