Using checkboxes on sheets

I’ve seen a checkbox used in a sheet, how do they work?

By Neale Blackwood

Excel has a number of controls that can be placed on a spreadsheet. The standard controls are shown on the Forms toolbar. These controls work by linking to a cell on the sheet. That cell is then used to perform a calculation. Controls are typically used to make the sheet easier and quicker to use.

Checkboxes are used for occasions when something is either selected or not. It is not used to select between three or more different options. A checkbox result is either TRUE or FALSE.

To see how a checkbox can be used, create the simple table below. Make sure you increase the height of row 2 to make inserting the checkbox easier.

Right click the toolbar and select the Forms toolbar. On the Forms toolbar click the ticked checkbox icon. With your mouse draw a small square in the area of cell D2.

The checkbox has text which can be used to describe it. In this case we will delete the text. Right click the checkbox and select Edit Text. Hold down the Delete key until all the text is gone. Click outside the checkbox, this just leaves the checkbox visible.

Right click the checkbox and select Format Control, click the Control tab, move the dialog box if necessary to see the table on the sheet, click inside the Cell link box and click cell D2. Click OK.

When you click the checkbox it will now change the contents of cell D4 to TRUE or FALSE. A ticked checkbox displays TRUE and an unticked checkbox displays FALSE. In Excel TRUE equals 1 and FALSE equals 0. Hence, you can use cell D2 in calculations. In cell E2 type the following formula =B2*(1-(C2*D2))

The C2*D2 part of the formula will calculate as either 10 % or 0% as 10% is multiplied by 1 or 0. By ticking or unticking the checkbox you can display the discounted or standard price.