How to Unprotect an Excel Sheet or Workbook With or Without Password

If you've ever found yourself unable to edit an Excel sheet or workbook because it's protected by a password you've forgotten, you're not alone. Excel's password protection feature is a handy tool for safeguarding your data, but it can be a nuisance if you can't remember the password. Fortunately, there are ways to unprotect an Excel sheet or workbook, both with and without the password. This guide will walk you through the steps to regain access to your protected Excel files.

If You Know the Password

If you remember the password, unprotecting your Excel sheet or workbook is easy. Here's how to do it:

1. Open your Excel file by double-clicking on the Excel document you want to unprotect.
2. Look for the 'Review' tab in the upper menu and click on it.
3. To unprotect Sheet: Click on 'Unprotect Sheet'. A dialog box will appear asking for the password. Enter the password and click 'OK'.

4. To unprotect a workbook: Click on 'Unprotect Workbook'. Similarly, enter the password when prompted and click 'OK'.

That's it! Your Excel sheet or workbook should now be editable.

Unprotect Excel Sheet Without Password

Using VBA Code

If you've forgotten the password for an Excel sheet, you can try using a VBA (Visual Basic for Applications) code to unlock it. Please note that this should only be used on your own Excel files for ethical reasons. Here's what you need to do:

1. Open the Excel file and go to the sheet you want to unprotect.
2. Press Alt + F11 to open the VBA Editor.
3. In the Visual Basic code editor, expand the ‘Microsoft Excel Objects’ option on the left pane, right-click the password-protected worksheet and select 'Insert', and then 'Module'.

4. In the module window, paste the following VBA code:

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

5. Click the 'Run' button on the toolbar, select the ‘Run Sub/UserForm’ option, or press F5 to execute the code. This process may take some time depending on the complexity of the password.

Using Zip File Format Trick

There’s another trick you can use to Unprotect a worksheet. Just follow these steps:

1. Open the Control Panel on your PC and select ‘File Explorer options’.

2. In the File Explorer Options screen, uncheck the ‘Hide Extensions for known file types’ to enable your file extensions. Then, click ‘Apply’ to apply the changes and click ‘OK’ to close the window. Now your file extensions of files will be visible.

3. In File Explorer, right-click on your password-protected Excel file and select the 'Rename' option, and replace .xlsx with .zip. Then hit ‘Enter’ and click ‘Yes’ in the Rename prompt box.

4. Next, open your Excel sheet zip file (that we renamed). In our case, the most important files to open are located in the /xl/ folder, where we have basically all that has been included in our Excel workbook.

5. Navigate to the xl > worksheets directory, you will see the list of all sheets (in XML format) available in your workbook.
6. Right-click on the password-protected sheet's XML file, hover your cursor over the 'Open with' option and select 'Notepad' from the list of apps.

7. Once the file is open in Notepad, find the following tag within its contents and delete it.

<sheetProtection password=… />

You can also use Notepad's Find function (CTRL + F)to quickly spot the sheetProtection tag.

8. After making the changes, save the XML file by pressing CTRL + S in Notepad and then close the file.
9. Re-zip all the extracted files into a zip file again. Then, change the extension back to .xlsx.

10. Open and check your spreadsheet. It will be unprotected.

This method only works in password-protected workbooks. If the file is protected with the ‘Encrypt with Password’ feature, this method won’t work.

Using Google Sheets

Yet another workaround that allows you to unprotect an Excel worksheet without a password. You’ll need a Google Drive account to do this.

1. Open your Google Drive account and click the ‘New’ button at the top left corner.

2. From the New menu, select ‘Google Sheets’ and click ‘Blank spreadsheet’.

3. In the blank spreadsheet, click ‘File’ in the toolbar and select ‘Import’.

4. In the Import file dialogue box, select the ‘Upload’ tab from the menu and click the ‘Select a file from your device’ button.

5. Browse and find the Excel workbook on your local drive, select it, and click ‘Open’ to upload. Or you can simply drag and drop the excel file into the Import file box.

6. While importing, select the ‘Replace spreadsheet’ option and click the ‘Import Data’ button.

7. This will import your protected Excel worksheet into your Google Sheets with all the data. You’ll notice that the worksheet is no longer protected, and you are free to edit the data.

8. Export the worksheet back to Excel format again. Click the ‘File’ menu from the toolbar, select the ‘Download’ option, and choose ‘Microsoft Excel (.xlsx)’ from the available options.

9. Give the file a new name and click ‘Save’.

You'll have the same exact Excel sheet with password protection removed.

Unprotect Excel Workbooks Without Password

If you have a password-protected workbook for which you can’t remember the password, then use the modified VBA Code and ZIP methods to unprotect the workbook as discussed below.

Using VBA Code

1. Open the Excel file with the protected workbook structure and then go to the ‘Developer’ tab and click the ‘Visual Basic’ button.

2. In the Visual Basic code editor, click the ‘Insert’ tab and select ‘Module’ option.

3. On the popup module (code) window, copy and paste the following code to unlock the workbook structure.

Sub Shareus()
        ActiveWorkbook.Sheets.Copy
          For Each sh In ActiveWorkbook.Sheets
            sh.Visible = True
          Next
        End Sub

4. Hit the ‘F5’ button or click the ‘Run’ button on the toolbar and select the ‘Run Sub/UserForm’ option to run the macro.

5. A new workbook will open with a different name. It is the same as the original workbook but without workbook structure protection.

Using Zip File Format Trick

You can unprotect the Excel workbook safely without a password by changing the file extension and manipulating its constituents.

1. Navigate to the Excel file with the protected workbook structure, rename and change its extension from .xlsx to .zip. Before you do that, Please make a copy of it for backup.

2. Extract the contents of the zip file using any software to a separate folder. The extracted files and folders would look like this:

3. Open the xl folder and right-click on the 'workbook.xml' file (the one that is password-protected) and open it with 'Notepad'.

4. Now find and select this whole protection tag and delete it.

<workbookProtection workbookPassword= …/>

You can also use Notepad's Find function by pressing CTRL + F to quickly locate the workbookProtection tag and delete it.

5. After removing the protection tag, save the ‘workbook.xml’ file. Then, zip (compress) all the extracted files back into a zip file.

6. Change back the extension of the file from .zip to .xlsx.

7. Open the Excel file, and you will find the password protection has been removed from the workbook.

By following these steps, you should be able to unprotect an Excel sheet or workbook, with or without the password. Always remember to keep your passwords safe and consider using a password manager to avoid similar situations in the future.