The ICAEW Blogs & Forums will no longer be updated with new posts. Your community announcements and articles will now be hosted on icaew.com under their respective community areas. This site and its contents will be closed and made available in an archive at the end of October.
Hello and welcome back to the Excel Tip of the Week. This week, we have a Creator post in which we are discussing how to add and remove passwords from Excel workbooks. This touches on topics originally covered back in TOTW #20. Overview of Excel passwords Excel lets you lock access and/or edit rights at various stages. You can prevent someone from opening a file, or allow them to open it but not make changes. You can protect a workbook or workbook's structure to prevent unauthorised inputs / changes, and can use a password to lock that option in. If your file has some VBA code in it (for example, a macro), then you can also password-protect that code. These options are all good ways of preventing untoward or malicious changes to your Excel file. However, no Excel password is truly secure, and they can all be removed using brute force attacks. Sheet passwords, and any password on older Excel versions, are particularly easy to remove. Truly vital data should be protected by storing the file within an appropriately secured workplace network, and not distributing out via email. What you can protect with a password Open the file This will prevent people opening the file without entering the password. This option is found under the 'Save as' dialogue: Modify the file This will allow anyone to open the file, but prevent any changes being made unless the password is entered. This is a separate option in the same menu shown above - you can set passwords for each item independently. Workbook password This will prevent changes to the entire workbook (similar to the 'modify' option above) - but is an option within Excel rather than within the file-saving dialogue. From the Review tab: This option is more customisable, as you can also e.g. allow changes but prevent the insertion of new rows/columns or any changes in the window structure. Shared workbook password Useful for shared workbooks stored on communal drives, this will prevent users deactivating track changes, making sure that you can always monitor what changes each user has made. From the Review tab: Worksheet password This will lock just the cells of the current worksheet. It's quite customisable as to which cells are and aren't affected, and what exactly user can and cannot do whilst the worksheet is protected. Again from the Review tab: VBA project password This will prevent users from accessing or modifying VBA code in your workbook. From within the VBA editor, select the project in question, then from the Tools menu select VBAProject Properties: Note that, unlike the other options listed here, you will need to fully close & reopen Excel for the lock to take effect. Final notes There can be such a thing as password overuse. Especially for internal files, excessive passwording can be obstructive, or time-wasting. Passwordless protection of files is often enough to prevent misuse. Any passwords that are used should be stored somewhere in case of emergency or discontinuity of staff. For example, a senior person could be given an emergency password store file that is itself passworded, so that passwords could be recovered in an emergency. Previous post - Revisiting INDEX MATCH TOTW index Next post - Revisiting data tables This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.
Some time ago I found an excel workbook I had password protected could be opened in Google Drive without the password. I've been sceptical about security since then. Better to use TrueCrypt or something like it.
If only there were a way to embed an iPhone into Excel!
"These options are all good ways of preventing untoward or malicious changes to your Excel file. However, no Excel password is truly secure, and they can all be removed using brute force attacks."
I think this needs putting much more strongly - the only well encrypted passwords are the workbook open password and the VBA module password.
I would not recommend treating any other password as secure except in the fairly trivial sense that you might be mildly inconveniencing users just enough to discourage them from even trying to get in to the workbook/sheet/area you don't want them to access.
In Excel 2010 and earlier, sheet passwords and workbook structure passwords can be cracked in seconds with a simple bit of VBA code that is freely available on the internet.
If you know your way around a hex editor other passwords can be cracked too, like the VBA module password.
Microsoft has no excuse for these flaws but they exist - so your readers should be aware of it.
Excel 2013 is much improved but I very much doubt it is unhackable.
The Password protection in excel is very weak and should not be relied upon. One of my colleague cracked a 12 digit alpha numeric password containing uppercase and special character in less than one minute using a vb code from google search.
Further in case of file that can be opened (even though password protected for modification) can be unprotected using "Save As" option.
My advice is not to rely too much on password protection if the data is really very confidential.
Do note that this is still not foolproof. For example, I could write an array formula with the range names that I could then view with the Formula Auditor in a way that would let me peruse the data:
Ultimately, if the data is in the spreadsheet that the user has, it will be possible for a suitably determined expert to get access to it. If you only want them to have summary data, then only providing summary data is the safest way (for example by paste-valuing a PivotTable summary to a fresh workbook).