Check boxes are a great way of making your spreadsheet more interactive. Most people know what a checkbox is and what it does, so when people see a checkbox in your spreadsheet, they will instantly know how to use it.
This tutorial works in Excel 2016, 2013, and 2010.
Inserting a checkbox is as easy as pie, but you’ll need to activate the Developer menu in order to do so.
Activate the Developer Tab
To activate the developer tab:
- Click on File -> Options.
- Select Customize ribbon on the left.
- In the list on the right hand side, make sure the Developer box is ticked, and click OK.
There should now be a new Developer tab visible in the Ribbon.
For this example, we will build a prospective guest list for a wedding where we can indicate which guests are coming or not. The list will contain a series of names with check boxes next to each one. Based on the number of boxes ticked, the spreadsheet will be able to give us a total number of guests that will be attending.
I’ve already created a list of names, so let’s go ahead and insert a checkbox.
Insert a Checkbox in Excel
A checkbox is a type of form control, and so it is found in the Controls group of the Developer tab.
To insert a checkbox next to the first person’s name:
- Click on Insert in the Controls group on the Developer tab.
- Click on the check box icon. The cursor now turns into a cross-hair.
- Point and click in the cell next to the first name to place the checkbox.
Customize the Checkbox
By default, check boxes comes with a string of text next to them, such as Check box 1. We can get rid of this text by right-clicking on the checkbox and selecting Edit Text. Then delete or edit the text as you wish.
You can see some more formatting options when you right click on the checkbox and select Format Control.
Here you can select the default value of the checkbox – either Checked, Unchecked, or Mixed. You can also tick the 3D shading box to give the checkbox a more 3-dimensional look.
Click OK for these changes to take effect.
Copy the Checkbox to Other Cells
When you copy and paste a cell on which a checkbox was placed, a second checkbox will be created in the destination cell.
So let’s do that for all the names in our list. Make sure the checkbox is neatly centered in the cell and then grab and drag the fill handle downwards. Release the handle at the last name.
Connect the Checkbox to a Cell
The best things about a checkbox is the fact that you can have its value (or state) reflect in a cell. This then allows you to reference that cell in a formula and control other cells by ticking or un-ticking the box.
In our example, we’ll connect each checkbox to the cell on its right.
To connect a checkbox to a cell:
- Right click on the checkbox and select Format Control.
- In the Control tab, place your cursor in the Cell link box.
- Click on the cell you wish to connect to the checkbox, and press OK.
Now, when the box is ticked, the value TRUE will be displayed in the cell. If the box is un-ticked, the value FALSE will be displayed. The cell changes as you continue to tick/un-tick the box.
Unfortunately linked cells do not copy down with check boxes, so you’ll need to do this for every checkbox you create.
It is a good idea to the connect the cell directly to the right of the one containing the checkbox. This enables you to know exactly which cell is connected to which box. In addition, if all the “reflecting” cells are in the same column, you can later hide that column to hide the TRUE/FALSE values.
Use the Checkbox Value in a Formula
Now that the state of each checkbox is being reflected in a cell, we can use that cell in a formula to do all kinds of amazing things.
For instance, we can now count the number of TRUE values in column D to see an exact count of the number of guests we can expect at the wedding.
Type the following formula into cell C17:
This will count all cells in column D that contain the value TRUE.
Finally, to hide the column with the connected cells, simply right click on the column header and select Hide.
There are so many useful things you can do with a checkbox. The possibilities are endless! See this video which shows you how to show/hide various series in a chart using tick boxes.
Do you have any more uses for the checkbox? Please let me know in the comments below!