September 15, 2020

How to Insert a Checkbox in Google Sheets

Google Sheet spreadsheet
SECTIONS
  1. How to Insert Checkboxes
  2. The Advantages of Checkboxes
  3. How to Use Checkboxes to Control Chart Contents

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 checkboxes.

How to Insert Checkboxes

The procedure is straightforward. Select the cells where you want check boxes inserted, then click Insert on the menu, and then click Checkbox on the drop-down menu.

Insert drop-down box displayed, Checkbox option highlighted.


You will now get checkboxes on the selected cells.

Invoice spreadsheet with checkboxes


What are the advantages?

Besides the fact that it would be quicker for the user to fill up information that is either true or false, the user can also process it in formulas that would require a TRUE/FALSE information to produce results. If you mark the checkbox as checked, then that cell then contains a TRUE value; else its value is FALSE. This comes in handy once we process data that requires TRUE and FALSE values.

Let us extend our example above. We added conditional statements on the cells where there are 0s displayed. Now to see them in action:


Invoice spreadsheet with checkboxes. One option selected, with formula applied
Using simple checkboxes allow you versatility in processing information quicker while cutting out extra time needed to process them. 


Use Checkboxes to Control Content of Charts

The simplicity of checkboxes conceal the diversity of its possible applications. As an example, checkboxes can be used to control the content of charts. Here are the steps:

Step 1: Add a series of checkboxes wherever you want them placed, using the procedure above. For this example, I added them to the end of the table of values.


Checkboxes added at the end of the spreadsheet

Step 2: I 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:

Data displayed from one row to another using checkbox.


Step 3: Tick all the checkboxes to create a copy of the table, then create a chart using the data in the duplicate table. You can follow the tutorial here

Displayed dataset in chart controlled by the checkboxes.
Now you have created a table where you can control which dataset is displayed. Sometimes, the simplest functions turn out to be most useful!


Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.