How to Insert Checkbox in Google Sheets (with Examples)

How to Insert Checkbox in Google Sheets

Google Sheets recently added an option to insert a checkbox in a cell.

In this tutorial, I will cover everything you need to know about using checkboxes in Google Sheets, along with some useful examples.

Click here to download the example file and follow along (you will have to make a copy to use it).

Inserting a CheckBox in Google Sheets

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

  1. Select the cell in which you want to insert the checkbox.
  2. Click the Insert option on the menu.Insert checkmark In Google Sheets - insert button
  3. Click the Checkbox option.Insert checkbox In Google Sheets - checkmark option in drop down

The above steps would insert a checkbox in the selected cell.

Inserted Checkbox In Google Sheets

In case you want to insert the checkbox in more than one cell, select those cells and then insert the checkbox. Doing this will insert the checkbox in all the selected cells.

How to Use the Checkbox in Google Sheets

Once you have inserted a checkbox, you can use it by simply clicking on it.

It works as a toggle – when you click on it once, it gets checked, and if you click on it again, it gets unchecked.

But the checkbox is what you see in the cell, in the backend, something else happens:

  • When the checkbox is checked, the cell value becomes TRUE.
  • When the checkbox is unchecked, the cell value becomes FALSE.

checkbox True False

This change in value in the cell means that we 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 (scuh as top/bottom 10).
  • Create Interactive charts in Google Sheets.

Let's go through each of these examples in detail.

Example 1 – Create a To-do List using Checkboxes

Let me first show you what I plan to create.

Below is a to-do list that you may find a project manager use.

Insert Checkbox In Google Sheets - To-Do List

In the above example, as soon as a box is checked in Column B, it changes the color of the corresponding item in column A, as well as apply the strikethrough format.

This can easily be done using conditional formatting. The trick here is to use the TRUE/FALSE value in the cells with checkboxes. If the value is TRUE, it means that the box has been checked, and you can apply the conditional formatting rules to the cell on the left.

Here are the steps to create this:

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

The above technique works as conditional formatting is applied whenever the formula returns TRUE. In this case, since the formula is the cell reference of the adjacent cell, the conditional formatting is applied whenever the checkbox is checked (which in turn makes the cell value TRUE).

Click here to download the example file and follow along (you will have to make a copy to use it).

Example 2 – Highlight Data Using Checkboxes

Let's say you have a dataset of students marks as shown below.

You can use checkboxes to highlight a part of the dataset – such as students who have scored more than 85 in Green and/or students who have scored less than 35.

Something as shown below:

Insert Checkbox In Google Sheets - Marks Highlight

In the above example, the marks data gets highlighted as soon as an option is checked.

Again, it conditional formatting that works in this case.

Here are the steps that will make this happen:

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

The above steps use the formula to check two conditions:

  • Whether the cell value is greater than equal to 85 or not (or less than 35 or not).
  • Whether the checkbox is checked or not.

When both the conditions are met, a cell is highlighted.

Click here to download the example file and follow along (you will have to make a copy to use it).

Example 3 – Creating Interactive Chart With Checkboxes

Suppose I have the profit margin data of a company as shown below:

Insert Checkbox In Google Sheets - Data for charting

I want to plot the data for 2018, and at the same time give the user the ability to be able to plot the data for 2017 or 2019 by using a checkbox (as shown below).

Add Checkbox In Google Sheets - dynamic chart

The trick in creating this is to have a dataset that is dependent on the checkboxes. For example, if I check the 2017 checkbox, I want the data in the dataset to be available, but if it is unchecked, the data should be blank.

To do this, I need to create another data set as shown below:

Insert Checkbox In Google Sheets - chart data copy

Note that I have changed the order in the new data set (2018, 2017, and 2019F), as I need to plot 2018 as the bars and rest as lines.

Now for each year, I need to use a formula.

For 2018:

=B3

This formula simply returns the 2018 values.

For 2017:

=IF($H$4,B2,"")

In this formula, H4 is the cell that has the checkbox for 2017. When this checkbox is checked, the formula returns the value from the original dataset, else it returns a blank.

Insert checkmark In Google Sheets - 2017 formula

For 2019F:

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

In this formula, I4 is the cell that has the checkbox for 2017. When this checkbox is checked, the formula returns the value from the original dataset, else it returns a blank.

Insert checkmark In Google Sheets - 2018 formula

The next step is to insert checkbox in cell H4 and I4.

To do that:

  1. Select cell H4 and I4 (these are the cells where I have the checkboxes in this example, and the cells that I have used in the formulas above).
  2. Click the Insert button.
  3. Click on the Checkbox option.

Now the final step is to create a combo chart using the data that we have created using the formulas.

Here are the steps to create this combo chart:

  1. Select the data.
  2. Click the Insert option in the menu.
  3. Click on Chart.
  4. In the Chart editor pane, change the Chart type to Combo.

That's it!

Now when you check/uncheck the checkbox, the chart would automatically update.

Click here to download the example file and follow along (you will have to make a copy to use it).

Copying / Deleting Checkboxes

In Google Sheets, a checkbox is a part of the cell.

This means that you can copy and delete a checkbox just like you do with any regular cell.

To copy a checkbox, copy the cell and paste it where you want the copy.

Note that a checkbox is linked to the cell that holds it. So if you copy a checkbox from cell A1 to cell A2, both checkboxes will be linked to the cell that has it.

To delete a checkbox, select the cell and hit delete.

Also See: How to Insert and Use a Checkbox in Excel.

You May Also Like the Following Google Sheets Tutorials:

  • >