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:


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:


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:


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!

Sample file

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

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.


  1. Declan Kelly on November 18, 2020 at 12:10 pm

    Brilliant, as always you make the solution to a complex problem look so easy.

  2. Mike Rempel on November 19, 2020 at 3:11 pm

    Very informative, nice job.

  3. George Schmidt on November 19, 2020 at 4:39 pm

    This is most Excel lent! Clear and concise 🙂 Is there a way to make the sort/unique/filter work as a data validation rule?

  4. Nisso on November 24, 2020 at 11:16 am

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

  5. Lisa Bryant on February 2, 2021 at 2:11 pm

    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!

  6. Kristin Kamler on March 12, 2021 at 6:37 pm

    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.

  7. George on May 28, 2021 at 3:15 am

    Impressive clarity and structure of your solution! Thanks!

Leave a Comment

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.