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.
There may be a better way but the protect workbook structure password; and a worksheet password may work for you.
Set up a worksheet with the data you do not want general users to see. Hide the worksheet and setup a structured workbook password.
On a 'general access' worksheet setup the reporting formulae you need, such as sumifs etc. - if necessary add drop down options etc. via 'Data Validation/Data Ribbon' to allow selection of departments - making sure selections do not allow too much specificity so that only 1 person can be selected - bald-headed males aged 51 in finance, for example!
On the general access worksheet protect all cells, possible with exception of data validation drop down - you will need to check this cell cannot be overwritten and data validation removed.
During the protection, and format of all cells with formulae on them, hide the cell contents so that the name of the hidden worksheet remains unknown - if known other workbooks could access the worksheet values.
This is an off-the-cuff suggestion. The areas you need you check are:
- data validation cell can be protected and yet allow dropdown selection
- external workbooks cannot access and determine the hidden worksheet
The above I feel will give reasonable protection but will not be 100% - macros on other worbooks will be able to get around much of the above. This may need enhancing by 'very-hiding' your hidden worksheet, possibly adding macro formulae and locking down the VBA etc.
As always, thanks for the tips, David!
One problem I often encounter is trying to protect confidential source data while allowing users to manipulate the output. For example, when working with payroll files, end users need to be able to see the total costs assigned to each area (calculated using SUMIF or similar), but should not be able to view individuals' salaries.
The workaround is to paste the output values into a separate document to distribute, but that creates its own problems around version control and identifying errors. Does anyone know of a better solution?