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

🔊

You can unprotect an Excel Sheet or Workbook with or without a password by using a VBA code, a ZIP file, or Google Sheets. If a password is known, use the 'Unprotect Sheet' option in the 'Review' tab. If a password is unknown, use the VBA code or a ZIP file to remove the protection tag from the worksheet/workbook.xml file.

Password protection in Microsoft Excel is often used in workplaces to protect important data. Microsoft Excel allows users to protect the worksheets and prevents others from making changes to the original data.

There are two ways a spreadsheet can be secured. One of the ways is using no password, which can be unprotected by anyone. In the other case, the worksheet or workbook is password protected, which means you’ll need to have the password to unlock it. In this article, we will show you how to unprotect worksheets or workbooks with or without a password in Excel.

Unprotect Worksheets/Workbook in Excel. One of the best features of Excel is that it can protect your Excel files at the cell, spreadsheet, and/or workbook level. After locking and protecting worksheets or workbooks, you need to unprotect them if you want to allow others to edit data.

If you know the password, it’s effortless to unprotect a worksheet. Though unlocking an Excel spreadsheet without a password is not simple, you can still do it using the following tricks.

How to Unprotect Excel Sheet with Password/No Password

It's very easy to unprotect an Excel sheet and allow users to edit and modify the spreadsheet. If you already know the password of the protected sheet, you can easily remove the protection using the following instructions:

Open the protected spreadsheet, and switch to the 'Review' tab and click the ‘Unprotect Sheet’ icon in the Changes group.

You can access the above option by right-clicking the protected spreadsheet tab and selecting the ‘Unprotect Sheet’ option from the context menu.

If your sheet is a password-protected worksheet, Excel will prompt you to enter the password. Type the password in the Unprotect Sheet dialog box and click ‘OK’.

If your worksheet was not protected with a password, clicking the ‘Unprotect Sheet’ option is enough to unlock your sheet.

How to Unprotect Excel Workbook with Password/No Password

When you password-protect your Excel workbook, you can't change the workbook's structure, such as adding, moving, renaming, deleting worksheets, or view hidden sheets. But you can still edit the worksheets' data even if your workbook is password-protected. If you wish to change the Excel workbook structure, like adding or removing worksheets, you must unprotect the Excel Workbook structure first.

To remove the workbook protection, open the protected workbook, and click the ‘Protect Workbook’ button (the option will be highlighted in gray color) under the Review tab.

Type the password in the Unprotect Workbook prompt box and save the workbook.

Now your workbook is unlocked, and you are free to edit the Excel Workbook structure.

How to Unprotect Excel Worksheet Without Password

If you have a password-secured worksheet and you have no idea what the password is or haven't unlocked it for a long time and forgotten it, there are a few ways to unprotect that Excel sheet.

Unprotect Excel Worksheet with VBA Code

Excel’s worksheet protection is based on a simple encryption algorithm. Even when you protect your Excel worksheet with a password, anyone with the below VBA code can crack it in minutes.

You can unlock a password-protected sheet by using a VBA code as a macro to identify the password. Here’s How:

Open the password-protected sheet, go to the ‘Developer’ tab, and click the ‘View Code’ button on the ribbon. This will open the code editor window in Microsoft Visual Basic for Applications.

Or you can go to the ‘Developer’ tab and click the ‘Visual Basic’ button. 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 –> Module from the context menu.

In the Code window of the protected sheet, copy and 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

Click the ‘Run’ button on the toolbar, select the ‘Run Sub/UserForm’ option, or press ‘F5’ to execute the code.

The code will take a couple of minutes to crack the password. Once it’s done, you’ll receive a pop-up with a cracked password, which is not the original one (it’s usually some combination of A’s and B’s), but still, it works. Click ‘OK’ and the sheet will be unprotected.

You need to save the Excel file before closing (as Macro-Enabled Workbook) after pasting the code to the module.

Unprotect Excel Sheet Without Password Using Zip

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

First, navigate to Control Panel and open ‘File Explorer options’.

In the File Explorer Options window, 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.

Now located your protected excel file in your drive and rename and change its extension from .xlsx to .zip.

To change the extension, right-click on the file, select the ‘Rename’ option, and replace ‘.xlsx’ with ‘.zip’. Then hit ‘Enter’ and click ‘Yes’ in Rename prompt box.

Now, your Excel file is a Zip file.

Next, extract the Zip file as shown below. 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. Now we can see it as separate .xml files.

Now navigate to ‘xl > worksheets > sheet 1.xml’ (which is the protected sheet). When you open the ‘/xl/worksheets/’ directory, you will see the list of all sheets (in XML format) available in your workbook. Then, open the sheet 1.xml file with Notepad or WordPad (right-click on the file and select a text editor from the ‘Open with’ context menu).

Find the following tag and delete it:

<sheetProtection password=… />

If you have a large amount of information in the worksheet, it will be hard to locate the ‘sheetProtection’ tag. So press Ctrl + F to open the Find feature, type ‘protection’ in ‘Find what’, and click ‘Find Next’. It will find the word ‘Protection’ and highlight it. Now, you can select the ‘sheetProtection’ and delete it.

After that, save the XML file and re-zip all the extracted files into a zip file again. Then, change the extension back from .zip to .xlsx.

Now, 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.

Unprotect an Excel Sheet with a 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.

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

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

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

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

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.

In the Import file window, select the ‘Replace spreadsheet’ option and click the ‘Import Data’ button.

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

You can now export the worksheet back to Excel format again.

In the now unprotected Google sheet, click the ‘File’ menu from the toolbar and select the ‘Download’ option. Then choose ‘Microsoft Excel (.xlsx)’ from the context menu.

Then give the file a new name and click ‘Save’.

Now you have the same exact Excel sheet, but it is no longer password-protected.

How to Unprotect an Excel Workbook Without Password

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

Unprotect Excel Workbook Without Password Using VBA Code

You can also unprotect workbook structure in Excel using Microsoft Visual Basic for Application (VBA) feature. Here’s How:

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

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

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

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

Then a new workbook opens up with a different name. It is the same as the original workbook but without workbook structure protection. Now you unprotected the workbook structure in Excel without knowing the password.

Unprotect Excel Workbook Without Password Using Zip

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

Get the excel file with the protected workbook structure, rename and change its extension from .xlsx to .zip as we did earlier for the protected worksheet. Before you do that make a copy of it for backup.

Then extract the zip file using some file archiver software like WinRAR or 7zip and you’ll get some folders and files as shown below.

Then open the ‘xl’ folder and open the ‘workbook.xml’ file with Notepad (which contains protection tag)

Now find and select this whole protection tag and delete it:

<workbookProtection workbookPassword= …/>

If you are having a hard time finding this tag, just press Ctrl + F to open the Find dialog, type ‘protection’ in ‘Find what’, and click ‘Find Next’. It will find the word ‘Protection’ and highlight it for you. Now, you can highlight the ‘workbookProtection’ tag and delete it.

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

Now all you have to do is change the extension of the file from ‘.zip’ to ‘.xlsx’.

Now the zip file will be converted back to the Excel file and you will find the protected password has been removed from the workbook.

That’s how you unprotect Excel worksheet/workbook.

Search This Blog

SALAI MEP