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, if you want to allow others to edit data, you need to unprotect them.
If you know the password it’s really easy to unprotect a worksheet. Though unlocking an Excel spreadsheet without a password is not simple but you can still do it by 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 spreadsheets. If you already know the password of the protected sheet, you can easily remove protection. Just follow any of these steps:
Open the protected spreadsheet, and switch to the ‘Review’ tab and click the ‘Unprotect Sheet’ icon in the Changes group.
You can access the same above option by right-clicking the protected spreadsheet tab, then select 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 your password-protect your Excel workbook, you can’t change the structure of the workbook, such as add, move, rename, or delete worksheets, and view hidden sheets. But you still are able to edit the data on the worksheets 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 (option would 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, 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 you haven’t unlocked it for a long time and you forgot it, there are 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 and go to the ‘Developer’ tab and click the ‘View Code’ button on the ribbon. This will open up 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 worksheet that is password-protected, 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 and 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 theses 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 extension, right-click on the file, select ‘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.