Everyday we have to remember hundreds of passwords, both in our personal and professional lives. And it's only human to forget some of them every now and then, as long as we know how to retrieve those passwords or reset them. Resetting passwords for a web service like Gmail, or Facebook is easy. But what about a software, like Excel.
Excel has multi-level protection for it's users. You can add a password to protect the Workbook, Worksheets, you can even protect the VBA code inside the Excel Workbook with a password. If you forget one of these passwords, there is not normal way (like Gmail, or Facebook) to reset those password. Here we will discuss how to remove passwords from an Excel Workbook or Worksheet.
Method 1
This is a full-proof method that would work on any file type, including macro and add-ins. You will not use any data using this method.
Here is where you can find out if your Excel Workbook or Worksheet is password protected:
You can have either or both of them locked like above. The process to remove both of these passwords are similar:
1. Close the Excel file
2. Rename the Excel file and add a ".zip" extension in the end of the original file name (do not delete the existing extension like .xlsx, .xls, .xlsm etc.) CLick "Yes" to confirm
3. The Excel file will get converted into a ZIP file automatically because of step 2. Open the ZIP file by double-clicking on it. Inside the ZIP file, you'll see many folders and files. Now, if your Excel Workbook is protected, or if both the Workbook and Worksheet is protected then follow the next step. Otherwise, if only your Excel Worksheet is protected, jump to step 10
5. Inside the ZIP file there would be a folder called "xl". Go inside that folder and search for a file called "workbook.xml"
6. Copy/Paste that file on your Desktop and open it using Notepad
7. Now try searching for a similar text as below inside the .xml file, this will always start with a "workbookProtection" tag
8. Remove that entire highlighted piece of code and save the file
9. Copy/Paste that updated file on your desktop back to the ZIP file where you found it, confirm to overwrite
10. Now, inside the "xl" folder you would find another folder named "worksheets". Go inside this folder and search for a file name "sheet1.xml" (if your Excel has multiple sheets, use the corresponding sheet number which you would like to unlock the password for)
11. Copy/Paste that file on your Desktop and open it using Notepad
12. Now try searching for a similar text as below inside the .xml file, this will always start with a "sheetProtection" tag
13. Remove that entire highlighted piece of code and save the file
14. Copy/Paste that updated file on your desktop back to the ZIP file where you found it, confirm to overwrite
15. Now close the ZIP file and remove the ".zip" extension that you had added earlier
Now if you open the Excel file, you'll see that the Workbook/Worksheet passwords have been removed.
Method 2
This method is way easy to use, but you would lose all your macro/add-in codes, named ranges etc. However, if you are not worried about those then this is the quickest way to unlock your Excel Workbook or Worksheet. This method would remove both passwords at the same time.
1. Open any webgrowser and go to Google Sheets
2. Upload your locked Excel file on Google Sheets
3. Download the Google Sheet as .xlsx file
Voila! Both the passwords have been removed! Wasn't that quick!
