Checkboxes (☑️) are now available in Google Sheets! Here’s three ways you can use them.

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:

Insert checkboxes menu

At the moment they have a NEW label next to them, but that disappears after a while.

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.

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).

False checkboxes

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. Again, you can use this in your formulas.

true checkboxes

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.

Checkboxes from data validation menu

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:

Dynamic chart with checkboxes

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):

Dynamic chart with checkboxes workings

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 )

The chart is then drawn from this second, dynamic data table (shown by the 2 in the image above). When I check or uncheck rows of data, those series will show or disappear from the chart.

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:

To do app with checkboxes

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:

Show answers with checkboxes

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.

27 thoughts on “Checkboxes (☑️) are now available in Google Sheets! Here’s three ways you can use them.”

    1. I just attempted getDataValidation on a checkbox cell and received “We’re sorry, a server error occurred. Please wait a bit and try again.”, however, the same code works perfectly on any non-checkbox on the sheet.

      function myFunction() {
      var ss = SpreadsheetApp.getActive().getSheetByName(‘test’);
      var cell = ss.getRange(1,1);

      var rule = cell.getDataValidation();
      if (rule != null) {
      var criteria = rule.getCriteriaType();
      var args = rule.getCriteriaValues();
      Logger.log(‘The data validation rule is %s %s’, criteria, args);
      } else {
      Logger.log(‘The cell does not have a data validation rule.’)
      }
      }

  1. Any idea on how to dynamically select a checkbox based on a value of another?
    eg =IF( G8 <=10, TRUE, FALSE )

    If TRUE checkbox is selected, if FALSE Checkbox is not selected.
    It seems when setting the boolean values TRUE or FALSE overwrites the checkbox cell.

    1. You can have that formula in a cell to give you the TRUE/FALSE value, but you can’t also have a checkbox showing.

      The checkbox is actually a data validation on the cell, so if you type TRUE/FALSE (which will delete the formula) then the checkbox shows again.

      Cheers,
      Ben

  2. When I look at the insert menu or the data validation criteria I don’t have the option for checkbox. Is there something I need to do to enable it?

    1. Samantha: I’ve notice the same. I use two browsers, one in English and the other in Portuguese. There’s the new feature in the english one. Maybe in some time we’ll have it in all of them.

  3. Ben, thanks for alerting about this new awsome feature! I have used it instantly on a Google Sheet of pay bills to indicate already paid/not yet paid bills w/conditional formatting.
    Great tip!

    1. There’s no third state but you can still type whatever you want into the cell, and override the checkbox. However, you’ll see a “Invalid:
      This cell’s contents violate its validation rule” error message.

      The TRUE/FALSE checkbox comes back if you type TRUE or FALSE again.

      Hope that helps!

      Ben

  4. That is really cool, but I don’t think it has been rolled out to all users yet. The option doesn’t appear for me on my grandfather-in g-suite account, or on my more recent company account.

  5. This is just what I need for my latest project. Do you know if it is available to dynamically add through the api? I can’t see it in the docs.

    1. Great question! Not sure either, as I couldn’t find it in the docs when I looked. I’m sure it will be available in time though.

      Ben

  6. Hello Ben,
    Very nice article and examples. However, when I try to use the =IF(A1=”TRUE”,”YES”,”NO”) in order to insert in B1 a “YES” if my A1 is checked it says “Formula parse error.”. Can you help me with this please? 🙂 Thank you in advance!

  7. Hi Ben, any thoughts on using a countif on these? Looking to use this for process management where 1 piece of inventory has 3 or 4 different checkpoints and the countif would allow me to create a dashboard/scoreboard for that process completion.

  8. Hi

    Just tried the new Checkboxes (could make many of my spreadsheets cleaner), but hit an issue.

    If you set up cells with checkboxes, and CLEAR the cell content (select sell, hit delete) then the checkbox doesn’t un-check, but disappears entirely! That makes it pretty much unusable, as most users will use that method to clear a check.

    Is there anyway to prevent if from being deleted?

    1. I am looking for a solution to that exact same problem as well as the ability to add these back (onOpen) through AppScript. If users delete the checkboxes (as most certainly they will), this destroys any reporting applicable to that cell. I cannot seem to find a solution out there for either? Any suggestions welcome!!!

  9. Utilizing the checkboxes for a selection of cells (i.e. 5 of them), is there a way to limit that selection of cells to only allow ONE checkbox?

  10. This is a great functionality, but there is a problem with it: users can delete it. If you have an editor allowed to edit a range, that person can delete the checkbox. So this not a good functionality to be shared, yet.

Leave a Reply

Your email address will not be published. Required fields are marked *