Sunday, August 07, 2011

Unprotect a password protected Excel 2007 or Excel 2010 worksheet

Just wanted to share how easy it is to unprotect or unlock a password protected Excel 2007 or Excel 2010 worksheet.  It can come in handy when you need to edit your password protected worksheet but has forgotten the password.

Remember that Office 2007/2010 is using Open XML format which is a zipped XML format.  To unprotect the Excel file without knowing the password, just follow the steps below.

Rename the file extension from xlsx to zip.

Use a zip tool (e.g. 7-zip, Winzip) to open the file.  Go into the xl folder.

image

  Then go into the worksheets folder.

image

Right click on the worksheet you want to unprotect and select edit.

image

Look for the sheetProtection tag.  Note that the password has been hash so it is not the real password.  To unprotect the worksheet, you can choose to delete the entire tag which will make the worksheet unprotected or you can set an empty password.

Protected with a password

<sheetProtection password="96E1" sheet="1" objects="1" scenarios="1"/>

Protected with empty password

<sheetProtection password="" sheet="1" objects="1" scenarios="1"/>

If you choose to set an empty password, the worksheet is still protected but you can unprotect it in Excel without entering any password.

After making the change, save and close the editor (e.g. notepad).  When prompted whether to update the archive, select “Yes”.

Rename the file extension from zip back to xlsx.  Open the Excel file and you can edit the previously protected worksheet.

Here is a video showing how to do it. Enjoy!

UPDATE: If you find difficulty doing it, you can use the tool in my entry http://deinfotech.blogspot.com/2011/12/simple-tool-to-unprotect-password.html.

26 comments:

prince said...

hello buddy i didn't get any folder like Xl...after rename that file i can't see any folder...there's 3 files one is worksheet but while m open that i see some boxes...square file and all...i did't get any xml code...can u help me

prince said...

 ऀ Ā 倀 ༀ 堀 ᜀ 氀 ଀ 琀 က 簀 ጀ 萀 ᘀ 谀 ഀ 鐀 ఀ 봀 Ȁ  Ḁ ऀ 䝯潤睩汬e琳̀ ꀊऀ଀ ଀    ऀ Ā 倀 ༀ 堀 ᜀ 氀 ଀ 琀 က 簀 ጀ 萀 ᘀ 谀 ഀ 鐀 ఀ 봀 Ȁ  Ḁ ऀ 䝯潤睩汬e琳̀ ꀊऀ଀ ଀    ऀ Ā 倀 ༀ 堀 ᜀ 氀 ଀ 琀 က 簀 ጀ 萀 ᘀ 谀 ഀ 鐀 ఀ 봀 Ȁ  Ḁ ऀ 䝯潤睩汬e琳̀ ꀊऀ଀ ଀


i get this type files...please gave suggation....what to do..3 file names are...
1) [5]SummaryInformation
2) Workbook
3) [5]DocumentSummaryInformation


same thing in this 3 files

Alex Siow said...

From your description, the Excel is pre 2007 version. You need to convert it to 2007 or 2010 version before you can use this method to remove the password.

If you have Excel 2007 or 2010, the easiest way to convert the file is to open the file using Excel 2007/2010. Then do a Save As and select "Excel Workbook (*.xlsx)" for the save as type.

If you do not have Excel 2007/2010, you need Microsoft Office Compatibility Pack.

prince said...

ok...i'll try that if it works then inform u...thnx for ur venerable support...:)

prince said...

dude it's not working after MS Office 2010 installing i'll try this...but not working please dude my all files are more important help me....

Alex Siow said...

Did you open the file using your Excel 2010 and do a save as to save it as the 2010 version. The title bar should not show compatibility mode if the file has been saved as 2010.

V Shankar said...

After opening the xl/worksheet, I don't see edit for locating the password...

Alex Siow said...

Hi V Shankar,

What do you see after opening up the worksheet?

Ryan said...

Thanks for this ...cheers

Ryan said...
This comment has been removed by the author.
Alex Siow said...

You are most welcome Ryan.

Anonymous said...

Nice post.
folder protect

Chrissy said...

Tried this one, but it won't help if your file is encrypted. I found no free ways to unlock a document which requires a password after opening and had to pay to an online service called www.password-find.com to decrypt my file. They did help me for a small fee, so I recommend it to everyone who can't open a password protected speadsheet.

Unknown said...

for password on the main file simply save it to google docs and then download it. Password protection gone.

tdm said...

google docs will not unlock 2010 password encrypted file anymore. any other suggestions?

Silvernickel said...

Make sure to use 7-zip since WinZip will not resave the modified file correctly. Otherwise, it worked great. Thanks.

GodddaaPrriinnttt said...

it works. for sure. i was saved.

Unknown said...

Doesn't work if file is initially xls. Even when opened and saved as xlsx it doesnt work, files are encrypted. Dont use winzip either.

Unknown said...

This worked great for me ! Thanks !

Some comments:
I'm running Office 2010 on windows 7.

The file was originally created in Office 2003 (.xls) so I opened it in 2010 and did a Save As to the 2010 version (.xlsx)

I downloaded and installed 7-ZIP.

I did NOT have to rename the file to .ZIP. Just right-click on the Excel file and select 7-ZIP and Open Archive.

From that point on I simply followed the instructions and successfully removed the password protection.

(Hint: the end of the string I had to delete was slightly different than the example. There was a space at the end of mine and no HTML formatting tags. I just deleted up to the end of the string and left the space.)

Anonymous said...

Yeah, this was just what I was looking for. Thank you for the help - it worked great!

Now we'll see if my co-worker finds some other way to waste my afternoon :-)

DGW906 said...

Used your technique to get rid of the password on a spreadsheet. But only after I had removed the password protection on the whole file. Before I removed the file protection password I got three entries when opening the zip file. Maybe helpful to update your instructions to make it clear the file cannot be opened as described if it is password protected itself.

Unknown said...

Really Useful! Thank's!

Manohar said...

WOW..WOW..WOW...
IT'S UNBELIEVABLE...
IT'S 100% WORKING WITH MY XLSX FILE...
THANK U.

NOMAN said...

You are great buddy........ Thanks
i wanna ask one more thing... how can we remove workbook password by this method..?

Unknown said...

I open the xml file but can't see the sheetprotection password.It has sheetpr.I try to remove it but the excel file is still blocked. Can anyody help me? Thank you very much.

Unknown said...

> Chrissy said...
Tried this one, but it won't help if your file is encrypted. I found no free ways to unlock a document which requires a password after opening and had to pay to an online service called www.password-find.com to decrypt my file. They did help me for a small fee, so I recommend it to everyone who can't open a password protected spreadsheet.

Thank you kind sir, I had encrypted file with some work information my former colleague had left and we couldn't open it, password-find relived our weeks of grief in seconds.