How to Add a Google Sheets Checkbox in 4 Easy Ways ✔️

How to Insert Checkbox in Google Sheets

Fun fact, the checkbox is believed to have originated in the Roman empire. It came from the shortening of the word Veritas, meaning truth, to V. The V later evolved to look like the tick we are familiar with today.

Checkboxes are a good way to keep track of data. 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 a Google Sheets checkbox, along with some useful examples.

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.

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

Inserted Checkbox In Google Sheets

If 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.

Checkboxes in Google Sheets are like texts in a cell which means that you can change their size, color, and even fonts, although they won’t look too different. You can also customize the cell values in the data validation window.

Use custom cell values

How to Use the Google Sheets Checkbox

Once you have inserted a checkbox in Google Sheets, 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 (such as top/bottom 10).
  • Create Interactive charts in Google Sheets.

You can download one of our Google Sheets checkbox templates below.

A sample checkbox sheet

Checkbox template

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

Example 1 – How to Insert Checkbox in Google Sheets With Conditional Formatting to Make a To-Do List

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 applying the strikethrough format.

This can easily be done for a Google Spreadsheet checkbox 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) or use the keyboard shortcut CTRL + Enter for an entire column.
  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 strikethrough 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).

Example 2 – Highlight Data Using Checkboxes and Conditional Formatting

You can also use conditional formatting for Google Sheets checkbox. This makes it easier for you to highlight and analyze data. Let’s say you have a dataset of students’ marks, as shown below.

You can add a checkbox in Google Sheets 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.

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 is 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 conditions are met, a cell is highlighted.

Example 3 – How to Make Check Boxes in Google Sheets and Make an Interactive Chart

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.

How to Check and Uncheck All Boxes In Google Sheets

Once you’ve made the checkboxes you can check and uncheck them all easily with the following steps:

  1. Highlight the column with the checkboxes by clicking on the column header (to do the whole spreadsheet instead, use the Ctrl + A keyboard shortcut).
  2. Press Enter to toggle the checkboxes on or off
    Checking and unchecking in bulk

How to Add Checkboxes in Google Sheets With the Fill Handle

You can create a checkbox in Google Sheets by clicking and dragging the small blue box after you select a cell with a checkbox to copy it into adjacent cells. This is just one of the many tips and tricks you can use on Google Sheets.

Adding more checkboxes with the fill handle

Using Data Validation to Add Custom Values to Checkboxes in Google Sheets

You can add custom values if you use the Data menu to make checkboxes instead of insert. Here’s how you do it:

An example of adding custom values to data validation checkboxes in Google Sheets
  1. Navigate to Data > Data validation
  2. In the menu that pops up change the Criteria to Checkbox
  3. Check the Use custom cell values option
  4. Define what you want the checked and unchecked boxes to represent in your sheet

How to Remove Custom Values From a Checkbox

To remove the custom values all you have to do is go back to the data validation menu and uncheck “use custom cell values”.

Example 4: How To Add Google Sheets Checkbox with App Scripts

Using App Scripts is a good way to create an automated choice for your checkboxes. To do this you can follow the following steps:

Step 1: Access App Scripts in Extensions in the toolbar.

The extensions menu

Step 2: Input the checkbox function in the script. You can copy-paste the following code:

function insertCheckbox(){

var ss=SpreadsheetApp.getActiveSpreadsheet();

var sh=ss.getActiveSheet();

var activeRange=sh.getActiveRange();

Var checkbox=SpreadsheetApp.newDataValidation().requireCheckbox()

.setAllowInvalid(false).build();

activeRange.setDataValidation(checkbox).setValue(true);

}

Script to add checkboxes

Step 3: Save the script and run it.

Run the function

Step 4: now we need to create an autaming button that we’ll use to click and add the checkboxes. There are many ways to do this including adding a new menu. This time however, we’ll use a simpler method by drawing the button. Go to insert then drawing.

Draw a checkbox button

Step 5: Click the three-dot menu on the button you have created, then Assign script.

Edit a checkbox button

Step 6: type the name of the function in the text box that appears. In this case, it’s ‘insertCheckbox’

Assign script to the sheet

Step 7: Now, when you select a cell and click the button, a checkbox will appear in that cell. You can even select multiple cells and click the button to add the checkboxes.

Finished script

If you want to remove a checkbox in a cell, you can just select the cell and click backspace.

And there, you have your automated checkbox button created and customizable in Google App Scripts. Unfortunately, with this method, you won’t be able to input multiple checkboxes in one cell.

Adding Checkboxes in Google Sheet on Android and iOS

You use data validation in the mobile versions of Google Sheets to add checkboxes. To do so:

  1. Highlight the cells you wish to add checkboxes to
  2. Click the three-dot menu
  3. Head to Data validation > Criteria and select Checkbox

How to Hide/Unhide data with Google Sheets Checkbox

A Google Sheets checkbox has a unique use where you can use it to hide data until the checkbox is checked. All you need to do is add the IF formula.

For example, if you want to hide a hint, put a check box on a cell, and on the adjacent cell, add the formula:

  =IF(A4=TRUE, “Text of choice”, )
Add text when box is checked

So now the cell will remain empty, but if you tick the checkbox, it will show you the hidden text.

An example of adding text

Let’s say you have a total sum of a range, and you want to hide some of the data

Summing checked boxes
  • You can create a new row showing the total of only the checked items. In the cell for the total checked type the formula
          =SUM(FILTER(cells with values. Cells with checkboxes))
Adding a filter for summing checked boxes

When you press Enter, you will get N/A in the cell if none of the checkboxes are ticked or the correct total if all of them are ticked.

N/A error

If you tick the checkboxes one by one, you will get the total of the ticked boxes only.

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.

How to Insert Checkbox in Google Spreadsheet FAQ

How Do I Conditional Format a Checkbox in Google Sheets?

Conditional formatting of a checkbox in Google Sheets can be achieved with the following steps:

  1. Highlight the cells with checkboxes you wish to conditionally format
  2. Navigate to Format > Conditional formatting
  3. In the Conditional Formatting menu, set the Format cells if… dropdown menu to Text is exactly
  4. If you want to conditionally format for a checked box type TRUE to conditionally format when unchecked, type FALSE
  5. Choose the formatting you want to apply to the cells

Note: If you have custom values you will need to put whatever they are instead of TRUE or FALSE. You can add another rule by clicking + Add another rule.

How Do You Deselect a Box in Google Sheets?

You just have to click on a selected box to deselect it. To do it in bulk, highlight the appropriate cells and press the spacebar. If you can’t uncheck a box in a shared sheet, it’s likely the range is protected by the sheet owner. If this is the case, you need to get them to add you to the permissions.

Can You Have Multiple Checkboxes in One Cell in Google Sheets?

No, you can’t have several checkboxes in one cell. But you can copy checkboxes into other cells with the fill handle.

Is There a Way to Uncheck All Boxes in Google Sheets?

Yes, you can uncheck all boxes in Google Sheets by pressing Ctrl + A to select the entire sheet, then pressing the spacebar. If this checks all the boxes instead, simply press the spacebar a second time.

How Do I Make the Checkbox Green in Google Sheets?

You can make the checkbox green in by applying Google Sheets checkbox conditional formatting, just follow these steps:

A screenshot showing how to make checkboxes green in Google Sheets
  1. Highlight the checkboxes you want to apply the formatting to
  2. Navigate to Format > Conditional formatting
  3. In the Conditional format rules menu, change the Format cells if… option to Text is exactly
  4. Type TRUE in the Value or formula box
  5. Click Default under Formatting style and change it to Green Text

Can You Make the Checkboxes Behave Like Radio Buttons?

No, you can’t use checkboxes as radio buttons. Radio buttons are used in a list of options to give the user only one option, unlike checkboxes which allow them to select more than one option.

Conclusion

Google Sheets Checkboxes are very convenient and easy to use. You can insert them straight from the insert toolbox, use them with conditional formatting, add them to charts, and even add and modify them on Google app scripts. Hopefully, this guide was enough to show you the ropes on how to add Google Sheets checkboxes.

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

If You Found This Google Sheets Checkbox Article Useful, You May Also Like the Following Tutorials:

Sumit
Spreadsheet Expert at Productivity Spot | + posts

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

Popular Posts

6 thoughts on “How to Add a Google Sheets Checkbox in 4 Easy Ways ✔️”

  1. Is there a way to insert checkbox via formula?
    For example, insert a checkbox when a condition is satisfied:
    =IF(A1=0,”Insert checkbox”, “No insert checkbox”)

    • =IF(A1=0,TRUE,FALSE) would do the trick as the most simple solution, checking the box on the exact amount.
      You can also replace this with; A1=>X (this amount of more); A1X (more than) or A1<X(less than).

  2. How can I sum up values on another cell based on if the checkbox is checked or not? I’m trying to make a budget spreadsheet. I have the total income and i have a list of bills to pay with the amount and a checkbox if they’re paid. When the checkbox is clicked i want to make a formula that subtracts it from the total income so it keeps track of how much is left. Sorry, I’m just a normal person trying to learn how to use sheets!

Comments are closed.

You May Also Like