Create A Select All Checkbox in Google Sheets

Reading Time: 4 minutes

What is a Checkbox in Google Sheets?

A checkbox (also known as a tickbox or tick box) is a graphical widget that allows the user to make a binary choice, that is, a choice between one of two mutually exclusive options. For example, on a simple yes/no question, the user may be required to answer ‘yes’ (checked) or ‘no’ (not checked).

Why use Checkbox in Google Sheets?

You have a list of tasks that you want to perform by the end of the week.

Figure 1: Dataset of interest
Figure 1: Dataset of interest

As soon as you complete a particular task, you want to strike it off to have an understanding of uncompleted tasks. You can perform this task by using a checkbox.

Figure 2: Example of checkbox in Google Sheets
Figure 2: Example of checkbox in Google Sheets

Add a checkbox beside every task. Apply conditional formatting on the checkbox such that whenever the tickbox is checked, i.e. the value is set to true, strike off the task, as shown below. This will help you keep track of unfinished tasks and thereby help you plan your schedule better.

Figure 3: Use of conditional formatting
Figure 3: Use of conditional formatting

Make a Copy of the Example Spreadsheet

How to create a Select All Checkbox in Google Sheets?

Step 1: Add the Select All Checkbox

  • Select a cell where you want to create a select all checkbox.
  • Go to Insert → Tick Box to add a checkbox at desired cell location.
Figure 4: Create a Tick Box
Figure 4: Create a Tick Box

Step 2: Add Select All Formula to the checkbox

  • Select a cell other than the select all checkbox.
  • Enter the following formula:
=IF(A1,{ "" ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE },"")

Here, the first parameter of the IF condition is whether the checkbox value is true or false, i.e. is the checkbox selected or not. If the checkbox is selected, then checkboxes in cell A2:A10 should be true. If the checkbox is not specified, keep the other checkboxes unselected.

Figure 5: Use the formula to create a select all checkbox in Google Sheets
Figure 5: Use the formula to create a select all checkbox in Google Sheets

Step 3: Add a Checkbox to be selected

  • After entering the formula to select all checkboxes, it’s time to create the list of checkboxes which need to be chosen with select all checkboxes.
  • Select the first cell and go to Insert → Tick Box.

Figure 6: Add other checkboxes
Figure 6: Add other checkboxes
  • Now go to Data → Data Validation.

Figure 7: Navigate to Data Validation
Figure 7: Navigate to Data Validation
  • Under Criteria, select the checkbox.
  • Then select “Use custom cell values.”
  • Set the TRUE value to be TRUE.
  • Set the FALSE value as a blank cell (ignore the, e.g. FALSE suggestion in the box already).
Figure 8: Set the parameters as shown above
Figure 8: Set the parameters as shown above

Step 4: Drag the selection down to create all checkboxes

  • After you have created the first checkbox, drag the selection down.
  • This will create a list of checkboxes.
  • As soon as you click on the select all checkbox now, all the checkboxes will be selected.
Figure 9: Example of a select all checkbox in Google Sheets
Figure 9: Example of a select all checkbox in Google Sheets

Conclusion

Checkboxes help keep track of the status of tasks planned for a particular period. You can create a select all checkbox in Google Sheets to select all the tasks at once.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.

How to View Edit History of Cells in Google Sheets | Easy 2 Minute Guide: Learn how to view the edit history of cells in Google Sheets. Learn to spot mistakes or past changes.

shalinamigo

←
→

Leave a Reply