What Is Checkbox In Google Sheets?
The Checkbox in Google Sheets is an interactive tool that we can use to display the selection or deselection of an action item or an option. We can insert a checkbox in a cell using the Checkbox option from the Insert tab or the Data validation functionality.
Users can utilize the Checkbox option in Google Sheets for tracking milestones in a project, taking attendance, and checking-off the completed tasks from a to-do list.
For instance, the source dataset contains a list of order IDs and item codes.
We need to update their order dispatched status in column C.
Then, in such a scenario, we can create checkbox in Google Sheets target cells, which is similar to Checkbox in Excel.
In this make checkbox in Google Sheets example, we choose the target cells C2:C6 and click the Insert tab to select the Checkbox option.
The above step will add checkbox in Google Sheets target cells.
So, now, if we must show a specific item’s order is dispatched, we can click inside the corresponding checkbox to view a tick mark inside the checkbox. On the flip side, if we must show that a specific item’s order is not dispatched, do not click inside the checkbox to leave it blank.
Table of contents
Key Takeaways
- The Checkbox in Google Sheets is a graphical widget that enables us to switch between two options and denote an approved or negative choice.
- We can use the Checkbox option from the Insert tab or the Data validation feature to insert a checkbox in a cell or range in Google Sheets.
- We can use checkboxes in Google Sheets to create dynamic charts, prepare to-do lists, highlight rows of data, and control switches for formula executions.
- Strategically positioning the checkboxes next to the data they represent and using the checkboxes with the Conditional formatting option yields fruitful results.
How To Insert A Checkbox In Google Sheets?
We can insert a checkbox in a cell in Google Sheets using the following methods:
- Checkbox Option From The Insert Tab
- Checkbox Option From The Data Validation Functionality
Method #1 – Checkbox Option From The Insert Tab
- Select the cell or range where we aim to add checkbox in Google Sheets.
- Select the Insert tab – The Checkbox option.
The above action will create checkbox in Google Sheets target cell or range.
We can now click inside the checkbox to show a selection.
Please note that the Formula Bar will show the Boolean value TRUE when we click inside the checkbox, indicating a selection. Otherwise, the Formula Bar will show the Boolean value FALSE, indicating no selection.
Method #2 – Checkbox Option From The Data Validation Functionality
- Choose the cell or range where we aim to make checkbox in Google Sheets.
- Select the Data tab – The option Data validation in Google Sheets, which is similar to Excel Data Validation.
- The Data validation rules window will open on the right of the workspace, where we must click the + Add rule option.
- Choose the Checkbox option in the Criteria field in the Data validation rules window.
- Click Done in the Data validation rules window to complete the action.
The above steps will insert the checkbox in the chosen cell or range.
So, we can now click inside the checkbox to show a selection.
Examples
The following illustrations show the practical uses of checkboxes in Google Sheets.
Example #1 – Create A To Do List App
We have a list of household chores in column A.
We must create a to-do list app using the source dataset. For that, we must have checkboxes against the chores in column B. Next, if we click a checkbox, the corresponding columns A and B row must get highlighted and the specific chore must appear struck out.
Then, we can use the Conditional formatting for checkbox in Google Sheets to secure the required outcome.
Step 1: Select cells B3:B7 and then Insert – Checkbox.
We will see the checkboxes in the chosen cells.
[Alternatively, select cells B3:B7 and then Data – Data validation.
The Data validation rules pane opens, where we must click the + Add rule option.
Next, set the Checkbox option in the Criteria field.
Next, set the required Advanced options settings.
Click Done to view the checkboxes in the chosen cells.]
Step 2: Select the range A3:B7 and then the Format tab – The option Conditional formatting in Google Sheets, which is similar to Excel Conditional Formatting option.
The Conditional format rules pane opens, showing the Single color tab.
The Apply to range field shows the chosen cell range. Next, we shall set the Format cells if field in the Format rules section as the Custom formula is option.
Next, update the required custom formula in the next field in the same section.
=$B3
Next, set the required Formatting style. In this case, we shall select the Strikethrough option and set the desired cell background color using the Fill color option.
The custom formula checks if the column B cell contains the value TRUE, which happens when we select the checkbox in the specific cell. The process repeats for all the column B cells in the chosen range.
When the condition is True for a column B cell, the specified conditional formatting style applies in the corresponding row in the source dataset.
Click Done to view the rule for Conditional formatting for checkbox in Google Sheets in the Conditional format rules pane.
Next, close the Conditional format rules pane.
Now, if we click the checkbox corresponding to the completed chore in the to-do list app, the chore appears struck out in the to-do list app, with the checkbox also conditionally formatted.
Example #2 – Highlight Data With A Checkbox
The source dataset contains the zonal sales figures of representatives at a firm.
The task is to highlight the rows of data in the source dataset where the sales figures are equal to or exceed $25,000. However, we must use a checkbox to highlight the required data.
Step 1: We shall use cell F1 to show the checkbox. So, select cell F1 and then Insert – Checkbox.
We shall see the checkbox in cell F1.
Step 2: Select the range A2:C11 and then Format – Conditional formatting.
The Single color tab in the Conditional format rules window opens.
The Apply to range field shows the chosen cell range.
Next, set the Format cells if field in the Format rules section as the Custom formula is option, after which we shall update the following custom formula in the next field.
=AND($F$1,$C2>=25000)
Next, set the required Formatting style settings. In this case, we shall select the required cell background color using the Fill color option.
The formula checks if the value in cell F1 is TRUE, which happens when the checkbox in cell F1 is selected and the column C cell contains a sales value equal to or exceeding $25,000. The process repeats for all the column C cells in the chosen range.
When both conditions are true for a column C cell, the corresponding row in the source dataset gets highlighted.
Click Done to get the defined rule in the Conditional format rules pane. Next, close the pane.
Now, if we click inside the checkbox in cell F1, the rows where the sales figures are equal to or exceed $25,000 get highlighted in the source dataset in the chosen color.
Example 3 – Show/Hide Hints And Solutions To A Test
The source dataset shows a questionnaire in a test in column A, with the answers in column B.
The requirement is to show or hide the hints and solutions to the test using checkboxes.
We shall use columns D and E to show or hide the hints and solutions. Furthermore, we shall use column C to insert the checkboxes required to decide whether to show or hide the hints and solutions to the test.
Step 1: Choose cell D3, enter the following formula, and press Enter.
=IF(C3,”=0.6*25000″,””)
Next, choose cell E3, enter the following formula, and press Enter.
=IF(C3,0.6*25000,””)
Likewise, update the appropriate formulas in the remaining cells D4:E6.
The condition in each IF(), which is similar to Excel IF function, is to check if the corresponding column C cell holds the value TRUE. If the condition is true, the formula returns the TRUE value.
In the case of the Hint column, the TRUE value in the IF() is a text that displays the equation required to derive the answer for the corresponding question. On the other hand, in the case of the Solution column, the TRUE value in the IF() is the equation or formula required to derive the answer for the corresponding question.
Step 2: Select the range C3:C6 and then Insert – Checkbox.
The checkboxes appear in the chosen cells.
Now, we can click inside the checkbox against the question and answer to show the corresponding hint and solution to the question. On the flip side, leaving the checkbox unselected will keep the hint and solution for the corresponding question and answer hidden.
Example #4 – Create Dynamic Charts
The source dataset contains a set of products and their monthly sales figures. Also, the cell range A8:F13 has the formatting style of the source dataset range.
The requirement is to prepare a dynamic chart based on the source dataset using checkboxes.
Step 1: Select cells F2:F6 and then Insert – Checkbox.
We get the checkboxes in the chosen cells.
Step 2: Select all the inserted checkboxes. We can do so by choosing the cell range F2:F6 and pressing the Spacebar to check all the checkboxes in one go.
Next, select cell A8, enter the QUERY(), and press Enter.
=QUERY(A1:F6,”where F=TRUE”,1)
The QUERY() shows rows of the source data in the range A8:F13, provided the corresponding checkboxes in the source dataset are selected. In this case, since we checked all the checkboxes, QUERY() shows the entire source dataset in the second dataset, with cells F9:F13 showing the value TRUE. The TRUE value indicates the specific checkbox is selected.
Furthermore, if we deselect a checkbox in the source dataset, the second dataset will not show the corresponding row of data.
We shall now prepare the dynamic chart using the second dataset.
Step 3: Select the range A8:E13 (leave the column F cells F8:F13, as we do not need to show them in the chart). Next, choose Insert – Chart.
The Setup tab in the Chart editor opens on the right of the workspace.
The Chart type field in the Setup tab shows the chosen plot type, Column chart in Google Sheets, which is similar to Excel Column chart. Next, the Data range field displays the chosen cell range.
Next, ensure the last three settings in the Setup tab are updated to achieve the desired plot.
Click the Customize tab to access the options in the tab.
Here, select the Chart & axis titles section to expand it.
We can use the first and second fields, and the Title format setting options in this section to update the chart and axis titles in the plot.
After that, click the Legend section to expand it. Here, use the Position field to set the setting to view the legend in the required position in the plot area.
We can now close the Chart editor. So, the dynamic chart appears as depicted below.
We can now select and unselect the checkboxes in the source dataset to view and remove the specific products’ series in and from the plot area, making the chart dynamic.
Example #5 – Counting Checked-Boxes
The source dataset shows task IDs in column A.
The requirement is to update the task completed status using checkboxes in column B. Next, we must count the total tasks completed based on the checked boxes and display the output in cell E1.
Step 1: Choose the cells B2:B11 and then Insert – Checkbox.
The checkboxes get inserted in the chosen cells.
Step 2: Choose cell E1, enter the COUNTIF(), which works like the Excel COUNTIF function, and press Enter.
=COUNTIF(B2:B11,TRUE)
The COUNTIF() counts the cells in the range B2:B11 where the cell value is TRUE, which happens when the corresponding checkbox is selected.
For instance, assume we click on specific checkboxes in the range B2:B11. So, the corresponding cell values as TRUE. Next, the COUNTIF() counts the number of TRUEs and returns the count as the output.
How To Delete A Checkbox In Google Sheets?
The steps to delete a checkbox in a cell or range in Google Sheets are as follows:
- Select the cell or range containing the checkbox.
- Press the Delete button.
Important Things To Note
- Before using the option Checkbox in Google Sheets, we must know that we can insert only one checkbox in one cell. Also, we cannot enter a checkbox and a text or any form of data in the same cell.
- When we wish to select or unselect multiple checkboxes simultaneously, we can select the cell range containing the checkboxes. Next, press the Spacebar to complete the action.
Frequently Asked Questions (FAQs)
We can use a checkbox as control switch in Google Sheets, as described below with an example.
The source dataset contains invoice data of a firm.
We must update the delivery locations based on the invoice numbers in column G cells G4:G7. However, we must control the calculations using a checkbox in cell G1.
Step 1: Choose cell G4, enter the following formula, and press Enter.
=IF($G$1,VLOOKUP(F4,$A$2:$D$12,4,0),””)
The VLOOKUP() returns the delivery location by looking up the specified invoice number in the source data and determining the corresponding return value from column D.
However, the IF() checks if the cell G1 value is TRUE, which happens when the checkbox in cell G1 is selected. Only then does the IF() execute the VLOOKUP(). Otherwise, the function output is an empty string.
Next, use the fill handle option to feed the formula into the remaining column G cells.
Step 2: Select cell G1 and then Insert – Checkbox.
The above action inserts a checkbox in the chosen cell.
Now, when we select the checkbox in cell G1, the formulas in cells G4:G7 show the VLOOKUP() output as the IF() condition in each target cell formula becomes true.
Thus, in this way, we can select and unselect the cell G1 checkbox as a switch to control the delivery location calculation.
You cannot add multiple checkboxes in one cell in Google Sheets.
The difference between a Checkmark and a Checkbox in Google Sheets is that the Checkmark is a symbol inserted as a text value within a cell. We can use it only as a symbol or a bullet point, with text added before or after it.
On the flip side, a Checkbox is an interactive tool that we can use to indicate a chosen option. Furthermore, we cannot add text before or after a checkbox in a cell.
Download Template
This article must be helpful to understand the Checkbox In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Checkbox In Google Sheets. Here we learn how to insert a checkbox in Google Sheets with examples and points to remember. You can learn more from the following articles –
Leave a Reply