Watch Video – Google Sheets Checkbox
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 use the terms ‘Checkbox’ and ‘Tick Box’ interchangeably in this article. It’s more commonly known as a Google Sheets checkbox, but some call it a tick box.
While inserting the checkbox is super easy (as you’ll see later in this article), how you can use it can be impressive.
You can easily create dynamic lists and charts where the user can simply click on the checkbox and program your data/charts to update.
I will show you a couple of examples of how to put a checkbox in Google Spreadsheets.
A Checkbox in Google Sheets 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 Google Sheets 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 Google Sheets Checkbox (Tick Box)
Here’s a full checkbox Google sheet so you can understand what we’re talking about:
Below is how to add checkboxes in Google Sheets:
- Select the cell in which you want to insert the Google Sheet checkbox (tick box)
- Click the ‘Insert’ option
- Click on the ‘Checkbox’ 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 a Check Mark on Google Sheets
Now that you know how to insert a checkbox in Google Sheets, the next step is to format it.
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.
Google Sheets Checkbox Notes (True, False)
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. So you can think of a tick box as a Google Sheets true false checkbox.
- 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.
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 when you add a checkbox 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: How to Add Check Boxes in Google Sheets for Interactive To-Do Lists
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.
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 is how to insert checkboxes in Google Sheets forthis type of interactive checklist and then highlight the completed tasks/items:
- Enter the tasks/items in column A and insert the checkboxes in column B in adjacent cells.
- Select the cells in Column A (the ones that have the items in it).
- Click the Format button in the menu.
- Click the ‘Conditional Formatting‘ option.
- In the Conditional Formatting pane that opens, click on the ‘Format cells if‘ drop-down.
- Click on ‘Custom formula is‘ option.
- Enter the following formula: =$B2
- 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:
Example 2: Highlight Data Using Google Sheets Add Check Box Functionality
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.
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:
- Insert the checkbox (and specify the criteria for it as text in the adjacent cell)
- Select the cells in Column B (the ones that have the marks).
- Click the Format button in the menu.
- Click the ‘Conditional Formatting’ option.
- In the Conditional Formatting pane that opens, click on the ‘Format cells if’ drop-down.
- Click on ‘Custom formula is’ option.
- Enter the following formula: =AND($E$3,B2>=85)
- Specify the format when marks are more than 85 (I have used green color in the above example).
- Click Done.
- Click on Add New Rule.
- Click on the ‘Format cells if’ drop-down.
- Click on ‘Custom formula is’ option.
- Enter the following formula: =AND($E$4,B2<35)
- Specify the format when marks are more than 35 (I have used red color in the above example).
- 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.
I can use the above dataset and combine it with the checkbox functionality to create something as shown below:
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).
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
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
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 2020 data
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.
Use Data Validation to Add Custom Values to Checkboxes
You can use the checkboxes to indicate values. To do this:
- Right-click on the cell with the checkbox
- Navigate to More cell actions > Data validation
- Make sure the value in the Criteria dropdown menu is set to Checkbox
- Check the Use custom cell values box and input the values you want to use and click Save
You can also use a similar process to add a Google Sheets checkbox formula for conditional formatting to the cells by entering the Conditional formatting menu instead of Data validation.
How to Remove Custom Values From a Checkbox
To remove custom values, you can follow the steps above for adding them but uncheck the Use custom values box instead of checking it.
Can Google Sheets Add a Checkbox in the Mobile App?
This works similarly to in a browser. Here’s how to make a check box in Google Sheets for mobile:
- Select the cells you want to add checkboxes to
- Tap the three dots menu
- Select Data validation
- In the criteria dropdown, select checkbox
Copying and Deleting the Checkboxes
Since a Google spreadsheet 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 Google Sheets checkbox and type something in it, the new text will replace it.
Frequently Asked Questions
Can You Put a Checkbox and Text in the Same Cell in Google Sheets?
No, unfortunately, you can’t.
How Do I Tick Multiple Checkboxes in Google Sheets?
Highlight the cells and press the space bar to tick or untick cells in bulk.
Can You Make a Checklist in Google Sheets?
Yes, once you have a list of items you want to check off, highlight the adjacent cells and navigate to Insert > Checkbox to add checkboxes.
How Do I Use Checkboxes in Conditional Formatting?
You can’t use checkboxes as a result of conditional formatting, but you can do the opposite. Say you have a checkbox in B2 and want C2 to highlight green when the checkbox is checked. In the conditional formatting menu for C2, you can set the rule to Custom formula is and use the formula =$B2. This Google Sheets “if checkbox is checked” formatting is handy for to-do lists.
Can You Create a Select All Checkbox?
You can simply select the row or column headers containing checkboxes to select them all at once. If they are over several rows or columns, you can hold Ctrl (Cmd on macOS) to select more than one header. Then you can check or uncheck them all at once by pressing the space bar.
Can You Have Multiple Checkboxes in One Cell in Google Sheets?
No, you can’t te cell needs to be able to return a single TRUE or FALSE value, which it would not be able to do if there were two boxes in a single cell.
You may also like the following Google Sheets articles:
- How to Insert BULLET POINTS in Google Sheets
- How to Insert a Degree Symbol in Google Sheets
- How to Indent Text in Google Sheets
- How to Wrap Text In Google Sheets
- How to Delete Empty Rows in Google Sheets
- How to Create a Drop-Down List in Google Sheets?
- How to Insert an Image in a Cell in Google Sheets