Excel Tip of the Week #118 - Adding and removing passwords

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.

No Data
Comment Children
No Data