A Google Sheets checkbox can be incredibly useful for making data input simpler and more intuitive as well as controlling your data and formulas.
As you may have seen here, drop-down menus in Google Sheets allow for more versatility to the user in filling up the spreadsheets with data while taking less time as they do not need to manually type the entire information.
Sometimes, however, we want a quicker way for cases where there are only strictly two options. For this, we can use add checkboxes in Google Sheets.
How to Insert Checkbox in Google Sheets
The procedure to insert a Google Sheets checkbox is straightforward, simply follow these steps:
Step 1: Select the cells where you want check boxes inserted.
Step 2: Click Insert on the menu.
Step 3: Click Checkbox from the dropdown menu.
Google Sheets will now insert a checkbox in each of the selected cell.
How to Use a checkbox in Google Sheets
After adding a Google Sheets checkbox, you can use it by simply clicking to check and uncheck it. It acts like a toggle switch in the Google Sheets spreadsheet, but something important is happening behind the scenes.
When the checkbox is unchecked, Google Sheets reads the cell value as FALSE.
When the checkbox is checked, Google Sheets reads the cell value as TRUE.
This TRUE/FALSE dichotomy is important if you want to use checkboxes in formulas or more advanced Google Sheets actions. However, if checkboxes are a visual cue in your spreadsheet, the TRUE/FALSE values are less important.
Checkboxes can be useful for:
- controlling chart content
- controlling contents of functions
- creating interactive lists
How to Delete Checkboxes in Google Sheets
Sometimes when you add a checkbox in an incorrect cell or you no longer need a checkbox in certain cells, you need to remove checkboxes.
To delete checkboxes in Google Sheets, follow these simple steps:
- Select the cells where you would like to delete a checkbox.
- Click Edit, then roll over the Delete option.
- From the second menu, select the option for "Values."
Now you have removed your unnecessary checkboxes. You can also hit delete or the backspace on your keyboard to delete a checkbox from only one cell.
How to add Custom Values to checkbox in Google Sheets using Data Validation
Although the default values for a checkbox in Google Sheets are TRUE and FALSE, you can also use custom cell values with the Data Validation menu.
To replace the generic TRUE or FALSE values for a checkbox, follow these steps:
- Select your cells containing checkboxes.
- Click Data, then choose the Data validation option.
- Under the criteria box, check the Use Custom cell values box
- Add your custom values and click save.
How to Remove Custom values
To undo this, follow the same steps for adding them, then uncheck the Use custom cell values option in the Data validation menu.
Use Checkboxes to Create Dynamic Charts
The simplicity of checkboxes conceal the diversity of its possible applications. As an example, checkboxes can be used to create interactive charts. Here are the steps:
Step 1: Insert checkbox in after each series of data wherever you want them placed, using the procedure above. For this example, I added them to the end of the table of values.
We created a duplicate table containing the same values, but displaying them when the checkbox is ticked. The formula has the general form:
=IF(<cell where="" the="" checkbox="" is="" located="">,<cell where="" the="" data="" is="" located="">,"")</cell></cell>
By using them, you can create a duplicate table containing the data that you want to be shown:
Tick all the checkboxes to create a copy of the table, then create a Google Sheets chart using the data in the duplicate table. You can follow the tutorial here.
Now you have created an interactive chart where you can control which dataset is displayed. Sometimes, the simplest functions turn out to be most useful!
Using Checkboxes with SUM function
You can also use a checkbox in Google Sheets to include or exclude certain values from your calculations.
This can be useful
Check out our tutorial here to sum cells with checkboxes in Google Sheets.
Using Checkboxes with Conditional Formatting to Create a To-Do List
You can also use the conditional formatting menu to create interactive checklists in Google Sheets. You can find further details about this in our conditional formatting checkboxes tutorial.
To do so, follow these steps:
- Select the cells with your list items.
- Click the Format button in the menu.
- Click "Conditional Formatting."
- In the Conditional Formatting pane that opens, click on the ‘Format cells if' drop-down.
- Click on "Custom formula is".
- Enter the following formula: =$B2
- Format the color and appearance of your list item cells.
When you mark tasks as complete in our example, the list item turns green and the strikethrough format is applied.
Insert Checkbox FAQs
Can you add multiple checkboxes in one cell?
No, you can only insert checkboxes in individual cells. You can add multiple checkboxes to multiple cells of your Google spreadsheet.
Can you put a checkbox and text in the same cell in Google Sheets?
If the cell where you insert a checkbox has any text or formulas, Google Sheets will remove the current content and replace it with a checkbox.
How to Select/Deselect all Checkboxes
To check or uncheck all of your checkboxes on a page or within a selected range, simply select the range (click Ctrl+A to select the whole sheet), then click the space bar. If you desire the opposite outcome, click the spacebar again.
How to count checkboxes in Google Sheets?
Check out our tutorial to count checkboxes in Google Sheets.
Recommended articles
More resources
