When a checkbox is in a ‘checked’ state, the corresponding cell value becomes TRUE, and when it is unchecked, the cell value becomes FALSE. This change in the cell value allows you to create interactive to-do lists and dynamic charts in Google Sheets. You may also set up your own rules and use conditional formatting to highlight or emphasize the data. For example, you can create a rule that when a cell value becomes true (the checkbox is checked), another cell gets highlighted or a strikethrough effect is applied to its value. We will not only show you how to insert a checkbox in Google Sheets but also explain how to make it functional with the help of a few examples.
How to add a Checkbox in Google Sheets?
We will first cover in brief how to insert a checkbox (single or multiple) and then explain how you can use checkboxes to create task lists, dynamic charts, etc.
1] Add a single checkbox in Google Sheets
You may insert a checkbox in Google Sheets using one of the following ways:
Using the Insert menuUsing Data Validation rule
Let us see these in detail.
A] Add a single checkbox using the Insert menu
Adding a checkbox in Google Sheets is quite simple. Select the cell where you’d like to insert the checkbox. Then click on the Insert menu on top of the spreadsheet and select the Checkbox option. This will insert a checkbox in the selected cell.
B] Add a single checkbox using Data Validation rule
You can also insert a checkbox using Data Validation in Google Sheets. Select the cell where you would like to have the checkbox and then go to Data > Data Validation. In the Data Validation pane on the right side, click on the Add rule button. Then select Checkbox in the Criteria dropdown and click on the Done button. To remove the checkbox, simply press the delete button on your keyboard.
2] Add multiple checkboxes in Google Sheets
Once you’ve added a single checkbox, you can copy it to the adjacent cell values (both horizontally and vertically). Take your cursor to the bottom-right corner of the cell where you’ve inserted the checkbox and as the cursor turns into a plus symbol, click, hold, and drag it till the cell up to which you want multiple checkboxes. Release the click to fill the cells with checkboxes. Bonus Tip: To tick all checkboxes in a column or row, select them using your mouse and then press the space bar key. To deselect them, once again press the space bar key.
3] Use checkboxes in Google Sheets
We will demonstrate a few examples to show you how to use checkboxes in Google Sheets. Let’s get started.
A] Create a Task List
You may use checkboxes to create a to-do list and mask your tasks as ‘Done’ or Not Done’. Refer to the image shown above. We have created a task list wherein we have added a few tasks in the cell range A5:A12. In front of each of these tasks, we have added checkboxes (in cell range B5:B12). To mark the tasks Done or Not Done, you may simply click on the relevant checkboxes. However, you may use conditional formatting to make it look a bit more interactive to the user. Here’s how:
Select the cell range of your task list (as A5:B12 in our case).Select Format > Conditional formatting.In the Condition format rules pane on the right side, select the ‘Custom formula is’ option in the Formal rules dropdown.In the Value or formula text box, type $
Now click on the checkboxes in your task list. Tasks marked as Done will now appear highlighted and crossed out. Read: How to add a Tooltip in Excel and Google Sheets.
B] Create a Dynamic Chart
Checkboxes allow you to show/hide data while creating dynamic charts in Google Sheets. Let us understand this with the help of an example. Refer to the above image. We have created a chart wherein we are showing a category-wise listing of the number of posts published on TheWindowsClub (an example). In this chart, we may include or exclude a category by clicking on the available checkboxes in our main dataset (A4:G9). So what you have to do is:
Create a copy of the dataset with empty values (headers only, as shown in the below image). Do not copy checkboxes here.In the cell where you should have copied the first data value, enter the following formula: =IF($G$5, B5, “”), where $G$5 is the cell where the first checkbox is placed, and B5 is the cell containing the first data value. So this function will return the value of B5 if the checkbox is ticked (is in the TRUE state), otherwise it will return an empty value.Copy the same formula to the entire data row.Repeat the same process for each row in the dataset, while changing the cell reference to the checkboxes and the data values.Test the results by clicking on the checkboxes. When you click on a checkbox in the main dataset, the row values should appear in the copy of the dataset.Now select the dataset copy (A11:F16 as in our case) and go to Insert > Chart.Select a Chart Type, for example, a line chart as shown in the above image. You may need to adjust the horizontal and vertical axis to properly show your data.Add customizations if required.
Now you may use the checkboxes in the main dataset to show/hide values in the chart which is based on its copy.
C] Show/Hide cell values
You may also use a checkbox as a ‘control switch’ to show/hide cell values. For example, take a look at the above image. We have created a table wherein we are going to show/hide password hints for different authors of TheWindowsClub, based on the checkbox selection. To do so, we have used the IF function in cell B5 as: Where C5 is the cell reference to the checkbox and B5 is the cell where the password hint needs to be displayed. So the function will return the password hint (Mother’s maiden name) if the checkbox is ticked, else, it will return a blank value. The same formula has been used for each row in the Password Hint column, only the cell references are changed. You may use checkboxes to show/hide cell values in Google Sheets in a similar way. This is all about adding and using checkboxes in Google Sheets. Read Next: How to count checkboxes in Google Sheets.
