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.
- Drop-down.
- 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:
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
Hi, Gus,
Here is a link to my Sheet which contains a two-level All Selection.
Data Validation Eg
Best,
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.