Skip to Content

How to Use Checkbox in Google Sheets

In Google Sheets, you can easily insert a checkbox that allows you to toggle between TRUE and FALSE values by ticking the box.

This can be a very useful way to make your spreadsheets not only visually appealing but also interactive. By adding checkboxes to your spreadsheets you open up a lot of possibilities for functionality that can make your reports more dynamic.

In this tutorial, I will show you how to insert a checkbox in Google Sheets and I will cover some of the useful ways that you can use them in your spreadsheet.

How to Insert a Checkbox

Inserting a check box is a pretty simple process.

Here are the steps:

1. Highlight the cells that you want to add checkboxes to

Insert-checkbox-1

2. In the top menu select Insert

Insert-checkbox-2

3. Then from the drop-down, select Checkbox

Insert-checkbox-3

You will now see that all of the cells that you highlighted in step 1 have a checkbox added to them

Insert-checkbox-4

Data Validation Checkbox

You can also create a checkbox through Data>Data Validation.

The benefit of using data validation for your checkbox is that you have more customization options.

For example, a normal checkbox will equal TRUE when checked, and FALSE when unchecked.

But through data validation, you can control which values are returned if you want something other than TRUE or FALSE to be set when the box is ticked/unticked.

Creating a checkbox through data validation is a similar process. Highlight the range of cells you want the checkboxes added to and then in the top menu select Data>Data Validation

Then set your Criteria to Checkbox

data validation checkbox

After your criteria are set to checkbox you will find the more advanced options of using custom cell values, rejecting input and more.

Using the Checkbox

Using the checkbox is relatively simple. You just click inside the box to tick/check it.

You will notice then when the box is unchecked it is equal to FALSE

false checkbox

Then when you click inside the square and check the box it turns to TRUE

true checkbox

Once you understand the basics of a checkbox returning either TRUE or FALSE, you can begin to add formulas to use these values in creatives ways which we will look at below.

Conditional Formatting with a Checkbox

Once you have your checkboxes created, you can apply conditional formatting to your spreadsheet that can apply formatting to the cells or rows based on whether the checkbox is checked or unchecked.

Here are the steps to apply conditional formatting based on the checkbox:

1. Highlight the range of cells that you want to apply the conditional formatting to

checkbox-conditional-formatting

2. In the top menu select Format

checkbox-conditional-formatting-2

3. In the drop-down select Conditional formatting

checkbox-conditional-formatting-3

4. In the Format rules section set to Format cells if Custom formula is. Then in the box for custom formula, enter the location of your checkbox. In my example, my first checkbox is in A2 so my formula is =$A2. Then you can set the formatting that you want and select Done when you are finished

checkbox-conditional-formatting-4

Now that we have learned how to set up basic conditional formatting using our checkbox, let’s look at some examples of useful ways you can use this skill.

Creating a To-Do List

One useful thing you can do by combining checkboxes and conditional formatting is make a dynamic to-do list that will update as you tick the checkbox.

to-do-list

This is really easy to set up. All I have done is set the formatting to strikethrough and highlight the row green if the checkbox is marked.

Here are the formatting rules for this:

to-do-list-rules

Highlight Entire Row Based on Checkbox

You can also create conditional formatting to highlight an entire row in a report of the box is checked. This can be useful for making your reports easier to read.

Here is an example of what this would look like:

checkbox-highlight-row

The process for doing this is the same, you just create a conditional formatting rule that will highlight the entire rule a certain color based on the checkbox.

checkbox-highlight-row-rule

Filter with a Checkbox

You can combine an IF statement with the FILTER function to use a checkbox to be able to dynamically filter your data at the push of a button.

Checkbox Filter

The formula that I have used to do this is:

=IF(N2,FILTER(A2:E17,B2:B17=M2),IF(N3,FILTER(A2:E17,B2:B17=M3),IF(N4,FILTER(A2:E17,B2:B17=M4),IF(N5,FILTER(A2:E17,B2:B17=M5),FILTER(A2:E17,A2:A17>0)))))

All this formula does is filters my original report based on whether the checkbox is ticked. Whichever box is ticked will control the item that the report is filtered by. This formula does rely on understanding how to use the FILTER and IF functions comfortably, but as you can see this can be a powerful way to create interactive reports.

Closing Thoughts

With a little know-how, checkboxes are incredibly easy to set up in Google Sheets.

By adding these to your spreadsheet you can create dynamic reports and interactive spreadsheets that can allow users to do many things.

If you have followed along this far, hopefully, you can see some of the benefits of using them.

More Google Sheets Tutorials:
How to Create a Dependent Drop Down List
Data Validation
How to Add a Checkmark Symbol