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, as follows.
1. Ensure non-input cells are locked and input cells are unlocked
🔒 Non-input cells such as formulas and labels
By default, all cells have the status “locked” (protected). If you want to check, select all relevant cells, select Format Cells (shortcut Ctrl 1), select the “Protections” tab and check there is a tick next to the “Locked” property.
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.
🔓 Input cells
You must unlock the input cells (including those with data validation).
First, select the input cells. If you are not sure which cells are inputs, use GoTo (shortcut F5). Select “special” and “constants”, “numbers”, then ok. All input cells with content are now selected!
Then select Format Cells (shortcut Ctrl 1), select the “Protections” tab and remove the tick next to the “Locked” property.
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 for each worksheet
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.
🔓🔐 If you remove worksheet protection to make a change, you must re-protect it afterwards!
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 my book "Avoid Excel Horror Stories". 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.
Bonus tip: Add password protection to the macros to prevent unauthorized users finding the password in the macro code! In Visual Basic editor, right-click macros, select VBAProject Properties, Protection tab, Lock project for viewing, enter password twice.
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.
RESULT
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.
