You want to make your spreadsheets more interactive and dynamic? Checkboxes in Google Sheets are the perfect solution! In this comprehensive guide, I’ll show you how to insert, format, and use checkboxes in Google Sheets to enhance your working experience.
What’s the difference between a tick and a checkbox?
Before diving into the details, it’s important to understand the difference between a tick and a checkbox in Google Sheets.
A tick is a symbol that you insert into a cell as text. It has no interactivity and can be used simply as a symbol or a visual marker. You can have text before or after a tick. It mainly serves as a formatting tool.
On the other hand, a checkbox is interactive. Although it is confined to a cell, you can click on it to change its state. If it is unchecked and you click on it, it will be checked (and vice versa). According to the United States Web Design System (USWDS), checkboxes are a fundamental element of modern web design.
How to insert a checkbox in Google Sheets
Inserting a checkbox into your Google Sheets spreadsheet is simple.
- Select the cell(s) where you want to insert the checkbox.
- Click on the “Insert” menu at the top of the window.
- From the drop-down menu, click on “Checkbox”.
The interactive checkbox will automatically be added to the selected cell(s). If you have selected multiple cells, a checkbox will be added to each of them. Note that if you have text or a formula in a cell and insert a checkbox, the text or formula will be replaced by the checkbox.
For a video demonstration on adding a checkbox in Google Sheets, watch the video below:
How to format a tick in 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 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 from gray to the selected color. Similarly, you can also change the font if you want to have larger checkboxes. One common formatting option is conditional formatting. This way, you can automatically highlight all checked checkboxes in green.
Using checkboxes in conditional formatting, sorting, and filtering data
Adding color to checkboxes is easy with conditional formatting, but that’s not the only way to sort data. Since the checkbox is part of the cell, you can sort and filter them.
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 within the selected range. This is how I built my task list templates.
Creating a True and False checkbox in Google Sheets
It may seem strange, but checkboxes in Google Sheets are either TRUE or FALSE:
- If the checkbox is checked, the value is TRUE.
- If the checkbox is unchecked, the value is FALSE.
You can use these TRUE/FALSE values in formulas. For example, you can list items and use the checkbox to mark an item as completed. You can also count the total number of completed items by simply counting the “TRUE” values in the list.
Checkboxes in Google Sheets offer many potential uses:
- Create a to-do list and mark tasks as completed.
- Highlight specific data points based on selection (e.g., top 10).
- Create interactive charts in Google Sheets.
Here are some powerful examples of using checkboxes in Google Sheets.
1. Add checkboxes to create interactive to-do lists
Using checkboxes to create a to-do list is easy. As soon as you click on these checkboxes, the item is marked as completed.
To highlight the checked checkboxes, I used conditional formatting to display them in green and apply a strikethrough text format.
Here’s how to do it:
- Enter the tasks/items in column A and insert checkboxes in column B (in adjacent cells).
- Select the cells in column A.
- Select “Format” > “Conditional formatting” from the drop-down menu.
- Click on “Format cells if” > “Custom formula is”.
- Enter the following formula:
=$B2(which selects all cells containing a checkbox). - Specify the format (color and strikethrough text format).
2. Highlight data with checkbox functionality
You can use the checkbox to make your reports visually appealing and easier to read.
In this example, Google Sheets will highlight the data in the table once you select a checkbox. In this example, the conditional formatting depends on the value of the cell containing the checkbox.
If I select the checkbox for “>85”, it instantly changes the color of the cells in column B that are greater than 85.
Here’s how to do it:
- Add the checkbox (and specify the criteria as text in the adjacent cell).
- Select the cells in column B (with the checkboxes).
- Select “Format” > “Conditional formatting” > “Format cells if”.
- Click on “Custom formula is” and enter the following formula:
=AND($E$3,B2>=85). - Specify the format when the values are greater than 85 (I used green in my example).
- Click on “Done”.
- Click on “Add another rule” > “Format cells if” > “Custom formula is”.
- Enter the following formula:
=AND($E$4,B2<35). - Specify the format when the values are less than 35 (I used red in my example).
- Click on “Done”.
The AND function (used in the above steps) checks two conditions:
- If the cell containing a checkbox is checked or not (i.e., if it is checked, the value is TRUE).
- If the cell has a value that meets the criteria (i.e., greater than 85 or less than 35).
When both of these conditions are met, the cells are highlighted based on their values.
3. Create dynamic charts using checkboxes
Charts get their values from the cells in the spreadsheet, and checkboxes hold values. This means we can use checkboxes to affect the appearance of our charts. Isn’t that great?
This means we can create charts that show dynamic values. You can update or modify a chart based on the checkbox. This means you can create a dynamic chart in Google Sheets. Here’s how.
I have a set of data showing a company’s profit margins over three years.
I can use the above data set and combine it with checkbox functionality to create a chart.
These types of charts are useful for multiple data series, as well as presenting the most relevant data to end users.
Note that I have created a dynamic data set that only shows values when the corresponding checkbox is checked. If the checkbox is unchecked, the data does not appear, and the chart does not display the corresponding line either.
The first step is to create a second data set that depends on the checkboxes.
For the 2018 data:
=B2
I will need all the original data points, as they will always be visible in the chart. In this example, I used a simple reference to the original data.
For the 2019 data:
=IF($H$3,B3,"")
The IF formula checks if the checkbox in cell H3 is checked or not. If it is checked, it returns TRUE, and the original data point will be returned by the IF formula. If the checkbox is unchecked, cell H3 returns FALSE (and the IF formula returns an empty cell).
Empty cells are not plotted in the chart. Therefore, if the checkbox for that data series is unchecked, you won’t see anything in the chart.
For the 2020 data:
=IF($I$3,B4,"")
The same logic applies to the 2020 data series. It depends on the checkbox in cell I3.
Once you have a new data set that depends on the checkboxes, you can use it to create the chart.
In this example, I used a combined chart in Google Sheets:
- The 2018 value is represented by columns,
- The 2019 and 2020 values are represented by lines.
How to use data validation to add custom values to checkboxes
You can use checkboxes to indicate values:
- Right-click on the cell containing the checkbox.
- Go to “Data validation” > “More cell options”.
- Make sure the criteria dropdown value is set to “Checkbox”.
- Check the box “Use custom cell values”. Enter the values you prefer (e.g., color, font). Click “Save”.
How to remove custom values from checkboxes
To remove custom values, follow the same steps as for adding them, but uncheck the box “Use custom cell values”.
Can I add a checkbox in the Google Sheets mobile app?
Yes, it is possible to use checkboxes in the Google Sheets app on Android and macOS. Here’s how to add a checkbox in the Google Sheets app:
- Select the cell(s) where you want to add checkboxes.
- Tap on the three-dot menu.
- Select “Data validation”.
- In the criteria dropdown menu, select “Checkbox”.
Please note that Google’s support also mentions adding checkboxes in Google Docs.
How to copy and delete checkboxes in Google Sheets
Google checkboxes can be copied and pasted like any other cell. To delete a checkbox from a cell (or range of cells), simply select the cells and press the Delete key.
Note: If you select a cell containing a Google checkbox and type something in it, the new text will replace it.
Ideas for formatting checkboxes in Google Sheets
Since a checkbox is part of the cell, you can format it just like any other cell. For example, you can use conditional formatting rules to apply a color to the cell, changing the checkbox from gray to a color of your choice. You can also change the font if you want to have larger checkboxes.
Suppose 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 within the selected range.
Frequently Asked Questions
As always, here are some of the most common questions I hear on the topic. Take a look and let me know in the comments if I missed anything!
What’s the difference between a tick and a checkbox?
There is a slight difference between a tick and a checkbox:
- A tick in Google Sheets is a symbol inserted as text in cells. It has no interactivity and can be used as a symbol or visual marker.
- A checkbox is interactive and is confined to a cell. In Google Sheets, a checkbox cannot have text before or after it.
Can you have a checkbox and text in the same cell in Google Sheets?
No, you cannot. A checkbox occupies the entire cell and essentially holds a TRUE or FALSE value. You cannot add other data to the same cell, but you can modify your formatting to give the impression that there is text in the same cell.
How can you check multiple checkboxes in Google Sheets?
Select the cells and press the space bar to check or uncheck multiple checkboxes at once.
Can you create a checklist in Google Sheets?
Yes, once you have a list of items, select the adjacent cells and go to “Insert” > “Checkbox” to add them.
How to use checkboxes in conditional formatting?
Let’s say you have a checkbox in B2 and you want C2 to be highlighted in green when the checkbox is checked. In the conditional formatting menu for C2, set the rule to “Custom formula is” and use the formula =$B2. This formatting option is incredibly handy for to-do lists.
Can you create a “Select All” checkbox?
You can select the header row or column headers containing checkboxes to select them all at once. If you want to highlight multiple rows or columns, there’s a keyboard shortcut: hold down the Ctrl key (Cmd on macOS) to select them. Then, check or uncheck the checkboxes by pressing the space bar.
Can you have multiple checkboxes in one cell in Google Sheets?
No, you cannot have multiple checkboxes in one cell. The cell needs to be able to return a single TRUE or FALSE value, which is not possible if two checkboxes coexist in one cell.
In conclusion
A checkbox in Google Sheets offers many potential uses and can quickly enhance your spreadsheets and spreadsheet templates. At bolamarketing.com, we are committed to developing your skills. Check out one of our articles on Google Sheets to get started!
