Default Keys: Ctrl + Shift + Alt + F5
Ever inherited an Excel model with password-protected sheets from a person who is no longer with us? And I don’t mean that they’re dead, just that they left the company and took their password with them — like a spiteful child, leaving the ball-field and taking their ball home with them… Well it turns out that unlocking those sheets is really not a hard thing to do, although it is a bit tedious and convoluted.
The gist of the method to unlocking sheets is that you can change an Excel file to a Zip file, and then open each worksheets’ guts as a text file, removing the line of text that lets Excel know that the sheet is locked. Once complete, you change the Zip file back to an Excel file, and Excel will no longer think any of your sheets are password protected. If you want to see the full details on how to do this yourself, just google “excel remove sheetprotection xml”; and yes, I meant sheetprotection to be one word. That search will bring up several great pages for the detailed process. However! If you’re like me, and would rather have the unlocking work done for you, read on my friend, I’ve got you covered. I built the Password Remover Nutility to do just that. It will automatically remove all worksheet passwords in any given workbook.
This Nutility requires you to have the Nutilities Excel add-in installed in order to use it. If you don’t know what Nutilities is, or where to get it, please start at my web-page here: http://iwishexcel.com . The add-in is completely free to use.
I think it is important to note here, this article is only referring to removing worksheets’ password protection. If the workbook you are trying to open requires a password just to open it, that level of protection is pretty safe, and this process will not work on it.
Instructions and Example:
Using the Nutility is easy. Open the workbook containing the sheets you need unlocked, and hit CTRL + Shift + Alt + F5. You will be met with the below popup. It lets you know that you will need to pick a folder where the Nutility can place an unlocked copy of your file, and gives you a couple other important tips for using the Nutility.
The next step is to pick the folder on your computer where the Nutility can do some temporary work with the XML files, and then create a new unlocked copy of your workbook there. It is important that you pick a folder on your local machine, and not on a shared drive.
In this example I chose a folder called “RandomFolder” on my desktop. After selecting the folder and hitting OK, the Nutility does all of its work within that folder. If the Nutility completes successfully, you will be met with the below popup. This Nutility takes ~30 seconds per worksheet in your workbook. So if you have 5 sheets, I would give it 2.5min before thinking Excel has frozen.
At this point the unlocked version of your file is available in the folder you designated. Enjoy!
Things To Be Aware Of
This Nutility will remove passwords when working in Excel Versions 2007+ (2007, 2010, 2013, 2016…) excluding Mac versions. It works on any Excel file-type (.xls, .xlsx, .xlsm, .xlsb…) but the output-unprotected file will always be .xlsm — feel free to save it as any file-type you want from there.
As this Nutility is resource intensive, you should save/close all other open items before running — crashes are very possible.
Also, when picking a folder for this Nutility to work with, pick one local to your computer! At one point the Nutility takes a guess at how long a process will take before continuing on. If you pick any shared-drive folder that responds slowly, there’s a strong possibility the Nutility will not guess/allot enough processing time and will error out.
Depending on the size of the sheets in your workbook, the Nutility can take between 10-30 seconds to process per sheet — so for 16 sheets that’s ~8min. Give the Nutility at least the 30 seconds per sheet (including all hidden sheets) before assuming it did not work.
The Nutility needs to be moving parts of the excel file around your computer (only within the folder you pointed it at) — so do not use the mouse or keyboard while it’s working.
If you need a password to open the workbook in the first place, this Nutility does not do that. It also does not unprotect VBA Projects.
It is assumed that the user has the rights to the all parts of the workbook they are working with — the user is responsible for complying with all applicable laws when using this Nutility.