Unprotect Excel Sheet (Complete Guideline)

In this article, we will learn how to unprotect an Excel sheet. We’ll also learn how to protect worksheets or workbooks and even remove passwords from worksheets or workbooks in Excel.

You may need to protect your worksheet to prevent any change in data or as required. But you must unprotect the sheet to be able to edit it again. You can unprotect the sheet by providing the password used to protect the sheet. But what if you forget the password? Are all of your data lost? Don’t worry. This article also highlights a way around that case.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Unprotect Excel Sheet,xlsm

Why Protect an Excel Sheet?

Protecting an Excel sheet adds a layer of security to your data, preventing unauthorized users from making any changes to the worksheet’s structure or content. By protecting a sheet, you can ensure the integrity and confidentiality of your information, especially when sharing the file with others.

However, there are instances when you may need to modify or work with a protected Excel sheet. Perhaps you’ve forgotten the password, or you’ve inherited a protected workbook from a former colleague, making it challenging to perform necessary updates or retrieve critical information. In such cases, unprotecting the Excel sheet becomes necessary.

How to Unprotect Excel Sheet with Password

Here we’ll learn how to unprotect an Excel sheet with a password using the Unprotect feature, Format feature, Peripheral device, and VBA macros.

1. Use Unprotect Feature

We’ll unprotect an Excel sheet using the Unprotect feature with a password. Let’s follow the instructions below to learn!

Go to the Review tab >> Select Unprotect Sheet from the Protect group.
As a result, an Unprotect Sheet dialog box pops up >> Enter the password in the password field >> Press OK.
Finally, the worksheet will become unprotected.

2. Apply Format Feature

Here we will apply the Format feature to unprotect sheets in Excel.

Select the Review tab >> Choose Format drop-down list from the Cells group >> Select Unprotect Sheet option.

Hence, an Unprotect Sheet dialog box will appear >> Insert the password in the Password field >> Hit OK.
After that, the worksheet will become unprotected.

3. Use a Peripheral Device to Unprotect Excel Sheet

Now you will use a peripheral device named Mouse to unprotect the sheet.

Left-click on the sheet named Mouse >> A window pops up >> Select Unprotect Sheet option.

As a result, an Unprotect Sheet dialog box pops up >> Type the password in the Password field >> Press OK.
Hence, you will be able to unprotected the worksheet.

4. Execute VBA Code

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.

Press Alt + F11 to pop up the Microsoft Visual Basic Applications Hence, select Module from the Insert tab to open a Module for inserting the VBA code.
Paste the below VBA code in that Module, and run the VBA

Sub Unprotect_Sheet()
            Sheets("VBA_with").Unprotect ("123")
End Sub

As a result, you will be able to unprotect specific sheets.

How to Unprotect Excel Sheet Without Password

Now, we’ll unprotect an Excel sheet using the Copy Paste option, VBA macros, and changing File extension.

1. Using Copy Paste Option

You can 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. Let’s follow the instructions below to learn!

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.

2. Run an Excel VBA Code

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.

Open a new Module and paste the below VBA code. Hence, run the VBA code.

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

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.

3. Change File Extension

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.

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, sheet3 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
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 <> The line is given below:

Search for the above line and remove it by pressing the Backspace key.
Now, 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 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.

How to Remove Password from Excel Workbook

In this section, we’ll learn how to remove passwords from the Excel files encrypted with passwords. Let’s follow the instructions below to learn!

Go to the File tab >> Select Info from the panel on the left-hand side.
Next, select Protect Workbook from the right >> Click on Encrypt with Password from the drop-down menu.

Now remove the password from the Password field >> Press OK.

Now you can reopen the file without any password. As long as the password field is empty you can access it without any password from this point on. This is one way to remove the password from an Excel file.

Things to Remember

You need to unprotect each sheet one at a time if multiple sheets of your workbook are protected.
You need to delete the code from all .xml files for protected worksheets.

Frequently Asked Questions

1. Why would I need to unprotect an Excel sheet?

Answer: You may need to unprotect an Excel sheet if you have forgotten the password, or if you want to make changes to a protected sheet that was previously restricted.

2. Can I unprotect an Excel sheet if it is protected with restricted editing permissions?

Answer: If the Excel sheet is protected with restricted editing permissions, you may need to request permission or credentials from the person who set up the restrictions. They can grant you the necessary access or make changes on your behalf.

3. Is it possible to unprotect an Excel sheet without any specialized software or tools?

Answer: Yes, you can unprotect an Excel sheet without specialized software if you know the password. Excel provides built-in functionality to unprotect a sheet, as mentioned earlier. However, if you have forgotten the password, you may need to resort to external tools or services to bypass the protection.

Conclusion

knowing how to unprotect an Excel sheet allows users to regain access to their data and make necessary modifications.

Utilizing password recovery tools, VBA macros, or online resources can provide effective solutions for unprotecting Excel sheets and managing data securely.

Remove Symbol in Excel: Knowledge Hub

<< Go Back to How to Unprotect in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!