How to add checkbox in google sheets ( 7 Useful Example )

Written by Amy Brink Technically reviewed by Channa S Bandara.
Publish Date : May 14, 2021 | Review Date : May 15, 2021
Google Sheet, Spread Sheet

In latest google sheets offer many features for making your datasheet more functionally and smartly. Recently introduce checkbox in google sheets to permits the user to make a binary choice. This is a small Interactive box that the user can be checked or check out. If checked, return TRUE, or if unchecked, return FALSE. This is the default Boolean interactive value, but the user can change this return value using the custom data validation option.

About This Article
In this tutorial we cover, usage of the checkbox in google sheets and how to implement it with different formulas.

These things are you need to consider when you implement a checkbox in google sheets.

  • By default, the checkbox returns TRUE and FALSE. but users can change using custom data validation. There don’t have any limitation for customer value, you can insert any data type such as text, number, Boolean, icon.
  • Users can combine additional functions and formulas base on checkbox interactive behavior. But if remove and change the value in the checkbox in google sheets, that related operation and argument will emit an ERROR.
  • You can easily implement a checkbox with dynamic Named ranges in google sheets.

Insert regular checkbox in google sheets

This is a simple step that way of implementing a checkbox in google sheets. 

  1. Open a spreadsheet in Google Sheets.
  2. To Highlight cell or column or ranges, that you would like to insert checkbox
  3. Then move your mouse pointer to the top of navigation and click Insert (middle of View and Format navigation link) > Select Checkbox.Select top navigation - Insert regular checkbox in google sheets
  4. This is the default checkbox in a google sheet that will return TRUE and FALSE when checked and unchecked. That value represents in the formula bar (at top of the column header).checkbox status - Insert regular checkbox in google sheets
  5. If you need to add custom cell value to checkbox criteria, move your mouse pointer to the top of navigation and click Data (middle of Format and Tool navigation link) > Select Data Validation. Then data validation dialog box will appear on spreadsheets. data validation - Insert regular checkbox in google sheets
  6. Then check the ” Use custom cell values ” checkbox to change the checkbox return value. Now you can insert a new value to replace the existing default checked and unchecked value in given cell ranges. data validation option - Insert regular checkbox in google sheets
  7. Your custom checkbox value will appear on the formula bar on top of the column header.

How to Use conditional formatting rules with checkbox 

Conditional formatting is a set of condition operations (IF function) that use to formatting style (background, text) in cells, rows, or columns base on the given range into certain conditions. It also can apply with checkbox interactive behavior. After implementation, conditional formatting will apply whenever the checkbox is checked or unchecked. Let’s check with an example of how to implement conditional formatting with a checkbox in google sheets.

About example

Let's take a student attendant sheet, In this spreadsheet contain 3 data row ( Student Name, Gender, Attendant ) in 3rd column ( attendant status) field contain checkbox ( insert checkbox using above example ). If checked assume as the student not attend class.

  1. If student who not attend class, that checkbox cell background color keep as red otherwise in need to green ( unchecked ).
  2. If the student who not attend class, the student’s name, gender need to strikethrough with a red background color otherwise keep as white.
Download Example Sheet

(Example 01)

Highlight a single row using the checkbox in google sheet

  1. Open a spreadsheet in Google Sheets.
  2. Select cell ranges with the checkbox.
  3. Then move your mouse pointer to the top of navigation and click Format ( middle of Insert and Data navigation link ) > Select Conditional formatting.navigation - Highlight a single row using the checkbox in google sheet
  4. Then right side will appear Conditional format rules dialog box. Select the single-color tab.
  5. Select format rule dropdown. Actually, in this dropdown will contain a pre-defined if condition statement that can be easily plugged and play with your given range. Anyway, you can also create a custom condition statement to implement any queries, select the “is equal to” option in the dropdown.
  6. Then give a value which you need to change the background color. as the above requirement when checked checkbox background color needs to be red. For I have given value as TRUE. then in formatting style change the background color to red.conditional formatting rule - Highlight a single row using the checkbox in google sheet
  7. To apply unchecked green color (default color ), select + another rule from conditional formatting rule dialog box and select “is equal to” option from format rule dropdown then give value FALSE then change the background color as green in formatting style.

(Example 02)

Highlight multiple rows using the checkbox in google sheet 

You need to follow up the same step from 01 to 04 in Answer 01. But to highlight multiple rows when checked in the checkbox in google sheets. To implement that, you need to change the format rule and cell range value to dynamically interact with related cells (which apply with conditional statements).

  1. Open a spreadsheet in Google Sheets.
  2. Select cell ranges with the checkbox. 
  3. Then move your mouse pointer to the top of navigation and click Format (middle of Insert and Data navigation link) > Select Conditional formatting. If already an exit condition formatting rule in the dialog box, please click + Add another rule.
  4. Then the right side of the datasheet will appear Conditional format rules dialog box. Select the single-color tab.
  5. Select the “Custom formula is” option from the format rule dropdown. To apply checkbox interactive behavior to related cells, insert below a simple query in value field =$C3 OR =$C3=TRUE.syntax-of-Highlight multiple rows using the checkbox in google sheets
  6. Then in formatting style change the background color to red and select strikethrough.
  7. Now student’s name, gender cell with change red with a strikethrough, the student who does not attend in class.conditional formatting rule - Highlight multiple rows using the checkbox in google sheet 

How to filter data set with checkboxes in google sheets?

The interactive checkbox value can be used to handle many conditional functions. Using conditional formatting and logical operation ( IF/ AND / OR ) in google sheets, you can easily highlight and filter data which given range depend on the checkbox’s interactive behavior. let’s check how to use actual data. 

What is conditional function ?
Still don’t you have any idea about logical operation we highly recommend to read our Right Way To Use Google Sheets IF Function ( With 6 Helpful Tips ) Article.

About example

Let's take a student exam mark spreadsheet., In this spreadsheet contain 3 data row ( Student Name, Gender, Number of attempts, Exam Mark, Practical Mark, Total Mark ) total mark calculate using exam mark and particle mark then divide by 2 ( exam mark + particle mark / 2 )

Download Example Sheet

( Example 1 )

Highlight students who got less than 45  total mark

Highlight students who got less than 45  total mark

Condition Expression

If checkbox = TRUE AND total mark < 45

return TRUE: “change cell red background“

return FALSE: “default background color”

  1. Open a spreadsheet in Google Sheets.
  2. Then move your mouse pointer to the top of navigation and click Format (middle of Insert and Data navigation link) > Select Conditional formatting.
  3. Then right side will appear Conditional format rules dialog box.
  4.  In the Single Color section, select the cell range which you need to filter(highlight) data. You can also create Name Ranges for reuse. Base on the example sheet that data exit from A2 to F14.
  5. Then select the “Custom formula is” option from the format dropdown. It enables to execution of custom queries.
  6. Then insert this query into the value or formula field.
     =AND ($I$2 = TRUE,$F2<45) 
  7. then insert the background color like Red in formatting style.

Actually in this full query execute like this =IF(AND($I$2 = TRUE,$F2<45)) . but in conditional formatting automatically extend your query with if condition.

 

(Example 2)

Highlight students who got greater than 75 total marks

Highlight students who got greater than 75  total mark

Condition Expression

If checkbox = TRUE AND total mark > 45

return TRUE: “change cell green background “

return FALSE: “default background color”

To apply this in google sheet you may need to follow up with 1 to 5 steps in Answer 01 then,

  1.   insert this query into the value or formula field.
    =AND ($I$2 = TRUE,$F2>75) 
  2. then insert the background color as green formatting style

( Example 3 )

Highlight Female Student who got greater than 75

Highlight Female Student who got greater than 75

condition expression

If checkbox = TRUE AND total mark > 45 AND gender = “Female”

return TRUE: “change cell  purple background“

return FALSE : “default background color”

To apply this in google sheet you may need to follow up 1 to 5 step in Answer 01 then,

  1.   insert this query into the value or formula field.
    =AND($I$4, $F2 > 75, $B2 = “Female”)
  2. then insert the background color as Purple formatting style

( Example 4 )

Highlight Students who got greater than 75 marks in 1st or 2nd attempt

Highlight Students who got greater than 75 marks in 1st or 2nd attempt

Condition Expression

If checkbox = TRUE AND total mark > 45 AND number of attempts = 1 OR number of attempts = 2

return true : “change cell  yellow background “

return false : “default background color”

To apply this in google sheet you may need to follow up 1 to 5 steps in Answer 01 then,

  1.   insert this query into the value or formula field.
    =AND($I$5, $F2 > 75, OR($C2 = 1, $C2 = 2))
  2. then insert the background color as yellow formatting style

You can get a basic idea of how you can implement a checkbox in google sheets. And what is conditional formatting and how to use the logical condition in the checkbox interactive behavior.

Table of article :