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.
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.
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:
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:
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.
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:
To undo this, follow the same steps for adding them, then uncheck the Use custom cell values option in the Data validation menu.
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!
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.
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:
When you mark tasks as complete in our example, the list item turns green and the strikethrough format is applied.
No, you can only insert checkboxes in individual cells. You can add multiple checkboxes to multiple cells of your Google spreadsheet.
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.
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.
Check out our tutorial to count checkboxes in Google Sheets.
