Google Sheets features a variety of tools related to data input and validation, such as adding drop-down lists or checkboxes. Checkboxes allow users to select or enable options quickly, simply by clicking on the relevant checkbox. However, the best part is that you can use those choices to trigger other actions. There are many ways to integrate them into your spreadsheet processes, as checkboxes can be used with functions, filters, pivot tables, and charts.
In this guide, you will learn how to insert checkboxes in Google Sheets and how to change the default values to custom ones. Additionally, you will learn to combine checkboxes with other tools and functions. You will learn to apply conditional formatting rules based on checkbox values, as well as how to use these values with other Google Sheets functions, like COUNTIF. To learn more about the COUNTIF function, check out COUNTIF & COUNTIFS in Google Sheets: Formula & Examples.
How to Insert a Checkbox in Google Sheets?
There are two ways to insert a checkbox in Google Sheets: Insert > Checkbox or Data > Data validation. In the next section, you have instructions on using the second option, which allows you to use custom values.
- 1. Select the cell where you want the checkbox to appear.
- 2. Go to Insert > Checkbox.
- 3. The cell now contains an unchecked checkbox, so its value is ‘FALSE’.
- 4. The cell’s value is ‘TRUE’ when the checkbox is checked.
In a later section, you will learn how to use the values of your checkboxes in your Google Sheets functions.
If a Google Sheet spreadsheet requires you to enter the same options over and over, you can make data entry easier by adding a drop down list. Here’s how.READ MORE
How to Add Custom Values to a Checkbox in Google Sheets?
To add a checkbox with custom values, you can use the data validation options. By default, the checkbox values are ‘TRUE’ and ‘FALSE’, but you can change them to whatever you want, like ‘YES’ and ‘NO’.
- 1. Select the cell where you want the checkbox and go to Data > Data Validation.
- 2. You will see the default data validation options.
- 3. For ‘Criteria’, select the ‘Checkbox’ option.
- 4. Check the option to “Use custom cell values”, then type in the new values.
- 5. As you can see, the new value for the unchecked checkbox is ‘NO’.
- 6. When checked, the cell’s new value is ‘YES’.
How to Conditionally Format a Checkbox in Google Sheets?
To use conditional formatting on checkboxes, select the cells with the checkboxes and go to Format > Conditional formatting. Set up the conditions in the sidebar, under Criteria > Format cells if, then choose the format you want to apply under those conditions.
Imagine you have created a to-do list in Google Sheets, using checkboxes to mark tasks as complete. This helps you keep track of tasks, but you want to strike through complete items or change their color so you can focus on what remains to be done. Let’s see how you can do that using conditional formatting.
- 1. Select the cells containing the tasks.
- 2. Go to Format > Conditional formatting.
- 3. Under ‘Format rules’, click on the ‘Format cells if…’ drop-down menu and select “Custom formula is”. For this example, I will use the checkbox status as the condition, so I need to use the following formula: “=$B2”.
- 4. Choose the ‘Formatting style’ you want to apply. For this example, I have selected a green background color and the text strikethrough option.
- 5. That’s it. As soon as items are checked, the chosen format is applied.
Google Sheet allows you to make data more readable by changing the color of cells based on their value. Here's how to use conditional formatting in Google Sheets.READ MORE
How to Use Checkbox Values in Formulas in Google Sheets?
Let’s say that in addition to the conditional formatting applied in the previous section, you also want to see a count of completed tasks. Let’s see how you can do this using the COUNTIF function.
- 1. In an empty cell, type in the COUNTIF formula.
- 2. For the ‘range’, select the cells containing the checkboxes.
- 3. For the criterion, type the boolean value ‘TRUE’ without quotation marks.
- 4. Press ‘Enter’ to see the count of completed tasks.
How to Copy a Checkbox in Google Sheets?
Cells containing checkboxes can be copied just like any other cell: through the ‘Edit’ menu or using shortcuts: Cmd + C (Mac) or Ctrl + C (Windows). You can also copy them by grabbing the fill handle and dragging it in the direction you want.
Adding checkboxes to your Google Sheets is easy, and you know how to do it using two different methods. You also know how to change the checked and unchecked values from the default TRUE/FALSE by adding them via ‘Data validation.
More importantly, you know you can combine checkboxes with other tools and functions and use their values as triggers for other actions. For example, you know how to use conditional formatting to change the color of the cells or strikethrough text based on checkbox values.
Additionally, you also know how to combine checkboxes with formulas, such as using the COUNTIF function to count checked items in a list.