Getting an All Selection Option in a Drop-down in Google Sheets

What about getting an All or Any selection option in a drop-down menu to populate part or whole data in Google Sheets?

I was about to write a tutorial on the use of drop-down menus in Charts. On the course, I’ve come across one issue.

I wanted to visualize my data as a whole or part by applying different filters. 

So I badly needed the option to select all the data from a custom drop-down menu.

That means I want a drop-down menu item called “All’ or “Any” with other categories (menu items) to filter my data for the chart.

How to Get an All or Any Selection Option in a Drop-down

Google Sheets has a Filter menu option. In addition, there is a function called Filter in Google Sheets.

You can use the drop-down menu items as the criteria in Filter to get part or whole data from a range.

See the following example where I’ve used a Drop-down menu to filter the data on the left-hand side.

When I select “All,” the Filter returns the entire data in the table.  

filter data based on the ALL selection in drop-down in Google Sheets

Inserting a Drop-Down Menu (New Approach)

Data Validation permits users to create a drop-down menu. You can insert it from the menu Insert > Drop-down or Data > Data validation in Google Sheets.

In a Drop-down menu, you can add menu items in two different ways.

  1. Drop-down.
  2. Drop-down (from a range).
Adding "Any" Item in Data Validation Drop-down

If you choose the “Drop-down” criteria, you should add the menu items manually. So you can include an ‘All’ menu item.

On the other hand, if you choose “Drop-down (from a range)” as the criteria, select the cell range to include the menu items.

But there won’t be any “All” selection option in the drop-down menu.

How do we get an “All” or “Any” selection option when we choose Drop-down (from a range)?

If your range is A2:A, insert the following formula in a new range and use that range instead of A2:A.

={"All";unique(A2:A)}

Note:- As per sample data on the GIF above, I have got this formula in cell J2. The E1 drop-down uses the cell range J2:J instead of A2:A.

I hope you have learned how to get an “All” selection option in a Drop-down in Google Sheets.

Now let me explain how to use this menu to filter your data using either Filter or Query.

Filter Based on All or Any Selection Option in a Drop-down

Just scroll up and see the GIF. There, in cell F1, I have the following Filter formula.

=ArrayFormula(if(E1="All",B2:C11, filter(B2:C11,A2:A11=E1)))

If the drop-down selection is “All,” this formula executes the TRUE part of the IF function.

That means it returns the range B2:C11 as it is. Otherwise, it filters B2:C11 based on the drop-down item, i.e., A2:A11=E1.

What about the Query alternative?

Here you go!

=query(A2:C11,"Select B,C " & IF(E1="All",, "where A = '"&E1&"' "),0)

Related:

  1. How to Use the IF Function in Google Sheets Query Formula.
  2. The Purpose of WHERE 1=1 in Google Sheets Query.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

4 COMMENTS

  1. Hi Prashanth,

    This is a really useful article – many thanks for sharing. I’m using the formula provided with great success but now I’m stumped – I’d also like to filter my data using a second and possibly third drop down but I can’t figure out how to apply the ‘filter all’ element of the if statement to the second and/or third drop-down menus.

    So a ‘customer name’ is selected from the first drop-down menu I’d like to see all the records relating to that customer, but then I’d like to filter the returned data using the second drop down to display all or filter by ‘subject’ and finally I’d like to filter the resulting data by all or ‘status’.

    I feel like I’m stretching my capabilities here – is it even possible?

    Gus

  2. Hello,

    Thank you for these amazing tips. It is helping me a lot. I have a question now, how will look like the formula when using 2 or more drop-downs?

    =ArrayFormula(if(E1="All",B2:C11, filter(B2:C11,A2:A11=E1)))

    • Hi, Josef,

      I am glad that you liked this tutorial. Now answer to your question.

      In my above example, there is only one drop-down, that is in cell E1. With that, you can only select all the countries or countries in any ONE continent. You can change that to all the countries or countries in any TWO continents.

      To do that add one more drop-down in cell E2. You can just copy the drop-down in cell E1 and paste it in cell E2.

      Now you can use this formula in cell F1.

      =ArrayFormula(if(E1="All",B2:C11, filter(B2:C11,(A2:A11=E1)+(A2:A11=E2))))

      This formula reveals how to use the OR criteria in Filter. To learn more about this usage, please check my following related tutorial – How to Use AND,OR with Google Sheets Filter Function – ADVANCED Use.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.