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.
Table of Contents
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
2. In the top menu select Insert
3. Then from the drop-down, select Checkbox
You will now see that all of the cells that you highlighted in step 1 have a checkbox added to them
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
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
Then when you click inside the square and check the box it turns to TRUE
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
2. In the top menu select Format
3. In the drop-down select Conditional formatting
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
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.
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:
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:
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.
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.
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