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.  

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).

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.