Check boxes make selections easy. Excel has a number of useful controls that can be added to sheets to improve the user interface. These include check boxes (tick boxes), option buttons and spinner controls.
These controls allow you to select options by clicking the control rather than entering a value in a cell.
Turning off and on certain calculations
The check box lets you easily turn off and on calculations by ticking or un-ticking a box. Let me use a simple example to demonstrate. See image below.
The example file can be downloaded at the bottom of the blog.
There are two calculations to perform.
One to adjust an amount by a percentage (input cell C2), the other to apply inflation (input cell C3) to the adjusted value. We need the ability to easily turn off or on, either or both of these calculations.
One way is to zero the input cells, but then you have to remember what the values were if you want to re-instate them.
Using check boxes allows us to leave the input cells alone. A ticked check box means the calculation is done an un-ticked box means the calculation is zero.
Check boxes need to be linked to a cell on a sheet. Cell E2 above is linked to the top checkbox and cell E3 is linked to the bottom checkbox. Linked cells display either TRUE or FALSE. Ticked = TRUE un-ticked =FALSE.
In Excel TRUE = 1 and FALSE = 0. Multiplying by TRUE is the same as multiplying by 1. Multiplying by FALSE will zero any calculation. See the image below.
The formulas in Column C are shown in the cell to the right in Column D.
Cell C6 calculates the adjustment amount by multiplying C5 by the adjustment factor C2 and then multiplying by cell E3. Cell E3 is linked to the check box and will determine if the calculation is done.
Cell C8 similarly calculates the inflation and then multiplies by cell E3 which controls whether the calculation is done.
This is a simple example to demonstrate the technique, but it can be applied to budgeting and forecasting models to change thousands of calculations with a single click.
In practice the linked cell is typically underneath the control and can be invisible to the user by applying the custom format ;;; (three semi-colons) which stops values displaying or printing.
Also it is best practice to name the linked cell with a pre-fix that describes the control it is linked to eg cell E2 could be named chkbxAdjustment. This name would be used in subsequent formula to make it obvious that the calculation is affected by a control. See image below for an example using the range names.
How to insert and use a Check Box
The method of selecting a control to insert varies with the Excel version. But the technique of using the control is identical in all versions.
Selecting the Control
Excel 2003 and earlier versions – right click the toolbar and display the Forms toolbar. This toolbar has all the tools click the check box and icon and “draw” it on the sheet using the mouse.
Excel 2007 – click round office button (top left of screen) Click the Excel options click Show Developer tab in the Ribbon click OK.
Excel 2010 – Right click the Ribbon and choose Customize Ribbon. On the right tick the Developer Ribbon option and click OK.
Excel 2007 + 2010 – Click the Developer tab and click the Insert option to insert a check box, use the Form Controls checkbox icon as it is easier to use and draw the check box on the sheet. The Active X controls (bottom) are more complex.
Right click the check box and select Format Control. In the Control tab click in the Cell link box and then click a cell on the sheet and click OK (see image below).
Right click the check box and use Edit Text to amend the text displayed. You’re finished.
This technique can also be used with Conditional Formatting to apply formats based on check boxes.
Future blog posts will cover Option Buttons and Spinner controls.