To reduce the risk of user error, you can protect your worksheets so that users can only change input cells and perform certain other restricted actions such as select cells and use filters. To enable this is a two-step process: (1) protect cells and (2) turn protection on.
1. PROTECT CELLS
All cells where changes will be prevented (i.e., changes will not be allowed) must be formatted as ‘locked’ and all cells where changes will be allowed (input cells and drop-downs) must be formatted as not ‘locked’. To see the status of a given cell, select it, then format cells (shortcut Ctrl 1) and select the ‘protection’ tab, as shown in the screenshot below.
Locked: By default, all cells in a new workbook are locked. That means you need to unlock all input cells, including any drop-downs. It is best to do this as you develop your spreadsheet since unlocking input cells later is a time-consuming, error-prone process – you are bound to miss some and, consistent with Murphy’s law, they will be exactly those cells the user wants to change.
Hidden: The protection tab offers an additional option, ‘hidden’. This allows you to hide the cell formula in the formula bar when the cell is selected. In the interests of transparency and understanding a spreadsheet, I do not recommend this option.
Bonus tip #1: Use cell styles to easily remove protection for input cells
The easiest approach to unlock input cells is to use cell styles. Format an input cell with "Locked" turn off. Then go to the Home ribbon and click on the down arrow at the end of the the Styles section.
Select "New Cell Style..." and save the format as e.g. "01 Inputs" (numbering ensures your cell styles appear in your desired order).
Now when you want to format an input or drop-down cell in your model, simply select the cell(s) and click on the relevant style name in the styles box on the home ribbon. The cells are then given the appropriate colour and are automatically unlocked… simple and effective!
I typically use one cell style for inputs and another for drop-downs with a different colour, both of which are unlocked, i.e., the ‘locked’ option box is not ticked.
See Microsoft support for more details on cell styles.
Bonus tip #2: Use background error checking to identify missing protection
You can turn on background error checking for ‘Unlocked cells containing formulas’ under ‘File’, ‘Options’, ‘Formulas’, ‘Error checking rules’. Excel then automatically marks any unprotected cells with a green corner, and you can then correct the cell formatting, as explained above.
2. TURN PROTECTION ON
This step is hinted at by Excel in the note underneath the protection settings. ‘Locking cells … has no effect until you protect the worksheet.’ This is simple enough to do.
Select the relevant worksheet, navigate to the Review ribbon, click ‘Protect Sheet’.
Then select what you want to allow. ‘Select locked cells’ and ‘select unlocked cells’ are allowed by default – generally sensible – but can be deselected. All other options are deselected by default but you can tick an option box in each case. One I generally allow is ‘Use AutoFilter’, especially for data analysis files or in models with large input sheets.
The final step is to input a password (if desired) and you are done. Obviously, you must remember the password, which can become a problem if you have lots of protected spreadsheets in use. You can therefore consider protecting the worksheet without a password to avoid problems.
Repeat for all further sheets in the model that you wish to protect. If you use a password, I strongly recommend using the same password for all protected worksheets in a single workbook otherwise the pain of password management or the risk of forgetting a password becomes too great.
I am afraid there is no option to turn the protection on (or off) for all worksheets at once, so this task quickly becomes time-consuming, unless you use a macro…
Macro to the rescue!
I use a macro to protect all worksheets in a workbook and another to unprotect them all. To get this, please download my free bonus material for the book "Avoid Excel Horror Stories" at https://www.how2excel.com/en/downloads-en. The VBA macro code is in the main file ‘how2excel – example calculations and macros’.
The ‘protect sheets’ macro also solves another problem with protected sheets: by default, you cannot open and close row and column groupings on protected sheets (an Excel bug), which can be frustrating. As well as protecting sheets, the macro provided specifically enables the opening and closing of row and column groupings.
Extra protection: workbook structure
On the Review tab next to ‘Protect Sheet’ there is an icon for ‘Protect Workbook’. This enables you to prevent users changing the structure of a spreadsheet (e.g., moving, renaming, deleting or adding worksheets), which may be worth considering.
In the suitably protected worksheet shown above, users can only enter and change values in the grey input cells. If they try to change any other cells, they get an error message like this.