This tutorial illustrates how to unprotect an Excel sheet if we forgot the password. To keep our worksheet or workbook confidential, we set a password. The password protection prevents the other users from making changes to our worksheet. But, there is a possibility that we may forget the password after setting it. However, if we forget the password, we will not be able to read or edit the exit file. To unprotect an Excel sheet without a password follow this article.
Unprotect Excel Sheet If Forgot Password: 4 Effective Methods
Throughout this article, we will show 4 effective methods to unprotect an Excel sheet if we forgot the password. To illustrate the methods we will use the following dataset that contains several different food types and their average price. Now notice the ribbon under the Home tab. We can see that many commands under the Home tab are not available as the worksheet is password-protected.
More specifically, if we try to make any changes to the worksheet, a message box like the following image will appear. It gives us the warning that the worksheet is protected.
1. Unprotect Excel Sheet with VBA If Forgot Password
First and foremost, we will use a VBA code to unprotect an Excel sheet if we forget the password. We can use the code of this method directly in Microsoft Excel 2010 or earlier versions. But, if we are using the later versions of Microsoft Excel 2010, we have to convert the file in Excel 97-2003 workbook (*.xls) format first. Then we will apply the VBA code in the new format. Let’s see the steps to perform this method.
- To begin with, go to the Developer tab. Select the option Visual Basic.
- The above command will open the Visual Basic window.
- In addition, go to the Insert tab.
- Furthermore, right-click on the sheet name. Select Insert > Module.
- Afterward, a blank VBA code window will appear.
- Next, type the following VBA code in that blank code window:
Sub Unprotect_Sheet_If_Password_Forgotten() Dim a As Integer, b As Integer, c As Integer Dim x As Integer, y As Integer, z As Integer Dim z1 As Integer, z2 As Integer, z3 As Integer Dim z4 As Integer, z5 As Integer, z6 As Integer On Error Resume Next For a = 65 To 66: For b = 65 To 66: For c = 65 To 66 For x = 65 To 66: For y = 65 To 66: For z1 = 65 To 66 For z2 = 65 To 66: For z3 = 65 To 66: For z4 = 65 To 66 For z5 = 65 To 66: For z6 = 65 To 66: For z = 32 To 126 ActiveSheet.Unprotect Chr(a) & Chr(b) & Chr(c) & _ Chr(x) & Chr(y) & Chr(z1) & Chr(z2) & Chr(z3) & _ Chr(z4) & Chr(z5) & Chr(z6) & Chr(z) If ActiveSheet.ProtectContents = False Then MsgBox "The Password is " & Chr(a) & Chr(b) & _ Chr(c) & Chr(x) & Chr(y) & Chr(z1) & Chr(z2) & _ Chr(z3) & Chr(z4) & Chr(z5) & Chr(z6) & Chr(z) Exit Sub End If Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next End Sub
- Now, click on the Run button or press the F5 key to run the code.
- As a result, a message box like the following image appears. This message box contains a fake password. We do not need to copy or remember the password. Just press OK.
- Finally, we got our worksheet unprotected. Now, like in the following image, we will be able to edit the value.
Note: If a workbook contains several protected sheets, run the VBA code for each sheet separately.
2. Use Zip Option to Unprotect Excel Sheet without Password
Changing the file extension is another approach to unprotecting an Excel sheet without a password. We will change the extension of the file from .xlsx to .zip. This strategy is pretty challenging. Just follow the below steps to perform this method.
- Firstly, go to Control Panel > Appearance and Personalization > File Explorer Options.
- The above commands open a dialogue box named ‘File Explorer Options’.
- Secondly, in the dialogue box go to the View Check the option ‘Hide extensions for known file types’, and click on Apply.
- Thirdly, change the extension of the .xlsx file in the .zip file using the rename option.
- A warning message will appear. Select Yes to go ahead.
- Now we can see the file is zipped.
- Next, right-click on the .zip file and select Extract All.
- Then open the folder named xl.
- Afterward, open the folder named worksheets.
- Furthermore, select and right-click on sheet1.xml. Open that file with Notepad.
- In addition, press Ctrl + F to open the Find Type the text protection in the Find what text field and click on Find Next.
- The above command will highlight the term protection.
- The most vital part is to delete the entire line including the term protection inside the <> symbol. Here’s what the line is:
- Moreover, zip the files again.
- After that, change the extension from .zip to .xlsx.
- A warning message will appear. Select Yes to proceed further.
- Lastly, open the .xlsx We can edit the new file like the following image.
3. Unprotect Excel Sheet Using Google Sheet If Anyone Forgot Password
In the third method, we will use Google Sheets to unprotect an Excel sheet if we forgot the password. This method is easy and does not contain any complex steps. Just follow the below steps to perform this method.
- First, open a blank spreadsheet in Google Sheets.
- Next, go to the File tab and select the option Import.
- Then, go to the Upload option and drag the protected Excel workbook into the box.
- A new dialogue box appears. Click on the option Import tab.
- As a result, we can see the data of the protected Excel sheet in Google Sheets. Also, we can make changes in the data of Google Sheets.
- After that, go to the File Download the file in Microsoft Excel (.xlsx) format.
- In the end, the Excel file gets unprotected. We can edit the file now like the following image.
4. Copy Contents of Protected Sheet to Another When Password Is Forgotten
Another method to unprotect an Excel sheet when the password is forgotten is to copy the content of the sheet. We won’t be able to crack the password here. However, you can copy and paste the contents of an Excel sheet onto a new sheet. We’ll utilize the same dataset as before. Let’s look at the steps below to learn more.
- In the first place, open the password-protected sheet.
- Next, press Shift + Ctrl + End or click on the triangle icon at the bottom left corner to select all used cells.
- Then, press Ctrl + C to copy the cells.
- Furthermore, open a new Excel sheet and select cell A1.
- After that, press Ctrl + V.
- Lastly, we can see the following file is unprotected.
Note: You can use this method if the protected sheet allows you to select locked and unlocked cells.
Read More: How to Unlock Excel Sheet for Editing
Download Practice Workbook
You can download the practice workbook from here.
In conclusion, this tutorial demonstrates how to unprotect an Excel sheet if you forgot the password. Download the practice worksheet contained in this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to respond to your message as soon as possible. Keep an eye out for more inventive Microsoft Excel solutions in the future.