Introducing the Google Sheets Checkbox and Three Ways You Can Use Them ☑️

Have you tried using a Google Sheets checkbox yet?

They give you a visual way to toggle between checked and unchecked values (true and false) in a spreadsheet cell. This opens up all sorts of opportunities to make your Sheets more interactive.

How do I insert a Google Sheets 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.

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.

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 Google Sheets checkbox

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

true Google Sheets checkbox

Advanced Google Sheets 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 in Google Sheets

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

Related Articles

128 thoughts on “Introducing the Google Sheets Checkbox and 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. Don’t know if you’ve figured this out yet, but the checkbox state is linked to the value of the cell it’s in. So to set a checkbox to checked, set its associated cell to true or 1. Alternatively, using data validation in the cell you can set the checked/unchecked values to whatever you want. To change the status of the checkbox just change the cell to the appropriate value. For example, I have a checkbox set in the data validation menu to checked = 25 and unchecked = 0. I can make it checked or unchecked simply by doing this in a script.

        SpreadsheetApp.getActiveSheet().getRange(“Sheet1!N19”).setValue(25);

        or 0.

  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.

    1. The workaround I have found for this is to utilize a script like the one below. Basically, it checks that the box I’ve marked is now “True” (i.e. it’s value is “1”) and, if so, it clears the contents of all other checkboxes in that group; in this case, all my checkboxes are in the same row:

      function onEdit(e) {

      var test = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

      var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Song Database”);
      var row = e.range.getRowIndex();
      var col = e.range.getColumnIndex();
      var c = s.getRange(row,col).getValue();

      if(c == 1 && col==4) { /Song is ready/
      s.getRange(row,5,1,3).clearContent();
      };

      if(c == 1 && col==5) { /Song is a work-in-progress/
      s.getRange(row,4,1,1).clearContent();
      s.getRange(row,6,1,2).clearContent();
      };

      if(c == 1 && col==6) { /Song is homework/
      s.getRange(row,4,1,2).clearContent();
      s.getRange(row,7,1,1).clearContent();
      };

      if(c == 1 && col==7) { /Song is proposed/
      s.getRange(row,4,1,3).clearContent();
      };
      };

  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

    2. Macro or scripting does it easy running from Script Editor 🙂
      Now I have to find a way to fix this error when running from spreadsheet: “You do not have permission to call setValue…”
      Cheers!

  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?

  30. Anyone know if you can have mutiple checkboxes inside of a single cell? As an example, we are using a google spreadsheet to house content for “Projects” (up to 100 projects).

    Each project consists of data points such as:
    – Completion date
    – Project name
    – Project Summary
    – Related Market Sectors (this is where I would love to have a cell allow for someone to checkoff multiple “sectors” and ideally these would all live inside of a single cell as each association of a sector for each project is unique to each project).

    Any help/insights would be greatly appreciated!

    1. I don’t think this would be possible since the checkbox sets the value of the cell (Usually as true or false). You can’t have more than one value in the same cell.

  31. Hi Ben,

    Thanks for the great article.

    I’m having a problem with checkboxes that I’m sure you can help with:

    When I copy a spreadsheet with checkboxes into another sheet using IMPORTRANGE, I lose the formatting. I “solved” this by simply inserting the checkboxes manually into the new sheet, which seemed to work fine. However, when I share the second sheet with someone else, the boxes appear empty, though the text in them is correctly “TRUE” or “FALSE”. Anyway I can share the second sheet without this happening?

    Thanks!

  32. Is there any way to turn these into radio buttons? Like when one is checked the other boxes automatically are unchecked? IE Only one can be checked at a time.

  33. Is there anyway you can alter the condition of the check box based on the results of another cell?

    i.e. If a list of items is blank ( nothing selected/ cell empty ) the checkbox is false, but if something from the list of items is selected the checkbox is true?

  34. Hello,
    My question is similar to one posted above.
    I’m very new to google sheets and do not have experience with excel. I have columns on an invoice with checkboxes. The checkboxes are used for contractors to check if the fee they are billing for
    is for labor or materials. When I view the sheet, I see the checkboxes. I am able to edit and create a check mark in the box. However, when those who are granted access to edit via shareable link view the document, they see the words “true” or “false” If they click on them they change from “true” to “false or vice versa. I would like for them to see only the boxes, and be able to place the checkmark. Thanks!

  35. I am using the check boxes for planning a wedding–checking the boxes of who received an invitation to the wedding, the shower, etc. Is there a way to add how many boxes I have checked. I would like a total at the bottom saying I have 38 boxes checked.

  36. It is a great development but I wish they would link better with excel. The company I work for is using both sheets and excel so I need a form with interchangeable functionality. However, a check box in sheets comes through as true/false text when downloaded to excel, yet in sheets they show as an image when importing an excel version that cross over the text options. I have to have two different versions of a form to accommodate those who’ve moved over to sheets, but it’s proving a nightmare when the form crosses over!

    I can’t even think of a way around for selecting an option without a tick box or form control, only to maybe ask the respondent to highlight their option in bold or something, which totally negates the ease of a check box! Any suggestions?

    1. The only thing that differs between Excel and Sheets on checkbox is the separate image in Excel. In Sheets, image and TRUE/FALSE are on the same cell. Also, Excel takes 3 values: TRUE/FALSE/NULL.
      The good news is, they both react to TRUE/FALSE cell.
      Simply create extra import/export tabs for each so they’ll be compatible. Meaning in Excel, have separate tab that will be compatible with Sheets and vice versa.
      I hope that helps – Cheers!

  37. Hi,
    I am new to google sheets. I have worked out how to add up how many boxes are checked using Countif.
    Is there anyway to work out what the total percentage of boxes that are checked in the row?
    eg. if 4 out of 8 boxes are checked – it would be 50%
    Thanks

    1. Hey Bek,

      Yes, you can add a COUNTA to get the total number of checkboxes as your denominator. For example, if you have 10 checkboxes in A1:A10, this formula will determine the percentage that are checked:

      =countif(A1:A10,"true")/counta(A1:A10)

      Cheers,
      Ben

  38. Hi,
    I’m adding rows into a sheet using the sheets api (interacting through integromat specifically). Is there any way I can add a checkbox into a couple of cells in the row. A checkbox doesn’t copy with a function.
    Right now I can only do that by copying from the previous row.
    Thanks

  39. Thanks for sharing this Ben, those graphs option are very helpful. Could be the next enhancement to my existing Sheets.
    Looking forward to Radio Button function too, where you can only select one from the options. Normally in circle buttons but I won’t mind the tickbox as long as only one option can be selected.
    Also trying to find ways to show checkbox without scripting when another cell is not null. I need the checkbox to show only on column B for example, when column A have value…

  40. Hi Ben,

    I have grouped rows together and the first row is a header for a list of tasks that need to be done for a project. Can I check the main task/header as complete and subsequently make its sub-tasks marked as complete automatically?

    Thanks!

  41. Hi and thanks for all the great information provided on the blog the courses.
    I´ve got a question regarding checkboxes and conditional formatting.

    I have a row with 5 checkboxes. I know how to apply conditional formatting so that the entire row changes depending on if a checkbox is checked or not.
    Now, how would I go about if I want the entire row to change (nackground color for ex.) if all checkboxes are “checked”.

    I´ve read through the comments and I have tried with the best of my abilities, but without any luck,

    1. =IF( AND( firstcell=TRUE, secondcell=TRUE, … lastcell=TRUE))

      or

      =COUNTIF( rowrange, FALSE)=0

      or several other ways.

  42. Hey Ben – I am trying to create some if/then formulas off of the checkboxes, but it doesn’t seem to work. This is the basic idea of my formula – =if(C75 = “TRUE”, “YES”, “NO”), and no matter if the box is checked or not, it always populates “NO”, as if it doesn’t recognize the “TRUE” or “FALSE” that populate the referenced cell, C75. Any ideas on this?

    1. TRUE and FALSE are Boolean conditions, not text. Remove the first set of quotes, and it will work.

      =IF( C75=TRUE, “YES”, “NO”)

      Or make it even simpler than that. The spreadsheet software evaluates the condition to arrive at an answer that is either TRUE or FALSE. If C75=TRUE, then all you really need is

      =IF( C75, “YES”, “NO”)

  43. Hi Ben,

    I’m using these for a to do list/task management for my team and was wondering if it’s possible to format so that if a box is checked, the entire row is moved to another tab that I would call “Completed”. I’ve been just moving completed tasks myself or filtering but it would be great if I could get it to do this automatically. Thanks!

  44. Is there a way to link the data such that I have a master sheet and individual data sheets and if I check the box on the individual data sheet it applies the check and conditional formatting to both the individual data sheet AND the master sheet?

  45. Found this via Google search for “radio button Google docs”. I still wish there were an out-of-the-box solution — BUT — your To-Do list example helped me achieve the intended function with a useful, elegant workaround. Thank you!

  46. Hi Ben,
    I was wondering if you can help, if I have a list of names in a sheet and selection of tick boxes for each name (in this case vehicle types they are licensed to drive) Is there a formula I can use to copy their names to a different sheet within the same doc based on the status of that tick box?
    Thanks

  47. Hi!

    I have a sheet that i want to record my time i work in.
    At certain times i work overtime and have different paygrades on the time. What i want to do is to have a checkbox to tell the sheet if the overtime is approved or not and then it calculates the diffrent times into different columns. If not approved i want everything in one cell at that column.
    Hard to explain but it’s a simple setup.

    Anyone have an idea what to do?
    There are 3 cells that need to read the checkbox and do one or the other depending on true or false.

    Here is a link to a test document.
    Some words are in swedish 🙂
    Övertid = overtime
    Mertid = overtime with out extra pay

    Need the Övertid checkbox to be ticked and then fill the extra pay columns (first the 16:00 one and when time passes 20 (8 pm) then fill that column with the time after 20.

    Here is a link to the test document 🙂

    https://docs.google.com/spreadsheets/d/1G4zV0RYTFj06RYssc9oXGAAggqLziYTwylfKX4Ttvjw/edit?usp=sharing

    Thanks!

  48. I want to add a completion checkbox at cell c68 getting all checkbox from c5:c67 to be true. I was hoping this might work: =IF({C5:C67},”COMPLETED”,””) however it shows completed even thought a cell example c5 is unchecked. Please any advise on this project.

  49. This should be easy, but I am missing something…. I created a Query to extract data from main sheet into a secondary sheet. However the CHECKBOX (Column F) becomes TRUE or FALSE. I tried Data Validation and that made my query information disappear. Can the desired result be built into the Query formula? If yes, suggestions…

    =QUERY(Sheet1!A:G,”select A,B,C,D,F,G where E=’AHS ‘”,1)

  50. The checkbox looks great. I would like to format data with a checkbox, but the checkbox is formatted data and a built-in ui.

    Checkbox registers as data, 1 or 0 (true/false).
    If a checkbox is added to the cell, it is considered a cell with data. ISBLANK returns 0.

    Now I have an array formula returning true or false (1/0) and I would like the results displayed as a checkbox.

    my function:
    (A2:A) == 100? 1 : 0

    I would like to display this as checked and unchecked boxes.

  51. Hi Ben, really cool work!

    Do you think is possible to make a progress bar or a dynamic chart with checkboxes?

    I mean if I have 100 checkboxes, and I check 50, a chart o progress bar that shows the percenteage 50% or the progress like 50/100 of checked boxes.

    Thank you in advance

  52. Hi Ben,

    This is a great post. Thank you for writing it.

    For checkboxes, is there a way to have a person be able to select only one checkbox. I have a checkbox for Yes, No, and N/A (as part of a scoring rubric). If a checkbox is selected for “Yes”, the person shouldn’t be able to select the checkbox for “No” or “N/A”.

    Is that possible?

    Thanks in advance!

  53. Ben, it’s wonderful that you continue to create these very helpful posts!

    I have a single question re the use of the checkbox.

    I have one column on a google sheet that teachers can select student names so that the teacher can send emails to those selected.

    Is it possible to make the checkboxes interactive on a new google site so that the teacher can use a web page that has the sheet instead of the sheet itself?

    Thank you very much!

  54. Are you able to assign the checkbox to a number so when you enable your column of checkboxs, it will have a sum total of the assign number?

    I’ve try it with a column of number and a column of checkbox beside it and had “=SUMIF(I24:I27, TRUE, H24:H27)” as the total. but I don’t want my number to show in a column, don’t know if there’s another way?

  55. Hi Ben, hello to all the fans of Google Sheets!
    Let me show you something I did with the check boxes.
    To color a Gantt diagram I used conditional format for the boxes, between true and false, color on color for true and white on white for false (this is not conditional)
    Google Sheets does not allow check boxes to have color and contrast of the exact same color. To avoid that restriction I used two very similar colors playing with RGB, for example # FF99CC on # FF99CD, these are two different colors for the restriction, and for the eye they are practically the same.
    I leave a copy of the sheet where I applied it, I hope it is useful. Click on each box in the diagram!
    https://docs.google.com/spreadsheets/d/1dFBSFQ2SLqRGBvzSmTC2C7dWWaMd3kBix5Q0EmdHPFc/copy
    Greetings to all!
    Raúl, from Buenos Aires.

  56. Hello, I have question.

    If i apply the data validation for the entire row/column but only to make the drop down availble when the next to it column have value, how is it?? is there any extra formula? because the drop down will appear even there is no data next to it.

    1. Hi Hsak, I think that this form of custom formula should work in the conditional format of the row:
      =AND(($E6=TRUE),($D6>10))
      I hope it is useful, greetings.
      Raúl

  57. Hi Ben, I want to insert a checkbox in a google sheet using a formula. So for example if cell A1 is not NULL then I want to insert a checkbox in cell B1. Is there a formula or a script that could do this please?

    Something like =if(A1″”, “CHECKBOX???”, “”)

    Kind regards,

    Angel

      1. Thanks Ben for your reply. Ben, is it possible to add the current date/time in an adjacent cell when a user ticks a checkbox?

  58. Good morning Ben,

    I would like to know if there is an option to add a time stamp to the check boxes. Example, if someone checks it I would like it to populate the time, date and if possible the initials of when/who checked it. I know I have seen this on an application somewhere, but no idea where I saw it.

  59. Hi. How can I use a formula that says if my check is true then sum this, don’t sum it if checkbox is not ticked?
    I tried this but it doesn’t work
    =IF(K6=TRUE,”YES”,”NO”),SUM(P29:P31)+’Sprint Values ‘!C2)

  60. Dear Ben,

    Thank you very much for this interesting technique regarding the creation of a dynamic chart using the checkboxes. I created a dynamic bar chart using the checkbox function. In a hidden tab, I created a query function giving me all the lines where a checkbox is marked. Basically identical to your approach but within two separate tabs. Unfortunately, when the number of checkboxes marked, falls under a certain amount, the bar graph does not display any data anymore. I found out that the setup of the graph automatically changes. It then deletes the data series and puts the number that should be displayed as a bar as a label of the x-axis instead. (which does not make sense at all) I could not manually change that, as it automatically resets. I saw that this was not a problem with the example that you shared with us. Do you have any idea why there is a difference in that? Thank you very much for your help in advance.

Leave a Reply

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