Sometimes, we protect our excel sheet before sharing it with someone else. In protected sheets, people can’t make any changes. That’s how it stores the original data. But we may face problems if we forget the password. In that case, we can’t edit any cell. As a result, we need to unprotect the excel sheet without a password. Today, we will demonstrate 4 methods. Using these methods, you can easily unprotect an excel sheet without a password.
Download Practice Book
Download the practice book here.
4 Ways to Unprotect Excel Sheet without Password
To explain the methods, we will use a dataset that contains a weighted scoring model in a protected sheet.
If we try to make any change in the sheet, a message box appears. It says the sheet is protected.
1. Apply VBA to Unprotect Excel Sheet without Password
In the first method, we will learn to unprotect an excel sheet without a password with VBA. Applying VBA is easy and most of the time it works smoothly. Moreover, we can use this method in all versions of Microsoft Excel. Let’s follow the steps below to learn the method.
- First of all, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.
- Secondly, select Insert and then, select Module to open the Module window.
- In the third step, type the code in the Module window:
Sub Unprotect_Sheet_without_Password() Dim x As Integer, y As Integer, z As Integer Dim a As Integer, b As Integer, c As Integer Dim x1 As Integer, x2 As Integer, x3 As Integer Dim x4 As Integer, x5 As Integer, x6 As Integer On Error Resume Next For x = 65 To 66: For y = 65 To 66: For z = 65 To 66 For a = 65 To 66: For b = 65 To 66: For x1 = 65 To 66 For x2 = 65 To 66: For x3 = 65 To 66: For x4 = 65 To 66 For x5 = 65 To 66: For x6 = 65 To 66: For c = 32 To 126 ActiveSheet.Unprotect Chr(x) & Chr(y) & Chr(z) & _ Chr(a) & Chr(b) & Chr(x1) & Chr(x2) & Chr(x3) & _ Chr(x4) & Chr(x5) & Chr(x6) & Chr(c) If ActiveSheet.ProtectContents = False Then MsgBox "Password is " & Chr(x) & Chr(y) & _ Chr(z) & Chr(a) & Chr(b) & Chr(x1) & Chr(x2) & _ Chr(x3) & Chr(x4) & Chr(x5) & Chr(x6) & Chr(c) Exit Sub End If Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next End Sub
- After that, press the Ctrl + S key to save the code and then, close the Visual Basic window.
Note: If you are using Excel 2010 or earlier versions, you just need to save the code. And then run it by pressing the F5 key on the keyboard. Then, you will get a message box. You just need to press OK to unprotect the excel sheet without a password. In that case, you don’t need to follow the next steps.
- In the following step, click on the File tab.
- Then, go to the Save As section and save the file in Excel 97-2003 Workbook (*.xls).
- Now, open the file that you saved in the previous step.
- Next, go to the Developer tab and select Macros.
- As a result, the Macro window will appear.
- Select the desired code and Run it.
- Instantly, a message box with a fake password will appear. Don’t worry, you don’t need to remember the password. Just press OK to proceed.
- Finally, the excel sheet will be unprotected and you will be able to edit any cell.
Note: If you have multiple protected sheets in a workbook, then run the VBA code for each sheet individually.
2. Unlock Excel Sheet without Password Using the Copy-Paste Option
You can also unlock an excel sheet without a password using the Copy–Paste option. Here, you will not be able to break the password. But you can copy the contents of an excel sheet and paste them into a new sheet. Again, we will use the same dataset here. Let’s pay attention to the steps below to know more.
- In the beginning, open the password protected sheet and select Cell A1.
- After that, press Shift + Ctrl + End to select all used cells.
- Next, press Ctrl + C to copy the cells.
- In the following step, open a new sheet and select Cell A1.
- In the end, press Ctrl + V to paste the selected cells.
Note: You can use this method if the Select locked cells and Select unlocked cells actions are granted in the protected sheet.
3. Change Extension to Unprotect Excel Sheet without Password
Another way to unprotect an excel sheet without a password is to change the file extension. Here, we will change the file extension from .xlsm to .zip. This method is quite tricky. So, observe the steps carefully to implement the method successfully.
- In the first place, create a copy of the original excel file.
- Secondly, change the extension of the copied file to .zip using the rename option.
- A warning message will pop up. Select Yes to proceed.
- After that, open the zip file.
- Open the folder named ‘xl‘.
- Next, open the folder named ‘worksheets‘.
- Inside the ‘worksheets’ folder, you will find the sheets of the excel file in .xml format.
- You need to open the password protected sheet with a text editor like Notepad. In our case, sheet4 is the password protected sheet.
- After opening the sheet in Notepad, select Format and then, select Word Wrap.
- Now, press the Ctrl + F key to open the Find window.
- Type protection in the Find what field and click Find Next.
- It will highlight the word Protection.
- The most important part is to remove the whole line that contains the word Protection inside the <> symbol. The line is given below:
- Search for the above line and remove it by pressing the Backspace key.
- In the following step, save the file in the same folder that contains the zip file.
- Again, right-click on the zip file and open it in a new window.
- Then, go to xl >> worksheets.
- Delete the old sheet4.xml file and copy the new sheet4.xml file here in the ‘worksheets’ folder.
- Once again, change the extension of the file from .zip to .xlsx.
- Finally, open the file and make any changes you want.
4. Use Google Sheets to Unlock Excel Sheets without Password
In the last method, we will use Google Sheets to unlock an excel sheet without a password. It is also another easy method and may become handy in some cases. So, without any delay, let’s jump to the steps below.
- Firstly, open the Google Sheets application.
- Secondly, select File and then, select Import.
- In the third step, go to Upload and drag the excel file into the box.
- A message box will appear. Select Import Data from there.
- Next, you can go to the protected sheet and make changes in the Google Sheets.
- After that, go to File and select Download. A drop-down menu will occur.
- Select Microsoft Excel (.xlsx) to download the file in .xlsx
- In the end, open the downloaded file and make sure you can edit it without any password.
Things to Remember
There are certain things you need to remember to unprotect an excel sheet without a password.
- Most importantly, do not use the methods for dishonest causes. Use the methods if the sheet is owned by you and you have forgotten the password.
- To unprotect multiple sheets, use the methods individually for each sheet.
- Make a copy of the original file, because you may lose data after using the methods. So, it’s better to apply the methods in a copied file.
In this article, we have demonstrated 4 easy methods to Unprotect an Excel Sheet without Password. If you don’t want to make extra changes, then avoid Method-2. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.