Dependent Drop Downs with FILTER
This post shows how to create multiple dependent drop downs using the FILTER function. These are also known as cascading or conditional drop downs, where the choices in a drop down depend on the selection made in a previous drop down. The technique presented enables you to create as many drop downs as you need, and there is no VBA coding needed.
Note: depending on your version of Excel, you may not have access to the FILTER, UNIQUE, or SORT functions. If not, check out this post which uses legacy functions or this post which uses slicers.
Before we get into the mechanics, let’s clarify our goal. We would like to create a series of dependent drop downs, where the choices available in a drop down depend on the selection made in a previous drop down.
For example, we would like the user to pick a Type of shirt. Depending on the Type of shirt selected, the user will see various Style options. Depending on the selected Style option, the corresponding Size options are displayed, and so on. Perhaps something like this:
These configuration options all come from a single table, which stores all valid combinations. Perhaps something like this (Table1):
To create this, we’ll use a few functions (SORT, UNIQUE, FILTER) along with data validation.
We’ll build this using these steps:
- Create a drop down list
- Repeat for each drop down
- Retrieve related info
Let’s get to it.
Create a drop down list
We’ll perform three steps for each drop down:
- Create the list of choices
- Create the in-cell drop down
- Make a selection
We’ll ultimately repeat these steps for each subsequent drop down.
Create the list of choices
To create the list of choices, we’ll write a formula in an unused area of the worksheet.
Let’s start with the first drop down, which in our case is Type.
The first drop down has no dependencies, and thus will include the same list of choices all the time. Since the products table is named Table1, and the first drop down needs to provide a unique list of all items found in the Type column, we’ll use the following formula:
We write the formula into cell B17 and hit Enter. We see that the formula returns multiple results:
If we’d like the list to be sorted, we can include the SORT function in our formula like this:
Now our list of choices is sorted, like this:
With our list of choices ready, we just need to get them into an in-cell drop down. For this, we’ll use Data Validation.
Create the in-cell drop down
We select the input cell, and use Data > Data Validation. We allow a list, and set the source to =$B$17# like this:
Note: the # tells Excel to include all results returned by the formula.
The input cell now contains a drop down with the list of choices.
Make a selection
Although we technically do not need to make a selection from the drop down right now, it will make the next step easier if we do. So, we select the input cell and pick any choice so that the cell is not empty, like this:
Note: the reason we make a selection is to avoid formula errors that could lead us to believe our formula is wrong.
Repeat for each drop down
Then, we perform these steps again for each subsequent drop down. We need to write a formula to create the list of choices, use data validation to create the drop down, and then make a selection so the cell is not empty.
But, there is a catch. Each subsequent list of choices depends on the choice made in the previous input cell. To accommodate this dependency, we’ll use the FILTER function.
Style Drop Down
The next choice list is for Style. The choices for Style depend on the selection in the Type input cell. So, to create our list of Style choices, we use this formula:
This basically tells Excel to return a sorted list of unique Style values where the Type column is equal to the selected Type in C6. We write the formula in C17 and hit Enter:
To get that list of choices into the Style input cell, we once again use Data Validation. This time, the source is =$C$17#. We expand the resulting Style drop down and make a selection so the cell is not blank:
The next input cell is for Size.
Size Drop Down
For Size, our list of choices depends on the values selected for Type and Style.
So, our FILTER function will show the list of Sizes where Type and Style values match. To use AND logic when we have multiple conditions, we use a multiplication operator * like this:
=SORT(UNIQUE( FILTER(Table1[Size],(Table1[Style]=C7)*(Table1[Type]=C6)) ))
Note: to use OR logic instead of AND logic, we would use the addition operator + instead of the multiplication operator.
We write the formula into cell D17 and hit Enter:
To create the drop down, we use Data Validation and set the source =$D$17#. We expand the resulting drop down and make a selection, like this:
So far so good … we just have one final drop down.
Color Drop Down
The final drop down for this illustration is color, but you can continue using this same technique for as many drop downs as needed.
Our formula to create the choices includes conditions for Type, Style and Size:
=SORT(UNIQUE( FILTER(Table1[Color], (Table1[Size]=C8)*(Table1[Style]=C7)*(Table1[Type]=C6) ) ))
We write the formula into E17 and hit Enter:
We use Data Validation to create the drop down, and set the source =$E$17#. We make a selection:
Now that we have created the dependent drop downs, we can optionally return related values.
Retrieve related info
In our case, once the user has selected the Type, Style, Size, and Color, we’d like Excel to return the related Price.
Once again, we’ll turn to the FILTER function. We write the following formula into C10:
=FILTER(Table1[Price], (Table1[Color]=C9)*(Table1[Size]=C8)*(Table1[Style]=C7)*(Table1[Type]=C6) )
We hit Enter, and bam:
Note: if the products table contained multiple matching rows, we can aggregate the results with a helper function such as SUM, MIN, MAX, TEXTJOIN, AVERAGE, COUNT, and so on.
If you see any #CALC! errors, it probably means that the dependent input cells are blank, or that the options selected don’t have related values. This could happen if you change the selection in the first input cell and don’t pick valid options from the updated drop downs for the subsequent cells.
I think we accomplished our goal. We created a series of dependent drop downs. We used a few functions (FILTER, UNIQUE, SORT) and data validation. Let me know what you think by posting a comment below … thanks!
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
Brilliant, as always you make the solution to a complex problem look so easy.
Very informative, nice job.
This is most Excel lent! Clear and concise 🙂 Is there a way to make the sort/unique/filter work as a data validation rule?
Apparently, creating creating dependent drop downs with FILTER is much easier and compact than other options (especially those where data are stored in one table and we had to create multiple dynamic named ranges for 1st row, last row, etc. – that exercise from graduate course was brutal).
I need help. This was very informative! But it still gave me problems. I have 3 manufacturing lines, producing multiple part numbers on each. Some of the part numbers are run on both lines 1 and 3 but not on line 2, some on lines 2 and 3, but not on line 1. I’m trying to find a way to get the part numbers to be listed in a drop-down list depending on which line number is selected from the first drop-down list. Can you help me with that? I would be most appreciative! Thank you so much!
Jeff, you saved me with this today. I had taken your Excel University CPE classes before and thought you may have a resource on your blog that would help solve my problem. As luck would have it, this expansion on data validation perfected the time-saving project I was working on for my boss. We’re all going to be so much more efficient thanks to your instructions! This made my whole week.
Yay … glad I could help 🙂
Impressive clarity and structure of your solution! Thanks!
I am working with a table where I would like to implement this useful function but my table works horizontally rather than vertically. Is it possible to do this with rows rather than the table headings? For example Table1[syte} would be Table1[row8]?
Thanks in advance.
I wish I would have come here first! Spent so many hours trying to figure out this issue and countless sites/blogs. You made it so easy and it works to perfection! Thanks for posting!
Thanks for your kind note and I’m glad it helped!
How would I remove the #CALC error if let’s just say I only wanted to order 1 clothing item, but I could choose up to ten? The other 9 spots give me a #CALC! error, becuase nothing is chosen. It is also ruining my total sum formula. How do I sum if with a #CALC! ERROR?
Please disregard the above question. I was able to figure it out. However, can I make this dynamic? Looking to make dynamic for 9 9other spots/ choices/
Thanks for work and it is very helpful kudos for your time and effort!
In this context, I have used this sheet Microsoft 365 and its working fine. However, the same is not working in excel 2019 has there are no functions such as SORT or Filter in ec; 2019 . Kindly request how to execute the same in 2019 or below.
Thanks much in advance
Reg , Sri