How to Insert Check Box (Tick Box) in Google Sheets + Practical Examples

Google Sheets has been adding some great functionalities lately. It recently added a way to easily insert a checkbox (tick box) in any cell with a single click.

Note that I will be using the terms ‘Checkbox’ and ‘Tick Box’ interchangeably in this article. While Google Sheets is calling this feature a tick box, it’s more commonly known as a checkbox.

While inserting the checkbox is super easy (as you’ll see later in this article), what’s impressing is the way you can use it.

You can easily create dynamic lists and charts where the user can simply click on the checkbox and you can program your data/charts to update.

I will show you a couple of example of doing this later in this tutorial.

Checkbox Vs Checkmark

Before I get into the mechanics of inserting and using a checkbox/tick box in Google Sheets, let me quickly cover the difference between a checkbox and a checkmark.

A checkmark is a symbol that you insert as text within the cell. It doesn’t have any interactivity and can be used merely as a symbol or a bullet point. You can have text before or after a checkmark.

A checkbox, on the other hand, is interactive. While it is confined to a cell, you can click on it and it will change the state. So if it’s unchecked and you click on it, it will be checked (and vice versa).

Note that you can not have any text before or after a checkbox (tick box) in Google Sheets. You can only have the checkbox and nothing else.

Inserting a Checkbox (Tick Box) in Google Sheets

Below are the steps to insert a checkbox in Google Sheets:

  1. Select the cell in which you want to insert the checkbox (tick box)
  2. Click the ‘Insert’ option
  3. Click on the ‘Tick Box’ option.

The above steps would insert a checkbox in the selected cell. In case you have selected multiple cells, checkboxes will be added to all the selected cells.

Note that in case you have any text or formula in a cell and you insert a checkbox in it, Google Sheets will remove the text/formula and replace it with the checkbox.

Formatting the checkbox

Since a checkbox is a part of the cell, you can format it just like any other cell.

For example, you can apply a color to the cell and the checkbox will change the color from gray to the selected color. Similarly, you can also change the font in case you want bigger checkboxes.

Also, since the checkbox is a part of the cell, you can sort and filter these.

For example, if you have a list of tasks with checkboxes in adjacent cells, you can select the entire range and sort the list alphabetically. This will also sort the checkboxes in the selected range.

How Does the Checkbox in Google Sheets Work

Once you have inserted the checkbox in a cell, you can click on it to check it and click on it again to uncheck it (it works as a toggle).

While you see a checkbox in the cells in Google Sheets, these are actually either TRUE or FALSE.

  • If the checkbox is checked, the value is TRUE
  • If the checkbox is not checked, the value is FALSE

You can actually see this yourself by selecting any cell that has the checkbox and pressing the F@ key (or looking at the formula bar). You will see the value the checkbox represents, which would be either TRUE or FALSE.

Bottom line – when you insert a checkbox, you simply insert TRUE when it’s checked and FALSE when it’s not checked.

The amazing thing about this is that now you can use these TRUE/FALSE values in the formulas. For example, I can have a list of items and I can use the checkbox to mark an item and complete. I can also count the total number of completed items by simply counting the value of TRUEs in the list.

You can do some really cool things with checkboxes in Google Sheets – such as:

  • Create a to-do list and mark tasks as done/complete.
  • Highlight specific data points based on selection (such as top/bottom 10).
  • Create Interactive charts in Google Sheets.

Let’s go through each of these examples in detail and see the different ways of using a checkbox in Google Sheets.

Example 1: Create Interactive To-Do Lists with Checkboxes

With checkboxes in Google Sheets, you can create a to-do list or a checklist where as soon as you click on the checkbox in a cell, it will mark the item as complete.

Below is an example of a completed to-do list with checkboxes.

To do list with a checkbox in Google Sheets

In the above example, you can click on the checkbox to mark an item/task as completed. It uses conditional formatting to highlight the cell with the task in green and apply a strikethrough format to it.

When you check any of the checkboxes, the value in the cell changes to TRUE. This value is then used in Conditional formatting to highlight the cell and apply the strikethrough format.

Below are the steps to create this type of interactive checklist and then highlight the completed tasks/items:

  1. Enter the tasks/items in column A and insert the checkboxes in column B in adjacent cells.
  2. Select the cells in Column A (the ones that have the items in it).
  3. Click the Format button in the menu.
  4. Click the ‘Conditional Formatting’ option.
  5. In the Conditional Formatting pane that opens, click on the ‘Format cells if’ drop-down.
  6. Click on ‘Custom formula is’ option.
  7. Enter the following formula: =$B2
  8. Specify the format (color and the strike-through format).

You can also use a formula to quickly get the count of tasks that are completed. The below formula would give this:

=COUNTIF(B2:B8,TRUE)

Example 2: Highlight Data Using Checkboxes

You can use the checkbox to make your reports/dashboard visually appealing and easy to read.

Below is an example where as soon as you select any of the checkboxes, Google Sheets highlights the data in the table.

Highlight Cells that match a criteria using checkbox

This again uses conditional formatting which is dependent on the value of the cell that has the checkbox. If the value is TRUE, the specified conditional format is applied and if the value is FALSE, the format is not applied.

In the above example, as soon as I select the checkbox for greater than 85, it instantly changes the color of the cells in column B if the value is greater than 85.

In case you want to create something similar, here are the steps:

  1. Insert the checkbox (and specify the criteria for it as text in the adjacent cell)
  2. Select the cells in Column B (the ones that have the marks).
  3. Click the Format button in the menu.
  4. Click the ‘Conditional Formatting’ option.
  5. In the Conditional Formatting pane that opens, click on the ‘Format cells if’ drop-down.
  6. Click on ‘Custom formula is’ option.
  7. Enter the following formula: =AND($E$3,B2>=85)
  8. Specify the format when marks are more than 85 (I have used green color in the above example).
  9. Click Done.
  10. Click on Add New Rule.
  11. Click on the ‘Format cells if’ drop-down.
  12. Click on ‘Custom formula is’ option.
  13. Enter the following formula: =AND($E$4,B2<35)
  14. Specify the format when marks are more than 35 (I have used red color in the above example).
  15. Click Done.

The AND formula used in the steps above checks for two conditions:

  • Whether the cell with checkbox is checked or not (if checked the value is TRUE)
  • Whether the cell has a value that meets the criteria (greater than 85 or less than 35)

When both of these conditions are met, the cells are highlighted based on the value in it.

Example 3: Create Dynamic Charts using Checkbox

In most cases, charts derive their values from the cells in the worksheet.

And by using a checkbox, you can make these cells show dynamic values. This means that the values can update/change based on whether a checkbox is checked or not.

This also allows us to create dynamic charts that are dependent on the checkbox selection.

For example, below I have a dataset where I have the profit margin values of a company for three years.

Data for creating a dynamic chart

I can use the above dataset and combine it with the checkbox functionality to create something as shown below:

Dynamic Chart Using Checkbox in Google Sheets

Note that the above chart updates as soon as I check or uncheck the box for 2019 or 2020F. This type of charts can be really useful when you have multiple data series and you want the user to have the flexibility to only see the data that’s useful for them.

This can be made possible and the chart data is dependent on the cell value of the checkbox. When the checkbox is checked the value is TRUE, else it’s FALSE.

This is then used to create a dataset that is dynamic and only shows values when the corresponding checkbox for checked. If the checkbox is not checked, it doesn’t show the data and the chart doesn’t show the line for it (as shown bel0w).

Making the dataset dynamic and dependent on tick boxes

In the above example, I have created a copy of the original dataset, but this copied dataset is also dependent on the checkbox cell value. So, if the 2019 checkbox is checked, the data for 2019 will be populated in the second dataset, else it will show blank.

And since I am using the second dataset (the one at the bottom) to create the chart, the chart itself becomes dynamic.

The first step in creating something like this is to create the second dataset – one which is dependent on the checkbox.

To do this, I have used the below formulas.

For 2018 data

=B2

For 2018 data, I need all the original data points, as these will always be visible in the chart. So I have used a simple reference to the original data (you can also copy and paste the original data if you want).

For 2019 data

=IF($H$3,B3,"")

The IF formula checks whether the checkbox in cell H3 is checked or not. If it’s is checked, it returns TRUE, and the original data point is returned by the IF formula. But if the checkbox is unchecked, the cell H3 returns FALSE and the IF formula returns a blank cell.

Since blank cells are not plotted in the chart, you don’t see anything in the chart when the checkbox for that series is not checked.

For 2020F data

=IF($I$3,B4,"")

The same logic works for the 2020 data series, making it dependent on the checkbox in cell I3.

Once you have this new dataset that’s dependent on the checkboxes, you can use this to create the chart.

In this example, I am using a combination chart in Google Sheets, where the 2018 value is shown as columns and 2019 and 2020F values as lines.

Copying and Deleting the Checkboxes

Since a checkbox is a part of the cell in Google Sheets, you can copy and paste it just like a regular cell.

When you copy and paste a cell (or range of cells) with a checkbox, it will insert the checkbox in the pasted cells in the same state as the copied one. So if you have a checkbox that checked, it will paste one that is checked.

To delete a checkbox from a cell (or a range of cells), simply select the cells and hit the Delete key.

Also, note that in case you select a cell that has a checkbox and types something in it, the new text will replace it.

You may also like the following Google Sheets articles: