How To Create A Select All Checkbox in Google Sheets

In this post, you’ll learn how to create a Select All checkbox in Google Sheets, as shown in this image:

select All Checkbox in Google Sheets

But before we look at that, did you know there’s already a quick way of selecting all checkboxes manually?

Select All Checkboxes With Spacebar

Highlight a range of checkboxes.

Press the spacebar to toggle them checked or unchecked.

select all checkboxes spacebar

How To Create A Select All Checkbox in Google Sheets

Ok, onto the good stuff then 😉

And firstly, I want to tip my hat to one of my readers for sharing this technique with me. Thank you, God of Biscuits, whoever you may be!

Alrighty then! Let’s see how to create that select all checkbox in Google Sheets.

This technique combines checkboxes, setting default values with array literals, and conditional formatting across one row.

Let’s set it up in column A, without all the bells and whistles, so that you can see how it works.

Step 1: Add the main checkbox

Insert a regular checkbox into cell A1, from the menu: Insert > Checkbox

Step 2: Add the secret formula

Copy this formula into cell A2:

=IF(A1,{ "" ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE ; TRUE },"")

Toggle the checkbox and you’ll see the formula outputs 10 true values in the range A3:A12, like this:

Checkbox array literal formula

The formula uses an IF function to check whether the checkbox in cell A1 is checked (TRUE) or unchecked (FALSE).

If it’s checked, then it outputs an array literal of 10 true values.

If it’s false, i.e. unchecked, then the formula simply gives a blank output.

You can probably see how it fits together now. We need to add some checkboxes to those cells A3:A12.

Step 3: Add the first special checkbox

Go to cell A3 and add another checkbox through the Data menu: Data > Data validation

Under Criteria, select the checkbox.

Then select “Use custom cell values”

Set the TRUE value to be TRUE.

Set the FALSE value to be a blank cell (ignore the e.g. FALSE suggestion in the box already).

Data Validation Setup

Step 4: Add remaining checkboxes

Copy and paste this new checkbox from cell A3 to the range A4:A12.

Now, when you check or uncheck A1, all the checkboxes in A3:A12 will automatically be checked or unchecked.

select all checkbox simple example

Step 5: Formatting

To make the example at the top of the page, have a look at these two articles for instructions:

Notes On The Select All Checkbox in Google Sheets

This method suffers one major drawback.

The select all checkbox only works when the checkboxes are unselected. In other words, once a user selects one or more of the checkboxes then the main select all checkbox won’t work.

You get a #REF! error if you try to use the select all when you already have some checkboxes selected.

select all checkbox error

Select All Checkbox in Google Sheets Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.

See Also: How To Mimic Radio Buttons With Formulas And Checkboxes

Another interesting checkbox technique is to mimic radio buttons with formulas and checkboxes.

You use array literal formulas to ensure a user can only select a single checkbox at a time.

4 thoughts on “How To Create A Select All Checkbox in Google Sheets”

  1. Wow, that was quick! Explained much better than me as well… I’m surprised that the hacky single quote in the FALSE field isn’t required; I could swear it was needed when I first tried this as the dialog wouldn’t accept a blank input. Maybe something has changed behind the scenes (or I just got confused)? Interesting also that it’s a ‘Checkbox’ at your end (I’m in the UK so maybe it’s a US English vs. UK English thing).

    I might as well add the other use-case I’ve come up with here, which was limiting the number of ticks in a range of tick boxes. For this you need an empty column to the left of (or empty row above) the tick box range. So for instance, for a column of 10 tick boxes in B1:B10 with a maximum of three ticks allowed:
    1. Create 10 tick boxes in B1:B10, set the custom ticked value to 1 (not TRUE, I’ll come back to this) and leave the custom unticked value blank
    2. In A1, enter the formula =IF(COUNT(B$1:B$10)=3,IF(B1,””,{“”,””}),””)
    3. Copy the formula down to A10

    The IF statements are basically saying, do nothing until three ticks are present, and then when three ticks are present, create a two cell wide empty array from each IF which isn’t next to a tick to ‘cover’ the unticked box, thus preventing any further ticks. If you untick any of the three tick boxes, the empty arrays are removed and you can change which boxes are ticked once more. I don’t think this can be achieved with a single-cell dynamic array as the manually-placed ticks would always be ‘in the way’, preventing the array spilling out onto the right cells. In principle you ought to be able to do this with the custom TRUE value remaining as TRUE (and using COUNTA instead of COUNT) but it seems to sometimes permanently lock all the tick boxes when done this way (almost as though the empty strings are still being counted). I’m also assuming that using ‘1’ instead of TRUE wouldn’t cause problems as they’re equivalent in Boolean terms – depending on how you are using the tick boxes you might need to use a ‘–‘ to coerce the blanks back into zeros anyway and this would automatically transform the TRUEs into 1s whether you liked it or not. Interestingly, the ‘really empty’ string you can generate from an IF(,,) or a LEFT(x,0) doesn’t work here to prevent access to the tick boxes; it must be the “” form.

    Keep up the good work!

  2. I would love to be able to carry this across columns in the same row (even if I have to hide some columns) instead of down through the column, but I’m not skilled enough to hack this for that purpose.

    Care to show us?

    1. I’m just an insider, but if you don’t have an answer, I’m up for it: it depends if you use English settings or not. In my case for rows the , is replaced by ; and for columns the ; for \
      With English configuration, with ; expands into rows and with , expands into columns

  3. Can you do more than 0 check boxes for select all? i tried this formula it didn’t work if my boxes are more than 10.. I even added more “true ‘ in the formual.

Leave a Reply

Your email address will not be published.