Unprotect Excel Sheet Without Password

by

It’s a common scenario to protect an Excel sheet with a password, only to find yourself in the frustrating situation of forgetting the password.

Depending on your protection options, forgetting the password can prevent you from performing essential actions on the sheet, such as editing cell contents and adding and deleting rows and columns.

But don’t worry. I will show you two methods that you can use to unprotect your Excel sheet without a password and regain control over your data. 

Note: Before using the methods I will show you, create a backup of your original file. This step is crucial as it guarantees you have the original file to revert to if the copy you are working on becomes corrupted. 

Method #1: Remove Sheet Protection Code From XML File of the Sheet

Every worksheet in a workbook has a corresponding Extensible Markup Language (XML) file.

The XML file contains code corresponding to the sheet’s structure and data, including cell values, formulas, formatting, and other metadata, such as protection settings.

Removing the worksheet protection code from the sheet’s XML file allows you to unprotect the sheet without a password. 

Suppose you have a password-protected worksheet named ‘Annual Sales’ in a workbook named ‘Archived Sales Reports.xlsx’:

password protected sheet to unprotect in excel

If you try to modify the sheet by, for example, editing a cell, Excel displays a warning message box indicating that what you are trying to change is on a protected sheet.

Warning message when trying to unprotect a sheet

Suppose you attempt to unprotect the sheet by entering an incorrect password because you forgot the correct one. In that case, Excel will display a warning message indicating that the password you entered is incorrect.

Warning message when using the wrong password on unlock unprotect a sheet

You can unprotect the sheet without a password using the following steps:

  1. Select the original workbook with the password-protected sheet and press CTRL + C to copy it. Press CTRL + V to create a copy of the workbook.
Create a copy of the workbook

Note: If your files don’t show their extensions, you can make them visible using the step below:

  • On top of the File Explorer window, click the View tab and select the File name extensions option on the Show/hide group.
Click on file name extension
  1. Right-click the workbook you want to work with and click Rename on the shortcut menu that appears.
Rename the Excel File name

Alternatively, select the workbook and press F2 to enter edit mode.

  1. Replace the Excel file extension with the ZIP extension.
change the extension to zip

When you press Enter, Excel displays a warning message box indicating that changing the file extension might make the file unusable.  

Click on yes
  1. Click Yes to confirm you want to make the change.

Notice that the Excel file becomes a zipped folder with an icon of a zipped folder:

Excel file becomes a zipped folder

The zipped folder icon shown above may look different from yours, depending on the file compression software you use. My device’s file compression software is WinRAR.

  1. Double-click the zipped folder to open it. The components of your worksheet will appear as in the example shown below.
Open the zip folder
  1. Double-click the XL folder to open it.
Open the XL folder

Once you have opened the XL folder, you will see its files and subfolders.

Files and subfolders in the XL folder
  1. Double-click the Worksheets subfolder to open it. Notice the SHEET1.XML file.
All the worksheets are shown as XML

Note: The Worksheets subfolder contains only one XML file because our workbook has only one sheet. If it had many sheets, the subfolder would contain many XML  files corresponding to the sheets.

  1. Select the SHEET1.XML file and press CTRL + C to copy it. Click the Desktop and press CTRL +V to create a copy of the file on the Desktop.
Copy the sheet to desktop
  1. Right-click the SHEET1.XML file on the Desktop, hover over the Open with option on the shortcut menu, and click Notepad or any other text editor on the submenu.
Open the XML file in notepad or wordpad

The SHEET1.XML file opens in the text editor:

XML file open in the text editor
  1. If you are using Notepad, open the Format menu and choose the Word Wrap option so that the long lines of text automatically wrap to the following line and all text fits within the visible window.
choose the Word Wrap option
  1. Press CTRL + F to open the Find dialog box. In the Find What text box, enter the keyword  ‘Protect’ and click Find Next.
Search for the word protect in the Wordpad notepad

The Find command will take you to the first word that contains the keyword ‘Protect’ as shown below:

The cursor would take you to the found word in the notepad
  1. Carefully select everything from the opening angle bracket preceding the word ‘sheetProtection’ to the closing angle bracket of the sheet protection code as shown below:
select the part to delete to unprotect the sheet
  1. Press the Backspace key to remove the selection, press CTRL + S to save the changes, and close the text editor.
  2. Delete the SHEET1.XML file in the zipped folder, replace it with the one on the Desktop, and close the zipped folder.
  3. Return to the zipped folder and change the ZIP extension to the original Excel extension, in this case, XLSX.
Change dictionary extension from zip to xlsx

Click Yes on the dialog box that appears, and the zipped folder will become a regular Excel file.

The file reverts back to the Excel format

When you open the Excel file, you will notice that the target worksheet is no longer password-protected.

The worksheet is now no longer protected and has been unprotected

You now have the option to password-protect the sheet again with a password that you can remember.

Also read: VBA to Unprotect a Workbook

Method #2: Import the File into Google Sheets and Download it as an Excel File

Sometimes, you can unprotect an Excel sheet without a password by importing the host Excel file into Google Sheets and downloading it as an Excel file. 

Note: Some Excel features, such as VBA code, complex formulas and functions, and advanced pivot tables and charts, are not supported by Google Sheets and, therefore, may not be imported into Google Sheets or imported incorrectly. Consequently, you will need a different method if your Excel sheet contains such features.

Suppose you have the Excel sheet below that is protected with a password:

Worksheets protected with a password

You can use the steps below to import the file into Google Sheets and download it as an Excel file:

  1. Launch the Google Sheets program and open a blank spreadsheet.
  2. Type in your preferred name for the file.
Give the file a name
  1. Open the File menu and choose the Import option.
Click on Import
  1. On the Import File feature, click the Upload tab, click the Browse button, and navigate to where your Excel file is stored. 
Click on Upload
  1. Select the Excel file and click Open.
Select the Excel file you want to unprotect unlock
  1. On the Import file feature that appears, select the Replace spreadsheet option on the Import location drop-down and click the Import data button.
select the Replace spreadsheet option
  1. Once the file is imported, open the File menu, hover over the Download option, and click the Microsoft Excel (.xlsx) option on the submenu.
Download the file as Microsoft Excel file

When you open the downloaded Excel file, you will notice that the target worksheet is no longer password-protected:

The worksheet is now no longer protected in the downloaded Excel file

In this tutorial, I have shown you two ways to unprotect an Excel sheet without a password. I hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

2 thoughts on “Unprotect Excel Sheet Without Password”

Leave a Comment