December 4, 2020

How to add checkbox in Google sheet

In this lesson we will learn how to add checkbox in Google sheet. To add checkbox in your google sheet follow the below steps.

  1. First select the cell of your google sheet where you would like to create the checkbox.
  2. Now click ‘Tick Box’ option under ‘Insert’ menu of the Google Sheet.

3. This way the checkbox will be added in the selected cell. Using the same process you can create checkboxes to multiple cells together if you select multiple cells at the beginning at once.

As you can see below we have created checkboxes in first 10 cells of column A.

Notes :
  • Remember, if the cells contain any other value in which you are creating checkbox, the data will be removed automatically.
  • You can format the cells containing checkbox like applying colours, changing font and so on. See below our checkboxes after applying format as required. We have increased the font of the checkbox so that it looks bigger and also added font and fill colour.

  • If the checkbox is checked the value is TRUE and if not checked the value is FALSE. You can sort the column containing checkboxes as follows. We have added a header for our column containing checkboxes and checked some of the checkboxes.  Now right click on the column and select any option between ‘Sort A->Z’ or ‘Sort Z->A’ according to your need.

  • Here we have sorted by A->Z. See below all unchecked checkboxes appear before the checked checkboxes as FALSE comes before TRUE in order A->Z.

  • You can add and apply filter for the checkboxes as well. Like below we have applied filter to the column containing checkboxes and selected only the value TRUE, which corresponds to the checked checkboxes.

If we need to change the default values which are TRUE & FALSE for the checkboxes when selected or not selected, we have to select the checkbox or range of checkboxes and go to ‘Data Validation’ under ‘Data’ menu of the Google Sheet.

Now in the Data Validation pane select ‘Use custom cell values’ and then provide the values you want for ticked or unticked.

Also if you want some help text to appear on mouse hover on the checkbox then select ‘Show validation help text’ and add the help test you want to display.

Here we have added the values Yes & No for Ticked and Unticked checkbox and in validation help text we added ‘Enter Yes or No’ as follows.

As you can see below the help text appears on the mouse hover on the checkbox and the checkbox cell values have also been updated to YES or NO.

Now we will see an example to create a TO DO List using checkbox.

Let’s say we have created the below To Do list for every month with the checkbox associated with each task.

Now the above To Do list will work in such way that upon selecting the checkbox in column A, the corresponding task in column B will be changed to Strikethrough format along with a different fill colour.

  • First select the To Do tasks in column B.
  • Now click ‘Conditional formatting’ under ‘Format’ menu of the Google Sheet

  • Then in Conditional Formatting pane select ‘Custom formula is’ in ‘Format cells if’ under ‘Format rules’.

  • Then add the below formula. 
=$A2

This formula signifies if the value in A2 is TRUE then the conditional formatting rule will be applied on cell B2 and same for the following cells in range. Here the Tick and Untick value for the checkbox we should keep as TRUE and FALSE. In case it has some different value like YES/NO then the formula should be as follows.

=$A2=YES
  • In the ‘Formatting Style’ we have selected strikethrough and also chosen the desired Fill colour.

  • Now as you can see below, for the Ticked checkboxes, the respective tasks style have been changed as per the conditional formatting rule. For the unticked ones since the value returned FALSE for the corresponding cells in column A, the respective tasks in column B have no change in format.

Now we will learn another example of Highlighting Data Using Checkboxes in google Sheet.

Let’s say we have the below table containing different stages of building an office along with the date of start and the duration of work for each phase.

Now we have created three checkboxes as follows for the below conditions.

  • Date between 1/5/2020 to 6/10/2020
  • Duration <10
  • Duration>12

We need to implement such a system when we put a tick in any of the above three checkboxes, the corresponding cells satisfying the respective condition will be highlighted.

To do that follow the below steps.

  • Select the cells in column C. This column contains the Start Date for each phase.
  • Click ‘Conditional Formatting’ under the ‘Format’ menu 
  • Then in Conditional Formatting pane select ‘Custom formula is’ in ‘Format cells if’ under ‘Format rules’
  • Enter the below formula
=AND($G$4,IF(AND(C2>DATEVALUE("1/5/2020"),C2<DATEVALUE("6/10/2020")),TRUE,FALSE))

Here the cell G4 contains the checkbox for the first condition. Let’s first understand the formula in a bit more detail.

AND(C2>DATEVALUE(“1/5/2020”),C2<DATEVALUE(“6/10/2020”)) – This section of the formula finds the dates in column C which are between 1/5/2020 to 6/10/2020. Even though the first cell that is C2 is mentioned in the formula but after applying conditional formatting rule on this custom formula it will be applied to all cells containing date in column C.

IF(AND(C2>DATEVALUE(“1/5/2020”),C2<DATEVALUE(“6/10/2020”)),TRUE,FALSE) – This part of the formula returns TRUE or FALSE based on each date in column C if meets the condition or not.

AND($G$4,IF(AND(C2>DATEVALUE(“1/5/2020”),C2<DATEVALUE(“6/10/2020”)),TRUE,FALSE)) – Here is the entire formula where the AND function is taking two values as arguments, the checkbox value and the value returned by the IF formula. The checkbox value is TRUE if ticked. The cells in column C which are satisfying the IF condition and returns the value as TRUE for them the value returned by the AND condition will also be TRUE and the rest will be FALSE.

  • Select the desired Fill colour to highlight the cells
  • Click Done in Conditional Formatting pane

Now as you can see below if you select the first checkbox the cells in column C containing the date between 1/5/2020 to 6/10/2020 are highlighted.

  • In the same way you can select the cells in column D(D2:D11) and in Conditional formatting pane add the below custom formula to highlight the cells containing duration<10 days.
=AND($G$5,D2<10)
  • Now as you can see below if you select the second checkbox the cells in column D containing the duration<10 days are highlighted.

  • In the same way click ‘Add Another Rule’ in the Conditional formatting pane and for the cells in column D(D2:D11) add the below custom formula to highlight the cells containing duration>12 days.
=AND($G$6,D2>12)
  • Now as you can see below if you select the third checkbox the cells in column D containing the duration>12 days are highlighted.

  • As you noticed above if you untick the checkboxes, the highlight of the corresponding cells are gone. Also you can select all the checkboxes together as follows.

Since the checkboxes are the part of cell in google sheet, you can copy or delete checkbox in same way as normal cell copy and delete operation in google sheet.

Leave a Reply

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