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.
STEPS:
- 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.
Read More: Excel VBA: How to Unprotect Excel Sheet without Password
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.
STEPS:
- 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.
Read More: How to Unlock Excel Sheet for Editing (With Quick Steps)
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.
STEPS:
- 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.
Read More: How to Unprotect Excel Sheet If Forgot Password (4 Effective Methods)
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.
STEPS:
- 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.
Read More: How to Unprotect Excel Sheet with Password (2 Quick Methods)
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.
Conclusion
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.
Your title refers to 4 easy ways. Unbelievable! I was going to print it and realized it would require 22 pages.
Maybe these are 4 easy ways if you are a developer or an electrical or an electronics engineer and doing research on Microsoft Excel.
But I’m doing office work and I use Excel as only a tool to help me do a job. And, in this case, I’m using my computer at home, alone, with no-one else using my computer to justify the need for “protection”. So why, all of a sudden, did my worksheet get “protected”? Could I have done something inadvertently that caused it to be “protected”?
So I have 3 questions:
(1). How do I find my password
(2). Why haven’t I needed it for the past 4-6 many years?
(3). How can I get a sheet “unprotected”, or actually turn off all protection completely, if I can’t find my password?
Since you are truly knowledgeable with Excel, do you have the ability to talk down to my level, in terms a common, lay person can understand? Please?
Thank you
Hello Tom,
I am replying to you on behalf of ExcelDemy. It is very unfortunate to know that, you are facing such difficulties. I have gone through your queries and thought of giving the following solutions.
1. First of all, please check this link in case you unintentionally protected your excel file through any of the methods described.
https://www.exceldemy.com/protect-excel-sheet-from-copy-paste/
2. When any worksheet or workbook is password protected, you cannot see them in actual letters, rather than some dots. Therefore, if you cannot unprotect the sheet with the help of this article, I am referring you to another one with some easy VBA codes to unprotect your file permanently without knowing the password.
https://www.exceldemy.com/unprotect-all-sheets-in-excel-vba/
3. You also mentioned that you are having trouble printing the worksheet. In this case, check this solution to solve this issue.
https://www.exceldemy.com/excel-margins-not-printing-correctly/
I hope the solutions will help you. Let us know your feedback.
Thank you!