How To Use Checkboxes In Google Sheets

Last Updated on January 10, 2024 by Jake Sheridan

In this guide, you will learn all about checkboxes in Google Sheets.

How to Insert Checkbox/Tick Box in Google Sheets

Checkboxes are a great way to add a bit of interactivity to your Google Sheets spreadsheets.

Using a checkbox, you can quickly set a cell’s value to either TRUE (when checked) or FALSE (when left unchecked).

This makes it a useful addition to documents such as a to-do list or a tracker.

Here’s a quick guide on how to insert a checkbox into your Google Sheets document:

Step 1

Open your Google Sheets document where you want to insert checkboxes.

In this example, we have a tracker that lists down the steps we need to take when planning a certain event.

A checkbox would be useful here since it will allow us to quickly check off tasks once they’ve been completed.

Step 2

Select the cell or range of cells where you want to insert the checkboxes.

For instance, we’ll select cells B2 to B7 in our sheet to insert checkboxes into the Completed? Column of our tracker.

Step 3

Click on Insert in the menu bar at the top of your screen.

In the dropdown menu under Insert, select Checkbox. This will immediately insert unchecked checkboxes in the selected cells.

Step 4

Once the checkboxes are inserted, you can click on any checkbox to check or uncheck it.

How to Use Checkboxes in Google Sheets

Once you’ve added a checkbox into your sheet, you should now be able to use it right away.

By default, Google Sheets adds unchecked checkboxes into your selected range.

These unchecked boxes hold a value of FALSE.

When the user clicks on the checkbox, the checkbox changes appearance to have a check mark symbol.

The value held within the cell also changes to TRUE.

While checkboxes are already a great visual indicator, the fact that they are boolean values also makes them useful when used with formulas.

For example, we can create a formula that outputs “Complete” when the checkbox is checked and outputs “In Progress” when the checkbox is unchecked.

We can set one up in our sample sheet using the following formula:

=IF(B2,”Completed”,”In Progress”)

In this formula, the state of the checkbox in cell B2 controls the output of the IF function.

If you want to copy the value of a checkbox, you can treat it as any regular cell value in Google Sheets.

You can even use the Fill Handle to copy a checked checkbox to fill any number of cells.

How to Delete Checkboxes in Google Sheets

While checkboxes are a useful feature, you may want to remove them later on.

Perhaps you want to archive a tracker and wish to convert the checkbox elements into their equivalent TRUE or FALSE values.

Or you may want to just delete the checkbox from a cell altogether.

Here’s how you can delete checkboxes in Google Sheets:

Step 1

Select the cells containing checkboxes that you would like to remove.

Step 2

Hit the Delete or Backspace key on your keyboard to delete all checkboxes in that range.

Step 3

If you want to remove the checkboxes but retain their corresponding TRUE/FALSE values, you can do so by removing the data validation from the range.

First, select the cell or cell range containing the checkboxes you wish to convert into TRUE/FALSE values.

Select Data > Data validation to access the Data validation rules panel.

Hover your cursor over the Checkbox rule and click on the Trash bin icon.

How to add Custom Values to checkbox in Google Sheets using Data Validation

As mentioned earlier, cells with checkboxes hold either a TRUE or FALSE value.

However, in some cases, you may want to use other values instead.

For example, you may want values like “Completed” and “Incomplete” for a to-do list.

This option could be useful when you have multiple columns with checkboxes and you want to differentiate the values outputed by checking or unchecking a cell in these different columns.

We can use the Data Validation feature to add custom values to our Google Sheets checkboxes.

Here’s how you can do this:

Step 1

Select the range of cells containing checkboxes that you want to add custom values to.

Step 2

Next, click Data > Data validation to access the Data validation rules panel.

Step 3

Click on the Checkbox rule that applies to your range.

Check the option labeled “Use custom cell values”

Provide the custom values you wish to add for the Checked and Unchecked states of your checkbox.

Click Done to proceed.

Step 4

The checkboxes in your selected range should output the custom values instead of the default TRUE/FALSE values.

How to Remove Custom values

Here’s how you can remove custom values in checkboxes:

First, select the range you wish to remove custom values from.

Next, right-click on the selection and choose the Data Validation option.

In the Data validation rules panel, uncheck the Use custom cell values option.

Click on Done to apply these changes.

Your selected range of checkboxes should now go back to returning TRUE or FALSE.

Use Checkboxes to Create Dynamic Charts

We can use checkboxes as an interactive element that will allow users to select what data to show in a chart.

This will allow us to create a dynamic chart that will show exactly what we need to compare or track.

To create a dynamic chart with checkboxes, follow our simple example below.

Step 1

Set up your data so that the data series you want to toggle are placed horizontally in separate rows.

Create a new column containing checkboxes for you to click on.

Step 2

Next, create a new table containing the same headers.

We’ll use the FILTER function to return all rows in our original range that contain a checked checkbox.

In our example above, we’ll use the formula =FILTER(B2:N5,A2:A5=TRUE) to return all rows in the range B2:N5 with a checked checkbox in column A.

Step 3

Next, we’ll check all rows for now and select our second table.

Click Insert > Chart to create a new chart from our second table.

In this example, we’ll use a line graph to visualize each row of our table.

Step 4

Now, when we uncheck certain rows, our chart will automatically update to include only the data we’ve selected.

Using Checkboxes with SUM function

We can use checkboxes to determine which cells in a range will be included in a calculation of a sum.

Here’s how you can use checkboxes in Google Sheets to control what values to add to a sum:

Step 1

Open your Google Sheets document containing the range you want to sum up based on a column of checkboxes.

In this example, we have aggregated sales data for various regions.

We want to find the sum of all values in the sales column where the corresponding checkbox is selected.

Step 2

Select an empty cell where you want to output the sum.

In this example, we’ll output our sum in cell E2.

Step 3

We’ll use the SUMIF function to return the sum of all values in the range B2:B9 where the corresponding cell in column C has a checked checkbox.

Using the SUMIF function, we can compare the range containing our checkboxes with the boolean value TRUE.

Since ticked checkboxes are equivalent to TRUE, we now have a way of selecting which cells in our sum range to add up.

In our example above, we’ll use the formula =SUMIF(C2:C9,TRUE,B2:B9).

Step 4

Now, only values with corresponding ticked checkboxes will be included in the sum of the range.

Using Checkboxes with Conditional Formatting to Create a To-Do List

You can combine the interactivity of checkboxes with the power of conditional formatting to create a visually appealing and effective to-do list.

In this section, we’ll go over how to add conditional formatting to a range so that clicking on a checkbox will add strikethrough formatting:

Step 1

Open your Google Sheets document and select the cell or cell range where you want to add a checkbox.

Determine the range you want to add conditional formatting to.

In our current example, we want to add checkboxes in column B that when checked will automatically add formatting to the corresponding task in column A.

Step 2

After selecting your range, click on “Insert,” then select “Checkbox” from the dropdown.

Checkboxes should now appear in each cell in your range.

Step 3

Select the range containing your tasks then click Format > Conditional formatting

Step 4

In the Conditional Format Rules panel, under the “Format cells if” dropdown, select “Custom formula is.”

If the checkbox for your first task is in cell B2, enter the formula =B2=TRUE.

Under the Formatting style section, choose the strikethrough option.

Click Done to apply the conditional formatting rule to the selected range.

Step 5

The to-do list should now add a strikethrough formatting to tasks when the user clicks on a checkbox.

 

How do I add a Checkbox counter in Google Sheets?

When working with tables with columns consisting of checkboxes, you may want to count the total number of checked checkboxes.

Here’s how to add a checkbox counter in Google Sheets.

Step 1

Open your Google Sheets document containing a range of cells with checkboxes that you want to count.

In our current example, we want to count the number of checked checkboxes in the range C2:C17.

Step 2

Click on the cell where you want the count of checked checkboxes to appear.

This could be any empty cell where the result will be displayed.

In the image above, let’s select cell E2 for displaying the count.

Step 3

In the selected cell, we’ll use the formula =COUNTIF(<checkbox range>, TRUE).

This formula counts the number of cells, where the tick box is checked.

In our current example, we’ll use the formula =COUNTIF(C2:C17, TRUE).

Step 4

Press Enter.

The cell will now display the number of checkboxes that are checked in the specified range.

This total will update automatically when you check or uncheck boxes in the specified checkbox column range.

How To Add Conditional Formatting to a Checkbox in Google Sheets

To add conditional formatting to a checkbox in Google Sheets, you can use the Conditional Formatting feature based on whether the checkbox is checked (TRUE) or unchecked (FALSE).

First, select the cells containing the checkboxes.

For instance, if your checkboxes are in column A from B2 to B9, highlight these cells.

Next, go to Format in the menu, then select Conditional formatting.

This opens the Conditional Formatting rules panel on the right side of your screen.

In the rules panel, under the “Format cells if” dropdown, choose Custom formula is.

Enter the formula =B2=TRUE for formatting the cells when checkboxes are checked. This formula checks if the cell in column B is TRUE.

Lastly, we’ll need to choose a formatting style to apply.

Select the formatting style you want to apply when the condition is met (e.g., changing the background color, text color, or adding bold/italic styles).

After setting the format, click Done.

In the example above, checked checkboxes will now be highlighted in green after applying our custom conditional format rule.

How do you sum if a checkbox is checked in Google Sheets?

To add up all values in a range based on whether a corresponding checkbox is checked, you can use the SUMIF function.

The SUMIF function allows you to sum cells that meet a specific condition.

In the case of checkboxes, the condition would be that the checkbox is checked (i.e., the cell value is TRUE).

You would write a formula like this:

=SUMIF(B2:B, TRUE, A2:A)

In this formula:

  • B2:B is the range of cells with checkboxes.
  • TRUE is the condition that checks if the checkbox is ticked.
  • A2:A is the range of cells that you want to sum.

This formula will add up all the values in column B where the corresponding checkbox in column A is checked.

How to get checkbox value if checked?

To determine if a checkbox is checked in a formula in Google Sheets, you can directly reference the cell containing the checkbox.

In Google Sheets, a checkbox is a special form of data validation that returns either TRUE when checked or FALSE when unchecked.

You can use this property in various formulas to perform different actions based on the checkbox’s status.

For example, if you have a checkbox in cell A1 and you want to perform a calculation or return a value based on whether this checkbox is checked, you could use a formula like:

=IF(A1, “Checked”, “Unchecked”)

You can replace the “Checked” and “Unchecked” outputs with any other values or formulas as needed.

Summary

Hopefully this guide has given you an overview of working with checkboxes in Google Sheets.

What’s Next?

Explore some of the other useful resources on Sheets for Marketers: