Removing Excel Passwords

One useful feature in Excel spreadsheets is the ability to protect all of the cells in the sheet other than those that you want to be able to enter values into. This allows you to set up a series of complex formulae in the sheet and not have the formulae accidentally overwritten by someone typing in the wrong field. What is far less useful is that Excel requires that you enter a password when protecting the cells and that you then need to re-enter the password if you need to unlock the sheet to change the formulae. I generally place the password itself in a cell in the spreadsheet as a reminder of what it is but that has a disadvantage where you want to be able to print out the sheet of including the password on the printed page. It is really easy to forget the password (that you shouldn't need to set in the first place) when you need to change the formulae in a protected sheet.

If yuo do a search on the web for ways to unlock a protected worksheet there are lots of options that come up regarding macros to run to do this. These are all leftovers from the old days when Excel used its own proprietary format for storing the data (XLS) instead of using XML to do it (XLSX). None of them work for modern spreadsheets.

Because Excel now uses XML to store the data we can actually remove the password protection directly ourselves by editing the XML directly. I suggest you work on a copy of the spreadsheet because if you make a mistake in your edit you can break the entire spreadsheet.

To be able to edit the XML we must first gain access to it. o do this we first need to change the file extension from XLSX to ZIP. This will then allow us access to all of the XML files that the spreadsheet contains by using and program that understands zip files to unpack the spreadsheet into its individual files.

To access the actual worksheets you need to first go into the xl folder and then into the worksheets folder within that. There you will find a number of XML files - one for each sheet.

Open the individual XML files in an appropriate text editor. If you don't have one that specifically handles XML files then a plain text one such as notepad will do. You need to use your editor's 'Find' facility to find the reference to "sheetProtection". There should be exactly one such reference per XML file. To remove the protection from that sheet you need to delete that XML tag from the file (that's everything from the < preceding to the /> following that text - inclusive). The encrypted version of the password will be a part of the tag that you are deleting so there is no need to work out what password will unlock the sheet as with the password code removed the sheet will no longer be protected. Save the file and add the updated file back into the ZIP file in place of the original. Repeat for any additional sheets where you need to remove the password.

The final step is to rename the extension from ZIP back to XLSX. You should now be able to open the spreadsheet in Excel and have access to all the cells without any cells being locked.

go to top

FaceBook Follow
Twitter Follow