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