How to Unprotect a Sheet in Excel (5 Easy Methods)

Have you ever come across an Excel sheet that's locked, stopping you from making changes or updates? Whether it’s to prevent accidental changes or to keep sensitive information safe, sometimes you need to remove that protection to get back to editing. If you're wondering how to break Excel sheet protection, you're in the right place. Fortunately, unprotecting an Excel sheet is easier than you might think.

In this guide, we’ll show you 5 simple methods to unprotect a sheet in Excel, whether you’re using the latest version like Excel 365 or an older one like Excel 2010. We’ll cover everything from built-in options like the Review Tab and Right-click, to using Google Sheets and even a bit of VBA coding for those who need more advanced solutions.

What Does Unprotecting a Sheet Mean in Excel

Unprotecting a sheet in Excel removes the restrictions, allowing you to edit or make changes to the content freely. A protected sheet can prevent others from editing cells, formatting, or making structural changes, and unprotecting it gives you full control over those elements.

There are several scenarios where you may want to unprotect a sheet:

• You need to modify a protected sheet.
• You forgot the password and want to remove protection.
• You want to enable editing after sharing the workbook.

Method 1: Unprotect Sheet Using the Built-In Option

This method is the simplest way to unlock an Excel sheet if you know the password.

Step 1: Open Your Excel Workbook

Launch Excel and open the file containing the protected sheet.

Step 2: Select the Protected Sheet

Click on the worksheet tab that is protected and you want to unprotect.

Step 3: Go to the Review Tab and Click on Unprotected Sheet

In the top menu, go to the Review tab in the Ribbon, located between the Data and View tabs. In the Changes group, you'll find the Unprotect Sheet option. Click on it to remove the protection.

Step 4: Enter the Password (If Required)

If the sheet is not password-protected, it will unlock immediately. If the sheet is password-protected, Excel will prompt you to enter the password. Type in the correct password and click OK.

Step 5: Sheet Unprotected

Once the password is entered correctly, the protection will be removed, and you can make changes to the sheet.

Method 2: Unprotect a Sheet by Right-Clicking on the Sheet Tab

One Way to unlock an Excel sheet is by right-clicking directly on the sheet tab. This method is quick and convenient if you are already familiar with the layout of your workbook.

Step 1: Open Your Excel Workbook

Open the Excel file containing the protected sheet.

Step 2: Right- Click on the Worksheet Tab and Click on Unprotect Sheet

Right-click on the worksheet tab at the bottom of the Excel window that you wish to unprotect. From the context menu, select the Unprotect Sheet option.

Note: If multiple sheets are protected, you will need to remove protection from each sheet individually.

Step 3: Enter the Password (If Required)

If the sheet is protected by a password, you will be prompted to enter it. After entering the correct password, click OK.

Step 5: Sheet Unprotected

The sheet will now be unprotected, and you can begin making edits to it.

Method 3: Unlock Excel Sheets Using VBA Code (For Excel 2010 and Earlier Versions)

If you're using Excel 2010 or an earlier version and need to unprotect a sheet, you can do so by using VBA (Visual Basic for Applications) code. This method is effective when you don't have the password, and it's a great alternative to other methods. Here's a step-by-step guide on how to unprotect a sheet using VBA code:

Step 1: Open the Excel File

Open the Excel workbook containing the protected sheet that you want to unprotect.

Step 2: Open the VBA Editor

• Press Alt + F11 on your keyboard to open the VBA (Visual Basic for Applications) editor.
• Alternatively, you can click on the Developer tab and select Visual Basic.

Step 3: Insert a New Module

In the VBA editor, click on Insert from the menu, then select Module. This will create a new module where you can enter your code.

Step 5: Enter the VBA Code

In the new module, paste the following VBA code:

Sub Passwordprotectedsheet()

'Breaks worksheet password protection.

Dim i As Integer, j As Integer, k As Integer

Dim l As Integer, m As Integer, n As Integer

Dim i1 As Integer, i2 As Integer, i3 As Integer

Dim i4 As Integer, i5 As Integer, i6 As Integer

On Error Resume Next

For i = 65 To 66: For j = 65 To 66: For k = 65 To 66

For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66

For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66

For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _

Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _

Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

If ActiveSheet.ProtectContents = False Then

MsgBox "Password is " & Chr(i) & Chr(j) & _

Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _

Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)

Exit Sub

End If

Next: Next: Next: Next: Next: Next

Next: Next: Next: Next: Next: Next

End Sub

Step 5: Run the Code

• To run the code, press F5 or click Run in the VBA editor.
• The macro will attempt to unprotect the active sheet by trying different passwords.

Step 6: Check if the Sheet is Unprotected

Once the code runs, if the sheet was successfully unprotected, you'll see a message box with the password. If the code fails to find the password, you will be notified that the password was not found.

Step 7: Save the Unprotected Workbook

After unprotecting the sheet, save your workbook to keep the changes.

Important Notes:

• Excel 2010 and Earlier: This method works for Excel 2010 and earlier versions. If you are using Excel 2013 or later, it may not work due to increased security measures.
• Complex Passwords: This method is effective for simple passwords, but if the password is complex, you may need more sophisticated techniques or third-party tools.
• VBA Security: Always be careful when running VBA code. Only use code from trusted sources, as malicious code can damage your files or compromise security.

Using VBA can be a powerful way to unprotect Excel sheets, but it’s important to note that this method may not work on all files, especially those with stronger protection or encryption.

Method 4: Use Online Tools to Remove Protection from Excel Sheets

To unlock an Excel sheet using an online service, follow these steps:

Step 1: Search for an Online Tool

Search for "online password removal service" to find a website that offers tools to remove passwords from protected Excel files.

Step 2: Click on "Unprotect Your File"

Once you find a reliable online service, look for the option to unprotect or remove the password from your Excel file.

Step 3: Click "Browse" and Choose the Protected File

Click the "Browse" button to select the protected Excel file from your computer. Choose the file you want to unprotect.

Step 4: Choose the "Remove Password" Option

Select the "Remove Password" radio button or a similar option offered by the service to initiate the password removal process.

Step 5: Upload the File

Click "Upload" or "Remove Password" to begin the process. The tool will work on removing the password from the Excel sheet.

Step 6: Download the Unprotected File

Once the process is complete, download the unprotected version of the Excel file to your computer.

Important Note:

Make sure to use a trusted and reputable service, as online tools may not always be secure. Avoid uploading sensitive or confidential files to untrusted sites.

Method 5: Unlock Excel Sheets by Uploading to Google Sheets

You can also unprotect a protected Excel sheet by converting the file format. To do this, follow the steps below to upload the file to Google Sheets and remove the protection. Before you begin, ensure that the protected Excel file is saved on your desktop. However, uploading to Google Sheets could explain potential limitations, such as compatibility issues with certain Excel features (macros, cell locking, etc.), which may not function the same way in Sheets.

Step 1: Open Google Drive

Go to Google Drive and log in with your Google account.

Step 2: Upload the Excel File

• In Google Drive, click on the "New" button on the left sidebar.
• Select "File upload" and choose the Excel file you want to unprotect from your computer.
• Wait for the file to upload.

Step 3: Open the File in Google Sheets

• Once the file is uploaded, right-click on the file in Google Drive.
• Select Open with and choose Google Sheets. This will open the Excel file in Google Sheets format.

Step 4: Check the Protection

If the Excel file was only protected by worksheet structure (like hiding columns or locking cells), the protection may not carry over into Google Sheets, and you may be able to edit the sheet directly.

If the Excel file was password-protected, it may ask for a password, or it may not allow full functionality in Google Sheets.

Note: Google Sheets may not always handle all types of protection (such as password-based cell protection) in the same way as Excel. If the protection is password-based, Google Sheets might not allow you to remove it directly without the password.

Step 5: Remove Protection (if applicable):

If there are any restrictions like hidden sheets or locked cells, you can check for sheet protection in Google Sheets by clicking on the Data tab in the menu and selecting Protected sheets and ranges.

In the sidebar that appears, you can manage or remove the protection by clicking on the trash icon to delete the protection rules.

Step 6: Go to File, Select Download and Click on Microsoft Excel

After removing any protection, you can save your unprotected sheet by simply clicking File > Download and selecting Microsoft Excel (.xlsx) if you want to save it as an Excel file again.

Limitations When Using Google Sheets to Unprotect Excel Files

Excel Protection May Not Transfer:

• Some protection features in Excel, like cell locking or sheet protection, may not carry over to Google Sheets. You might be able to edit the sheet even if it was protected in Excel.

Issues with Password-Protected Sheets:

• If the Excel file is password-protected, Google Sheets may ask for the password, but it won’t let you remove protection without it. Google Sheets doesn’t handle password-based protections the same way Excel does.

In short, Google Sheets might not fully support all types of protection, especially password-based protection, and you may encounter issues accessing or editing protected sheets.

Conclusion

Unprotecting a sheet in Excel can be necessary for various reasons, whether you're updating a protected document, forgotten a password, or need to make adjustments to a shared file. There are multiple methods to unprotect a sheet, ranging from using Excel's built-in options to utilizing VBA code and online services.

Each method has its own benefits and limitations. For simple protection with a password, using the Review tab or right-click method may be sufficient if you know the password. However, if the password is unknown or the protection is complex, you may need to resort to alternative solutions like uploading to Google Sheets, using online services, or employing VBA code for older versions of Excel. While these techniques can remove protection, it's important to handle sensitive files with care and ensure you use trustworthy tools.

Understanding how to unprotect a sheet allows for greater flexibility in your workbooks and helps you regain control over your Excel files when needed.

Also Read:

• How to Protect Sheet in Excel
• MS Excel Tutorial
• Top Excel Formulas and Functions You Should Know 2024