Google Sheets Checkbox: How to Make Your Sheets More Interactive

Google Sheets Checkbox is an interactive element that allows the user to make a binary choice. With this option, you can convert a cell to a check box. By default, the checkbox is unchecked, and the value is FALSE. You can click the cell to change the state. When the checkbox is checked, the cell value is TRUE.

Using the checkbox, you can toggle cell value between TRUE or FALSE. So, you can create interactive spreadsheets using the Google Sheets checkbox.

Let’s learn more about the Google Sheets checkbox using a few examples.

How to insert a checkbox in Google Sheets

It’s easy,

  • Select the cell or range of cells you need to add a checkbox.
  • Go to Insert and select the Checkbox.

How to format checkbox

Checkbox work like a letter inside a cell.

  • Change cell color – use the Fill color tool
  • Checkbox outline – use the Text color tool
  • Size of the checkbox – use the Font size tool

How Google Sheets Checkbox works

A checkbox allows the user to make a binary choice. Google Sheets Checkbox has two states, checked or unchecked. The default checkbox is unchecked. The value of the cell with the checkbox is “TRUE” if it is checked, FALSE otherwise.

A single click on the checkbox toggle the value of the checkbox. You can see the cell value in the formula bar. Watch the above video to how it works.

How to use conditional formatting with Checkboxes in Google Sheets

With Google Sheets Conditional formatting, you can format the text or background color of cells if they meet certain conditions. You can attach this conditional formatting to the Google Sheets checkbox and format cells depending on the checkbox’s value dynamically.

Apply formatting to a cell, cells, or entire row based on the checkbox value

You can apply formatting to a cell, range of cells, or the entire row based on the value (TRUE or FALSE) of the cell containing the checkbox.

To apply formatting,

  1. Select the range of cells you want to format
  2. Go to Format and select Conditional formatting
  3. In the Format Rules, for Format cells if… dropdown, select the “Custom formula is” option
  4. Let’s say, your checkboxes are in the column “D” and row 1 is the header row. Then type =$D2 for the Value for formula box.

I will explain the above using the following examples.

Example 01: Create a To – Do list using Google Sheets Checkbox

A to-Do List is a list of the tasks that you have to do or things that you want to do. By adding a checkbox to the To-Do list created in Google Sheets, you can easily mark your completed items. And you can add different colors and strike through the text. That way, you can easily track what you have completed and what is remaining to do.

To create the To-Do list,

The following video demonstrates the steps to create a To-Do list in Google Sheets using the checkbox. You can continue reading if you need further information.

  1. Add your to-do items in column B; In this example, the first row is used for the column heading.
  2. Insert the checkbox in column A.
    • Select the range of cells in column A (next to each To-Do item in the list)
    • Go to Insert and select ☑ Checkbox.
  3. Using mouse or keyboard, select the list of To-Do items in column B.
  4. Go to, Formats and select Conditional formatting, it opens the conditions formatting options in the sidebar. Make sure you have selected the Single color tab.
  5. [Optional] Under the “Apply to range” remove the current last row number of the cell range (e.g. B2:B). That way, you can apply formatting to new to-do items added below.
  6. Go to the Format rules section.
    • For the Formula cells if… dropdown, choose the option “Custom formula is“.
    • In the Value or formula box, type =$A2. (For the conditional formatting formula, you only need to write the function for the first row)
    • Under the Formatting style, select the Strike through button with one click.
    • Select the color you want to display once the To-Do item is completed using the Fill color tool.
  7. Click Done.
  8. Now you see your formatting is applied to the list items when the checkbox is checked.

Example 02: Apply Google Sheets conditional formatting across the entire row based on the checkbox

You can also apply Google Sheets conditional formatting to the entire row by making a slight change to the steps mentioned above.

When creating the to-do list, we applied the formatting to one column only. So, that formatting was applied to the relevant cell of that column only. By expanding this to multiple columns, you can apply the formatting to the rows inside those columns.

The below video demonstrates steps to add conditional formatting to your data table, so that the entire row is highlighted once you click a checkbox.

YouTube player
  1. Select the range of cells you want to add a checkbox. Go to Insert and select ☑ Checkbox.
  2. Go to Format, then choose Conditional Formatting.
  3. Make sure you have selected the Single color tab.
  4. Type the range you need to apply the formatting for the Apply to range field in A1 notation. In this example, I use A2:Q78.
  5. Under the Format rules section
    • For the Format cells if… dropdown, select “Custom formula is“.
    • For the Value or formula box type =$Q2. You can also type =$Q2=TRUE. Both yield the same results.
    • Then you can change the formatting using the Formatting style box.
  6. Click Done.

In the above example, =$Q2 refer to the value in the Q2 cell. When the checkbox is checked, the cell value is TRUE. Then the function =$Q2 is true, and it applies the relevant formatting to the range of cells in Row 2. Google Sheets automatically copy down these formattings to all the rows in the selected range.

Example 03: Highlight multiple rows based on conditions attached to checkboxes in Google Sheets

You can also highlight multiple rows using multiple conditions with Google Sheets conditional formatting. In this example, I will show you how to use Google Sheets conditional formatting with a checkbox to highlight multiple rows of your data set.

For this example, I will use a list of books with ratings, number of reviews, price, and number of pages.

We will add three conditional formatting rules to highlight rows that fulfill the conditions shown in the below image.

Google Sheets Checkbox

You can make a copy of the Google Sheets used in this example from this link.

How to use multiple conditions inside Google Sheets Conditional Formatting

In previous examples, we checked only one condition, which is the value of the checkbox. In this example, we need to check the value of the checkbox and the other conditions mentioned in the above image. In this specific example, all the conditions must be TRUE to apply formatting.

You can use Google Sheet AND function to combine all these functions as below.

=AND(Condition1,Condition2,Condtion3)

The below video demonstrates steps to apply conditional formatting to your data set so that matching rows will be highlighted when you click the checkbox. You can continue reading if you need further information.

YouTube player
  1. Insert checkboxes in front of your conditions, as shown in the above screenshot.
  2. Go to Format and select Conditional Formatting.
  3. For the Apply to range field, type the data range in A1 notation. In this case, it is A4:F274.
  4. Under the Format rules section
    • For the “Format cells if…” choose the “Custom formula is” option from the dropdown list.
    • To highlight rows with price < 50, type =AND($I$4,$F4<50) in the Value or formula box.
    • Under the Formatting style, you can choose your colors and other formatting styles.
  5. Then click Done to apply formatting for condition “price < 50”.
  6. To add another formatting rule, Go to the formatting rule sidebar again and click +Add another rule link located just below the rule you created above.
  7. Repeat the above 3,4, and 5 steps to add other rules with the following changes.
    • To highlight rows with Rating > 4, use =AND($I$5,$A4>4) for the Value or formula box
    • To highlight rows with Price <50 and Rating >4, use =AND($I$6,$F4<50,$A4>4) for the Value or formula box.

Filter data using Google Sheets checkbox

In Google Sheets, you can use the FILTER function to get a filtered list of your data. The FILTER function takes the dataset and returns only the rows that meet certain criteria you specified.

You can use the FILTER function with Checkbox to make Google Sheets more interactive. You can attach the FILTER function to the Checkbox so that you can toggle the Checkbox to get a list of data that meets a certain set of criteria.

Example 04: Dynamically generate a separate table from rows with selected checkbox

Sometimes you may need to create a separate table from selected rows of your dataset. Copying and pasting are sometimes not easy in situations where you need to pick many rows, and if they are not in any order.

For such cases, you can use Google Sheets Checkbox to tick the required rows. Then write a FILTER formula to filter only rows whose checkbox value is TRUE dynamically.

The following steps explain how to do it.

  1. Select the column next to your dataset. Go to Insert and select the checkbox.
  2. Type the following equation in a cell that you need to appear in your selected rows.
    =FILTER(A3:F273,G3:G273=TRUE)
    Here, A3:F273 is the data range, and G3:G273 is the column with checkboxes.
    Note: You should have the same number of free columns as your dataset to the right of the cell you type the formula.
  3. Now select the checkboxes and you will your selected rows are copied to the new place.

Watch the demo below.

Example 05: Use Google Sheets Checkbox to filter data

You can attach a set of filter functions to the Google Sheets checkbox. So, when you select a checkbox or multiple checkboxes, it will output a list of rows that meets the set of conditions attached to these checkboxes.

In this example, I will show how to integrate FILTER function and IF function together with Google Sheets Checkbox to create an interactive filter option for your dataset.

The following steps explain how to do it.

Google Sheets Checkbox - Interactive Filter Option
  1. Prepare the worksheet, as shown in the above image. There, I have included two checkboxes in cells I2 and K2. The source data range is A4:F274.
    Make sure; you do not have any other contents after the 4th row in columns H, I, J, K, L, and M.
  2. Type the formula given below in the H4 cell.
=IF(AND(I2=true,K2=true),FILTER(A4:F274,A4:A274>4,F4:F274<20),
IF(AND(I2=true,K2=false),FILTER(A4:F274,A4:A274>4),
IF(AND(I2=false,K2=true),FILTER(A4:F274,F4:F274<20),"Please select criteria")))

In the above formula,

First, we check whether both checkboxes are checked. If it is, the function will filter data based on both conditions. If not, the function checks whether the I2 cell is selected. If it is selected, the function will filter the data based on one condition. If that cell is also not selected, the function will look for the other. If both the cells are not selected, the function requests the user to select criteria.

Watch the demo below.

You can further extend this with multiple criteria.

Make interactive charts with the Google Sheets checkbox

When creating charts with multiple series or with large data sets, the carts may get cluttered. This makes it harder to focus on important things. In such cases, it is useful if you can turn on or off some parts of the data set, or some series from the chart.

You can extend the above Example 04 or Example 05 to create dynamic charts with the Google Sheets checkbox in such cases.

Example 06: Toggle Charts series using Google Sheets Checkbox

In the following video, I have created a graph to visualize world population data over several years in each country. If we put all the countries together in the chart, we cannot see the changes clearly.

Therefore, I have created a separate table, as explained in Example 04, and created the chart from that table. Then we can toggle the checkbox to turn on and off the series from the chart.

In this example, I have used the following function to create a separate table from the selected checkbox.

=FILTER(A2:N218,N2:N=TRUE)

Where, A2:N218 is the source data range. N2:N is the column with checkboxes.

In this example, we used a checkbox to filter series in charts. You also use other types of conditions to filter chart data, as explained in Example 05.

Wrapping Up

The checkbox in Google Sheets is an interactive element that allows the user to make a binary choice. You can attach multiple functions, conditions, and formatting to checkboxes and make Google Sheets more interactive. In this post, I explained multiple ways that you can use Google Sheets Checkbox with examples.

The above examples or combinations of them can be used for many purposes. For example, the To-Do list example can be used to create, attendance sheets, or can be improved to track your projects. You can even develop them to send email notifications to others using Google Apps Script. As such, these examples will provide you with the basics of many complex uses of the Google Sheets checkbox.

2 thoughts on “Google Sheets Checkbox: How to Make Your Sheets More Interactive”

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share via
Copy link