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.

71 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!

      1. Hey Lucian, Roman,

        You can go one further and actually just ditch the comparison with TRUE, since if A1 is TRUE the IF statement will execute the TRUE part of the logic. i.e. your formula could be even shorter:

        =IF(A1,"Yes","No")

        Cheers,
        Ben

  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.

    1. Hey Mitch,

      Yes, you can use COUNTIF. For example, suppose you had your checkboxes in column A1 to A10, then this formula will count whenever they are checked:

      =countif(A1:A10,TRUE)

      Cheers,
      Ben

  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!!!

    2. Hey Ian,

      Unfortunately I don’t think there’s a way around this at present…. If I find any “hacks” I’ll post them here.

      Cheers,
      Ben

  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.

      1. that’s an option depending on what you need to share. If you need users to use this checkbox, if you protect the range, they will can not interact with it. It is kind of weird, you can set a data validation in one cell and users can not delete it, but if you use the checkbox it can be deleted.

  11. Hi Ben,
    When using Forms to populate the Sheet, is there a way to have the form answer cause a check box to be checked? So for example, if on the form I ask “what is your favorite food?” and you check “steak” out of a possible 6 choices, right now “steak” shows up on the sheet as text. Can I have that response automatically click the Steak Checkbox that I created in the same row (assuming I create a series of checkboxes tied to the possible responses)? Thanks

    1. Hey Greg,

      Unfortunately I don’t believe this is possible at present, at least not with formulas. If and when checkboxes are accessible with apps script, it might be possible to achieve this then. I’ll keep an eye on the reference docs and post an article once we can access checkboxes with apps script.

      Cheers,
      Ben

  12. Great Article. Was able to use it to conditionally format cells based on the check marks. One issue I had is that if I tried to set the rule for more than one cell with a checkbox I could not do it. Is there any way to do this. What I want is to have several checkboxes and if all of them are unchecked I want it format it one one while if one of the checkboxes is checked it formats it another way.

    1. Hey Joshua,

      Without knowing the precise details, you should be able to achieve what you want by using the AND and OR functions, and maybe some IF statements…

      Basically, the one scenario you mentioned is equivalent to:

      FALSE | FALSE | FALSE

      in your cells.

      Hope that helps!

      Ben

  13. Is there a way for only one checkbox to be checked at a time in a range of cells? So if someone checks one box it unchecks the other one selected in that range.

  14. Is it possible to gain access to the spreadsheet referenced in “1. Create Dynamic Charts” that explores using checkboxes to show/hide data in a chart of GDP by country?

    I’m trying to play around with a similar feature and I think it would help to make a copy of your Sheets example to explore.

    Thanks in advance!

  15. Hi Ben

    Do you know how you can create a “Check All/Uncheck all” box? I know you can do this in excel but can’t figure it out on G sheets.

    Thanks
    Athena

    1. Hey Athena,

      Unfortunately you can’t do that at present with these checkboxes. They’re independent of each other. I think we’ll see more functionality in the future though.

      Cheers,
      Ben

  16. Is there anyway to link the check boxes so if a box is checked on one active sheet it automatically copies to another sheet in the same document – I want to use them for attendance records each teacher has their own tab and I’d like to have a copy of everyone’s data on a final tab using a copy function.

    1. You could have a checkbox link to another cell, which would display the same TRUE or FALSE value, but you can’t link to another active checkbox.

      Cheers,
      Ben

  17. Hi,
    Can you add a conditional format so that when you untick a checkbox it will remove the cell colour. I can do it so the cell turns a different colour when i check the box but not when I uncheck it.

    Regards

  18. I have 4 checkbox options in a row to track the workflow of a document. A2=checkbox for “buyer signed”; B2=checkbox for “seller signed”; C2=checkbox for “completed”; D2=checkbox for “N/A” (document not needed in this transaction).

    I tried conditional formating so that if the checkbox C2 is selected, A2:B2 would gray out, and adding a second conditional formatting for if D2 is selected (N/A) that A2:C2 (or the entire row) would gray out. Anytime I add the 2nd conditional formatting formula, it deletes the other. Is there a reason for this? It doesn’t seem to want to allow both to exist together.

    1. Hey Luke,

      Hmm, not sure why they’re being deleted… If you mean they’re not working as you expect, try changing the order of the rules (you grab the rule handle and drag it above the other one). The rule at the top will take priority over those below.

      Hope that helps.

      Ben

  19. Hey there. I’m trying to have one cell add up cells A3:A50 and that worked fine using the SUM function. However, cells B3:B50 have the check mark box next to them. Is there a way to have the SUM function only add the numbers whose corresponding check mark box returns a FALSE value? I’m trying to figure it out but I can’t seem to not get errors.

  20. I’m trying to figure out the data validation to use so that if the word “admin assistant” is found in a cell, it checks the box; however, the cell could have “admin assistant”, “HQ”, or “Evang admin” in it. Is there a way to check a box in E2 if “admin assistant” is in the cell D2, check the box in F2 if “HQ” is in the cell, and check the box in G2 if “Evang admin” is in the cell?

  21. THis is great, thank you. I have a sheet with 50 or so rows and 10 columns. Each cell is a checkbox that corresponds to a step in a process. How can I apply the formula you used for the color change ( = $B2 = TRUE) en mass so that each individual cell is independend to each other?

    i.e. rather than change the color of the whole row, I just want to change the color of each individual cell.

    Thanks in advance.

    1. Hi Martin,

      Change your conditional formatting rule for the whole range (all 50 rows and 10 cols) to be:

      Format cells if…
      Is equal to
      TRUE

      Cheers,
      Ben

  22. Hi Ben!

    Late to the game and I’m slowly learning 🙂 Your site is helping me tremendously so far!

    I ran into a mini issue and I’m not really sure if it’s possible to do or not. Can’t find anything but I might not be using the correct terms. I’m making a sheet with client’s payments that didn’t go through. Once I have their updated billing info I’d like them to “fall” down to the bottom of the list once I “check” off this box. Is there anyway to do this?

    Thanks so much,
    Dani

  23. Ben

    I am loving your videos for Data Studios, absolutely fantastic.

    I am having a issue which searching for via the forums, doesnt seem to have an answer for.

    In my google form I have check boxes, which creates multiple answers in a cell. When I then create a pie chart, it doesnt seem to treat the multiple answers, as individual answers. Is there a way around this?

  24. Question: I am using check boxes for meeting students. So, I was able to put a rule where if I check the checkbox, it automatically turns green. But, I really need the whole row (not column) to turn green when the check box is pressed. Is this possible? If so, how?

    Thanks in advance.

    1. Hi Angelann,

      Yes, you can definitely do this. Have a look at the conditional formatting custom formula rule that I apply in the To-Do app section of this article.

      Cheers,
      Ben

  25. I am trying to find a way to have an uber list that I can check values I want and have that spit out a list of only those values. That way I can generate different specific tasks for different project. Any thoughts on how to do this?

  26. I was going to use this to track data with my students. Is there a formula I can put at the bottom of my column to count how many boxes are checked?

    Thanks

  27. Hi Ben,
    i want to add macro checkbox. how can i do that? i recorded a macro. i want to run this macro with checkbox. i write a cell =myFunction() then i take this eror ” You do not have permission to call setActiveSheet (7. satır).”

    My Macro
    function myFunction() {
    var spreadsheet = SpreadsheetApp.getActive();
    Utilities.sleep(2);
    spreadsheet.getRange(‘U2’).activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘Yapılan işler’), true);
    spreadsheet.getRange(‘A3’).activate();
    spreadsheet.getRange(‘Planma!U2’).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘Planma’), true);
    spreadsheet.getRange(‘B2’).activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘Yapılan işler’), true);
    spreadsheet.getRange(‘B3’).activate();
    spreadsheet.getRange(‘Planma!B2’).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘Planma’), true);
    spreadsheet.getRange(‘C2’).activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘Yapılan işler’), true);
    spreadsheet.getRange(‘C3’).activate();
    spreadsheet.getRange(‘Planma!C2’).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘Planma’), true);
    spreadsheet.getRange(‘F2’).activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘Yapılan işler’), true);
    spreadsheet.getRange(‘D3’).activate();
    spreadsheet.getRange(‘Planma!F2’).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

    };

  28. Hi, I have a problem which I’m hoping someone can help.

    I have 4 columns (A-D) with tick boxes using the tick box function on sheets. I wanted to put a formula in so that if I select column A as True then it will automatically select columns B-D as true (tick the boxes). I got this to work with IF formula, however I’m then not able to untick or use the tick box in columns B-D as if has the the formula in the cell.

    Any Ideas?

    A B C D
    All John Jack Steve
    TRUE IF(A2=TRUE,TRUE,FALSE)

  29. I am using the checkboxes as a completed task check. They are both in separate columns. Is there a way for me to format it so that the row changes color based on both boxes being checked?

Leave a Reply

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