Checkboxes are now available in Google Sheets!
They give you a visual way to toggle between boolean values (true and false) in a spreadsheet cell. This opens up all sorts of opportunities to make your Sheets more interactive.
Adding a checkbox
You’ll find checkboxes under the Insert menu:
At the moment they have a NEW label next to them, but that disappears after a while.
When you add a checkbox, it will show up in the cell or range of cells that you have highlighted, and it will be unchecked. If you look in the formula bar, the cell has a value of FALSE.
This means you can link to this checkbox cell with any formula, for example an IF statement, and it will behave as a FALSE value (when it’s unchecked).
When you click on the checkbox itself, it will become checked (shown by a tick mark and grey background) and the cell value will change to TRUE. Again, you can use this in your formulas.
Advanced Checkbox Options
You can also add checkboxes through the Data Validation menu.
Right click a cell, choose Data Validation from the menu and this brings up the Data Validation popup.
Under the Criteria option, you’ll find Checkboxes as the last choice.
You have more options when you insert a checkbox this way, rather than through the menu. You can customize the values to be something other than TRUE and FALSE if you wish. You can also add validation, so that the cell rejects any values that do not equal the values you specify for Checked and Unchecked.
Three ideas for using checkboxes
1. Create dynamic charts
I’ve previously shown one method of creating dynamic charts using Data Validation, which works great but has the principal drawback that you can’t select multiple items from the drop-down menu.
Checkboxes can solve this for you and allow the viewer to pick and choose all or any of the data series to show in a chart, as shown in this Gif:
Each row of data in this table represents a country and its GDP over a 10-year period, from 2006 – 2015.
This is how the technique works under the hood (click to enlarge):
I’ve added a checkbox next to each row in the table, in column L in this case (shown by the 1 in the image above). That means for each row, I can check or uncheck a box, to indicate whether I want to select this row or not.
The table now has a column of TRUE/FALSE values, where TRUE indicates that I want to include these rows and FALSE indicates that I do not want to see these rows.
The data is passed into a second, dynamic table, based on whether the specific row (country) has been selected, from this TRUE/FALSE column. There are various ways you could do this, and I’ve chosen the Query formula in this example:
=QUERY( A1:L11 , "where L = TRUE" , 1 )
2. Create a To Do list app
With the launch of checkboxes we can now create a To-Do app!
This simple example is easier to understand than the dynamic chart example above.
It consists of a list of tasks in column A and a list of checkboxes adjacent to them, in column B. I’ve added a conditional formatting custom formula rule to shade rows red when the checkbox is checked, to show that task is done.
The formula for the custom conditional formatting rule is:
= $B2 = TRUE
The key point to note is the $ before the B only of the B2 reference. That ensures that only column B is considered for the test (TRUE or FALSE) but the formatting is applied to the whole row.
The To-Do app looks like this:
3. Show/hide hints and solutions to a test
Here’s a final example which may have applications in the Ed-Tech world.
Suppose you create worksheets using Sheets that students work in, and use to answer questions.
Using checkboxes, you could add hidden solutions or hints, that only show when the checkbox is toggled.
Here’s a simple example to illustrate this:
To show a hint or solution, you can use an IF formula and the TRUE/FALSE value of the checkbox to show/hide the solution.
For example the solution for 35% of 40 could be shown by this formula to show the answer:
=IF( E9 , 0.35 * 40 , "" )
And if you wanted to show the formula itself as a hint, you could wrap it in quotes to turn it into a string value:
=IF( E9 , "= 0.35 * 40" , "" )
Have you used checkboxes in your Google Sheets?
Feel free to share your ideas in the comments!
Read more about checkboxes in the Google documentation.