The Google Sheets checkbox is an interactive widget in a cell that can be checked or unchecked.
A checkbox in Google Sheets has a value TRUE when checked and FALSE when unchecked.
Checkboxes open up all sorts of opportunities to make your Sheets more interactive, which you’ll see further down this post. All the examples are also available in the free template linked at the bottom of this post.
How To Insert A Checkbox In Google Sheets
Method 1: Checkboxes are added via the Insert menu: Insert > Checkbox
Method 2: Alternatively, you can add checkboxes via Data Validation.
Go to the menu: Data > Data Validation
And then select Checkbox from the criteria drop down menu:
Google Sheets Checkbox Notes
Google Sheets Checkbox TRUE status
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.
This means you can link to this checkbox cell with any formula, for example, an IF statement, and it will behave as a TRUE value (when it’s checked).
Google Sheets Checkbox FALSE status
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. Again, you can use this in your formulas.
Advanced Google Sheets Checkbox Options
If you add a checkbox to Google Sheets through the Data Validation menu, you have more options.
You can set custom values for the checked or unchecked states, i.e. something other than TRUE and FALSE.
For example, see how to create a select all checkbox in Google Sheets, which uses custom checkbox values.
You can also add a validation rule so that the cell rejects any values that do not equal the values you specify for Checked and Unchecked.
Five Ways To Use Checkboxes In Google Sheets
1. Create a To Do list app
Checkboxes make it easy to create a To-Do list in Google Sheets!
This example consists of a list of tasks in column A and a list of checkboxes in column B, with alternate colors added to the rows:
Then we can use 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:
The key point to note is the $ before the B only. That ensures that only column B is considered for the test but the formatting is applied to the whole row.
For a more sophisticated to-do list app, which includes a progress bar, see this checklist template here.
2. Highlight Data With A Checkbox
The same technique used in the to-do list example above can be used to highlight data in a dataset.
It’s conditional formatting applied across the row, but toggled on or off by the checkbox:
The custom formula in the conditional formatting checks that the checkbox is checked (TRUE) in addition to any other criteria.
Highlight your data range, in this case A2:C21 (omitting the header row).
Then go to the menu: Format > Conditional formatting
Under Format rules, select Custom formula is
As shown in the example above, to highlight rows where the revenue is greater than $3,000, enter this formula:
This is applied to the range A2:C21. The $ signs ensure the checkbox is referenced correctly and that the test is only applied to column C.
The AND function ensures that the checkbox is checked (i.e. TRUE) and the criterion is met before applying conditional formatting.
Your conditional formatting sidebar should look like this:
For a second example, suppose you want to highlight rows less than or equal to $1,000. Use this formula to do that:
See also how to highlight the top 5 values in Google Sheets for another example of conditional formatting. It could be combined with this AND function and checkbox technique too.
3. Show/hide hints and solutions to a test
Here's an example that may have applications in the Ed-Tech world.
Suppose you create worksheets that students 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 function and the TRUE/FALSE value of the checkbox to show/hide the solution.
For example, with a checkbox in cell F9, the solution for 35% of 40 could be shown by this formula to show the answer:
=IF( F9 , 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( F9 , "= 0.35 * 40" , "" )
4. 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 has a checkbox so that a user can select whether they want to include it or not.
In other words, the data range has a column of TRUE/FALSE values, where TRUE indicates that you want to include these rows and FALSE indicates that you do not want to see these rows.
Next, you use a formula to filter out rows that have a FALSE value and create a table of data for the chart.
For example, here's the QUERY formula to filter out the unchecked rows.
=QUERY( A1:F6 , "where F = TRUE" , 1 )
The chart is then drawn from this second, dynamic data table. When you check or uncheck rows of data, those series will show or disappear from the chart.
5. Use Checkboxes As Control Switches
Suppose you have a large number of complex calculations, and your Google Sheets runs slowly because they recalculate every time you make a change.
Use a checkbox to act as a control switch and prevent these formulas recalculating all the time.
Then use an IF formula to control whether the performance-hungry formulas in your slow Google Sheets calculate or not:
=IF( $A$2,your_formula_here, )
The FALSE argument of the IF function is shown blank in this example, but could be replaced with a note, e.g. "On hold".
Q: Can you create a select all checkbox?
A: Yes, with a bit of effort, you can. Here's how to create a select all checkbox in Google Sheets.
Q: Can you make the checkboxes behave like radio buttons?
A: Yes, you can using Apps Script. Here's how to create Radio Buttons in Google Sheets.
If you want to avoid Apps Script, you can still mimic radio button behavior with formulas and checkboxes.
Checkboxes In Google Sheets Template
Feel free to make a copy: File > Make a copy
If you can't access the template, it might be because of your organization's Google Workspace settings. If you right-click the link and open it in an Incognito window you'll be able to see it.