Unprotect Excel Sheet (Complete Guideline)

In this article, we will unpack 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.

Download Practice Workbook

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 an Excel Sheet with a Password

We can unprotect an Excel sheet for which we have the password using the Unprotect feature, Format feature, Peripheral device, and VBA macros.

Method 1 – Using the Unprotect Feature

To start with, let’s unprotect an Excel sheet with a password using the Unprotect feature.

Steps:

• Go to the Review tab >> Select Unprotect Sheet from the Protect group.

The Unprotect Sheet dialog box pops up.

• Enter the password in the password field >> Click OK.

The worksheet will become unprotected.

Method 2 – Using the Format Feature

We can also apply the Format feature to unprotect sheets by entering the password.

Steps:

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

The Unprotect Sheet dialog box will appear.

• Insert the password in the Password field >> Click OK.

The worksheet will become unprotected.

Method 3 – Using a Peripheral Device to Unprotect an Excel Sheet

Similarly, we can use a peripheral device named Mouse to unprotect the sheet.

Steps:

• Right-click on the sheet named Mouse.
• In the window that pops up, select the Unprotect Sheet option.

The Unprotect Sheet dialog box pops up.

• Type the password in the Password field >> Click OK.

The worksheet will become unprotected.

Method 4 – Executing VBA Code

Now we’ll unprotect an Excel sheet without having the password using VBA. As a bonus, this method should work in all versions of Excel.

Steps:

• Press Alt + F11 to pop up the Microsoft Visual Basic Applications window.
• Select Module from the Insert tab to open a Module for inserting the VBA code.
• Paste the below VBA code in that Module.
• Run the code by pressing F5.

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

The sheet specified in the code will be unprotected.

How to Unprotect an Excel Sheet Without a Password

We can  unprotect an Excel sheet where we don’t have the password in 3 more ways: by using the Copy Paste option, VBA macros, and changing the File extension.

Solution 1 – Using the Copy Paste Option

We can unlock an Excel sheet without a password using the Copy–Paste option. We can’t break the password, but we can copy the contents of an Excel sheet and paste them into a new sheet, then edit them from there.

Steps:

• Open the password-protected sheet and select Cell A1.
• Press Shift + Ctrl + End to select all used cells.
• Press Ctrl + C to copy the cells.

• Open a new sheet and select Cell A1.
• Press Ctrl + V to paste the copied cells.

Note: You can only use this method if the Select locked cells and Select unlocked cells permissions have been granted in the protected sheet.

Solution 2 – Running Excel VBA Code

With this method, we can unprotect an Excel sheet without a password in all versions of Excel.

Steps:

• Open a new Module using the procedure described in Method 3 above and in it, paste the following 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

• Run the code.

A message box with a fake password will appear.

• Just click OK to proceed.

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, run the VBA code for each sheet individually.

Solution 3 – Changing the File Extension

Another way to unprotect an Excel sheet without a password is to change the file extension from .xlsm to .zip, then edit the .xml files inside the .zip.

Steps:

• Create a copy of the original Excel file.
• Change the extension of the copied file to .zip using the Rename option in the Windows context menu..

A warning message will pop up.

• Select Yes to proceed.
• Open the zip file.
• Open the folder named ‘xl‘.
• Open the folder named ‘worksheets‘.

Inside the ‘worksheets’ folder are the sheets of the Excel file in .xml format.

• Open the password-protected sheet (in our case, sheet3) with a text editor like Notepad.

• In Notepad, select Format and then Word Wrap.
• Press Ctrl + F to open the Find box.
• Type protection in the Find what field and click Find Next.

The word Protection will be highlighted.

This the critical bit:

Now we remove the whole line that contains the word Protection inside the <> parentheses. In this case, this is the line:

• Select the whole line and press the Backspace key to delete it.
• Save the file in the same location as the .zip file.
• Right-click on the zip file and open it in a new window.
• Go to xl >> worksheets.
• Delete the old XML file and copy the new sheet4.xml file into the folder to replace it.
• Change the extension of the file from .zip back to .xlsx.

You’ll now be able to open the file and edit as desired.

How to Remove a Password from an Excel Workbook

If you are the owner of a password-protected file, here’s how to remove the password from an Excel file encrypted with a password.

Steps:

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

• Remove the password from the Password field >> Click OK.

The file can now be opened without a password. As long as the password field is empty, no password is required.

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 “protect” code from all the .xml files for a protected worksheet.

Frequently Asked Questions

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

Answer: You may have forgotten the password, or simply want to make changes.

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, provided you know the password. Excel provides built-in functionality to unprotect a sheet, as discussed above. However if you have forgotten the password, you may need to resort to external tools or services to bypass the protection.

Remove Symbol in Excel: Knowledge Hub

• How to Unprotect Excel Sheet Without Password
• How to Unprotect Excel Sheet If Forgot Password
• How to Unlock Excel Sheet for Editing
• How to Unlock Grayed out Menus in Excel
• How to Unprotect Excel Sheet with Password

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