How to make a dynamic dependent dropdown list in Excel an easy way

The tutorial shows how to create an Excel drop down list depending on another cell by using new dynamic array functions.

Creating a simple drop down list in Excel is easy. Making a multi-level cascading drop-down has always been a challenge. The above linked tutorial describes four different approaches, each including a crazy number of steps, a bunch of different formulas, and a handful of limitations relating to multi-word entries, blank cells, etc.

That was the bad news. The good news is that those methods were designed for pre-dynamic versions of Excel. The introduction of dynamic arrays in Excel 365 has changed everything! With new dynamic array functions, creating a multiple dependent drop-down list is a matter of minutes, if not seconds. No tricks, no caveats, no nonsense. Only fast, straightforward and easy-to-follow solutions.

Notes:

How to make dynamic drop down list in Excel

This example demonstrates the general approach to creating a cascading drop down list in Excel by using the new dynamic array functions.

Supposing you have a list of fruit in column A and exporters in column B. An additional complication is that the fruit names are not grouped but scattered across the column. The goal is to put the unique fruit names in the first drop-down and depending on the user's selection show the relevant exporters in the second drop-down.
Source data for a dependent drop down list

To create a dynamic dependent drop down list in Excel, carry out these steps:

1. Get items for the main drop down list

For starters, we shall extract all different fruit names from column A. This can be done by using the UNIQUE function in its simplest form - supply the fruit list for the first argument (array) and omit the remaining optional arguments as their defaults work just fine for us:

=UNIQUE(A3:A15)

The formula goes to G3, and after pressing the Enter key the results spill into the next cells automatically.
Getting the unique items for the main drop down list

2. Create the main drop down

To make your primary drop-down list, configure an Excel Data Validation rule in this way:

  • Select a cell in which you want the dropdown to appear (D3 in our case).
  • On the Data tab, in the Data Tools group, click Data Validation.
  • In the Data Validation dialog box, do the following:
    • Under Allow, select List.
    • In the Source box, enter the reference to the spill range output by the UNIQUE formula. For this, type the hash tag right after the cell reference, like this: =$G$3#

      This is called a spill range reference, and this syntax refers to the entire range regardless of how much it expands or contracts.

    • Click OK to close the dialog.

    Creating the main drop down list

Your primary drop-down list is done!
The first dropdown is accomplished.

3. Get items for the dependent drop down list

To get entries for the secondary dropdown menu, we'll filter the values in column B based on the value selected in the first dropdown. This can be done with the help of another dynamic array function called FILTER:

=FILTER(B3:B15, A3:A15=D3)

Where B3:B15 are the source data for your dependent drop down, A3:A15 are the source data for your main dropdown, and D3 is the main dropdown cell.

To make sure the formula works correctly, you can select some value in the first drop-down list and observe the results returned by FILTER. Perfect! :)
Getting items for the dependent drop down list

4. Make the dependent drop down

To create the second dropdown list, configure the data validation criteria exactly as you did for the first drop down at step 2. But this time, reference the spill range returned by the FILTER function: =$H$3#
Configuring the dependent drop down list

That's it! Your Excel dependent dropdown list is ready for use.
A dependent dropdown list in Excel

Tips and notes:

  • To have the new entries included in the drop-down list automatically, format your source data as an Excel table. Or you can include a few blank cells in your formulas as demonstrated in this example.
  • If your original data contains any gaps, you can filter out blanks by using this solution.
  • To alphabetically sort a dropdown's items, wrap your formulas in the SORT function as explained in this example.

How to create multiple dependent drop down list in Excel

In the previous example, we made a drop down list depending on another cell. But what if you need a multi-level hierarchy, i.e. a 3rd dropdown depending in the 2nd list, or even a 4th dropdown depending on the 3rd list. Is that possible? Yes, you can set up any number of dependent lists (a reasonable number, of course :).

For this example, we have placed states / provinces in column C, and are now looking to add a corresponding dropdown menu in G3:
Source data for a multiple dependent drop down list

To make a multiple dependent drop down list in Excel, this is what you need to do:

1. Set up the first drop down

The main dropdown list is created with exact the same steps as in the previous example (please see steps 1 and 2 above). The only difference is the spill range reference you enter in the Source box.

This time, the UNIQUE formula is in E8, and the main drop down list is going to be in E3. So, you select E3, click Data Validation, and supply this reference: =$E$8#
Setting up the first drop down list

2. Configure the second drop down

As you may have noticed, now column B contains multiple occurrences of the same exporters. But you want only unique names in your dropdown list, right? To leave out all duplicate occurrences, wrap the UNIQUE function around your FILTER formula, and enter this updated formula in F8:

=UNIQUE(FILTER(B3:B15, A3:A15=E3))

Where B3:B15 are the source data for the second drop down, A3:A15 are the source data for the first dropdown, and E3 is the first dropdown cell.

After that, use the following spill range reference for the Data Validation criteria: =$F$8#
Configuring the second drop down

3. Set up the third drop down

To gather the items for the 3rd drop down list, make use of the FILTER formula with multiple criteria. The first criterion checks the entire fruit list against the value selected in the 1st dropdown (A3:A15=E3) while the second criterion tests the list of exporters against the selection in the 2nd dropdown (B3:B15=F3). The complete formula goes to G8:

=FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3))

If you are going to add more dependent dropdowns (4th, 5th, etc.), then most likely column C will contain multiple occurrences of the same item. To prevent duplicates from getting into the preparation table, and consequently in the 3rd dropdown, nest the FILTER formula in the UNIQUE function like we did in the previous step:

=UNIQUE(FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3)))

The last thing for you to do is to create one more Data Validation rule with this Source reference: =$G$8#
Setting up the third drop down

Your multiple dependent drop down list is good to go!
Multiple dependent drop down list in Excel

Tip. In a similar manner, you can get items for subsequent drop-downs. Assuming column D contains the source data for your 4th dropdown list, you can enter the following formula in H8 to retrieve the corresponding items:

=UNIQUE(FILTER(D3:D15, (A3:A15=E3) * (B3:B15=F3) * (C3:C15=G3)))

How to make an expandable drop down list in Excel

After creating a dropdown, your first concern may be as to what happens when you add new items to the source data. Will the dropdown list update automatically? If your original data is formatted as Excel table, then yes, a dynamic drop down list discussed in the previous examples will expand automatically without any effort on your side because Excel tables are expandable by their nature.

If for some reason using an Excel table is not an option, you can make your dropdown list expandable in this way:

  • To include new data automatically as it is added to the source list, add a few extra cells to the arrays referenced in your formulas.
  • To exclude blank cells, configure the formulas to ignore empty cells until they get filled.

Keeping these two points in mind, let's fine-tune the formulas in our data preparation table. The Data Validation rules do not require any adjustments at all.

Formula for the main dropdown

With the fruit names in A3:A15, we add 5 extra cells to the array to cater for possible new entries. Additionally, we embed the FILTER function into UNIQUE to extract unique values without blanks.

Given the above, the formula in G3 takes this shape:

=UNIQUE(FILTER(A3:A20, A3:A20<>""))

Formula for the dependent dropdown

The formula in G3 does not need much tweaking - just extend the arrays with a few more cells:

=FILTER(B3:B20, A3:A20=D3)

The result is a fully dynamic expandable dependent drop down list:
Making an expandable drop down list in Excel

How to sort drop down list alphabetically

Want to arrange your dropdown list alphabetically without resorting the source data? The new dynamic Excel has a special function for this too! In your data preparation table, simply wrap the SORT function around your existing formulas.

The data validation rules are configured exactly as described in the previous examples.

To sort from A to Z

Since the ascending sort order is the default option, you can just nest your existing formulas in the array argument of SORT, omitting all other arguments which are optional.

For the main dropdown (the formula in G3):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))

For the dependent dropdown (the formula in H3):

=SORT(FILTER(B3:B20, A3:A20=D3))

Done! Both drop down lists get sorted alphabetically A to Z.
Sorting a drop down list alphabetically

To sort from Z to A

To sort in descending order, you need to set the 3rd argument (sort_order) of the SORT function to -1.

For the main dropdown (the formula in G3):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)

For the dependent dropdown (the formula in H3):

=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)

This will sort both the data in the preparation table and the items in the dropdown lists from Z to A:
Sorting a drop down list descending

Tip. Another fast and easy way to enter information in Excel spreadsheets is a data entry form.

That's how to create dynamic drop down list in Excel with the help of the new dynamic array functions. Unlike the traditional methods, this approach works perfectly for single and multi-word entries and takes care of any blank cells. Thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel dependent drop down list (.xlsx file)

Latest comments

  1. Hi Team, Thanks for this post. I am returning to heavy duty excel work back after several years and ablebits was my go to resource that point in time and today as well. Thanks for keeping the content updated and relevant.
    I created the first and second drop down list as instructed and it works perfectly for different selections in first list. HOWEVER, as soon as i copy and paste the validations in rows below, this method won't work for secondary drop down list, because the formula setup "=FILTER(B3:B15, A3:A15=D3)" is only looking for value in cell D3. So If i happen to make different selections in D4, D5 or D6 then corresponding values in E4, E5 and so on will only refer to selection made in D3. How to overcome this problem?

    1. Hi! If I have understood your problem correctly, you need to create a separate Preparation table for each dynamic drop-down list. As you can see from the example, the data validation formula simply contains a reference to the cell containing the formula "=FILTER(B3:B15, A3:A15=D3)". Unfortunately, this formula cannot be used directly in data validation.

  2. I have hypens included in several of my first drop down list, this is creating problems with my dependent lists. Good way to resolve?

    1. Hi! Without seeing your data, it is impossible to understand what problems you are having. I will try to guess.
      Hyphens in your first Excel drop-down list can definitely throw wrench into dependent lists—especially if you're using named ranges and INDIRECT function, which doesn’t work well with special characters like hyphens.
      Solution: Use MID function or SUBSTITUTE function to clean the input.
      Here’s how you can fix it:
      1. If your first drop-down list contains items like 100-Tops, you can use a formula to extract just the part you need for the dependent list name.
      Example formula for dependent List:

      =INDIRECT(MID(A1, 5, 99))

      This skips the first 4 characters (100-) and uses the rest (Tops) to match your named range.
      2. Use SUBSTITUTE to remove hyphens
      You can clean the input like this:

      =INDIRECT(SUBSTITUTE(A1, "-", ""))

      This removes hyphen so 100-Tops becomes 100Tops, which can match named range like 100Tops.
      3. Create Named Ranges without hyphens. Make sure your named ranges don’t include hyphens. Excel doesn’t allow them in range names anyway, so use underscores or remove them entirely.

  3. Thanks for this, I've had the following working for quite some time:
    =SORT(UNIQUE(FILTER(Sites!$A$3:$A$1502,Sites!$A$3:$A$1502 "")))
    I would like to expand on this. If this formula is in column B, how I can create the dynamic list from column A, but exclude the value in THAT rows' A cell? For example, A1="Alaska". How can I use the formula in B1 to include all other values from column A except "Alaska"?

    Thanks again!

  4. Hello,

    I have a wonderful dynamic dropdown, but would love to know if there is a way to clear cells when an upper dropdown selcetion has been chosen?

    For instance, I choose the proper selections four levels down, but say I want to go back to level 2 and change that to a different selection. I would like level 3 & 4 to then get cleared or no longer have the old data, and then user may select level 3 and 4 data with the new 2nd level selection.

    Level 1: A Level: A
    Level 2: B Level: B1
    Level 3: C Level:
    Level 4: D Level:

    1. Hi! You can only automatically delete values in cells that have Level 3 drop-down list and Level 4 drop-down list if you have changed value in Level 2 drop-down list by using VBA macro.

  5. Hello, for some reason the formula with a # ( =$H$3#) sign does not work on Excel for Mac in the 16.78.3 (2023), can you please recommend an alternative approach?

    1. Hello Artem!
      The formula =$H$3# might not work in Excel for Mac version 16.78.3 (2023) for several reasons:
      1. Cell Formatting: Ensure the cell where you're entering the formula is set to a number or general format, not text.
      2. Calculation Settings: Check if the calculation mode is set to "Automatic" under Excel -> Preferences -> Calculation.
      3. Check if there is an error in cell H3, e.g. #SPILL error. Read more: Spill range reference.
      4. Macro Security Settings: If the formula involves macros, ensure macros are enabled under Excel -> Preferences -> Security & Privacy -> Macro Security.

  6. Hi.

    Is there any way to auto populate a dynamic dependent dropdown list if there's only one value available?

    1. Hi! Excel does not have a standard way of automatically populating a cell with a value from a drop down list. However, you can try to do this using a VBA macro.

  7. Thank You!,
    Is there a way to clear the selected items once I've changed the dependent?
    Again Thank You!!!!!!!!

  8. Hi,
    What happens if you have multiple dynamic dropdowns,
    Many rows where you choose different fruits and then need the exporters available in column E
    D3 has Apricot and the choices are available as expected in E3 - data comes from $F$8#
    D4 has Orange but the choices available in E4, are still for Apricot, as the Exporter list is still referring to the Fruit in D3. $F$8#

    1. Hi! Try carefully using the instructions in the third section of this article: How to make an expandable dropdown list in Excel.

      1. I think you didn't get the question.
        They have a column to apply the validation, not only one row.
        It does not work because the FILTER refers only to a one single cell.
        If they enter Apricot in D3, the list available for E3 is good
        Then, they entered Orange in D4, the list in E4 is the list for Apricot, instead of list for Orange

        1. Yes, facing the same issue, unable to see the list update for cell A2 After moving from cell A1

  9. To use the Filter function but it links to another worksheet

  10. Thank you, but my excel is in new version and it does not have filter and unique formula, can you please help?

  11. Hello,

    I have question about first section : How to make dynamic drop down list in Excel.
    D3 is your main dropdown cell. What if i want to have dropdown cell in another rows (D4, D5.... ) with same filter options and sorce data?

    Thank you

      1. Hi! The quoted article explains how to extend the main dropdown to further rows. Is there a way to do the same with the dependent dropdown?

        That is, with the example of this article, make every cell in the E column have a dropdown that depends on the value of the cell in the D column of the corresponding row.

        Thank you

  12. I am trying to create a drop down of product names, but each name has three or four specific fields of dependent data to that product name that I want to come over as it is selected from the drop down. Once an item is selected from the drop down, a calculator will reference that data to come up with a specific number. Is that possible?

    1. Hi! A drop-down list creates a standard text string. No references or data fields are possible. With the selected value, you can then use a formula to retrieve the data associated with it. For example, using VLOOKUP or INDEX+MATCH. I hope I've understood what you're asking.

  13. Is it possible to make the pulled data editable? When I change this data it disappears. Thank you!

  14. How do you make an indirect dynamic drop down for excel 2010? I managed to get the dynamic lists to work, the primary dropdown to work, but its dependent drop down doesn't work. Help most appreciated.

  15. I would like to create many (dozens or more) drop down lists, for many separate cells in many rows of a spreadsheet. Each drop down list is dynamic and based on a formula that I can probably make identical (or nearly identical) to all the others. For example, the formula for the pulldown list might be this:
    XLOOKUP($L$21, $Q$2:$Q$8,CHOOSECOLS($S$2:$AB$8,1,3,5,7,9),"ERR",0) where $L$21 (the variable entry) determines the entry I want to match to column Q, and the pulldown items are in some part of the columns in S-AB.

    I am pretty sure I could make the formula identical in each drop down with a little more work, perhaps by using the row number of the item being searched but I'm trying to avoid volatile functions so haven't worked on this yet.

    In any case, is there a way to "mass produce" drop down lists, either with an identical formula or better, a slightly variable one that can be copied down a column. It would save a lot of time if so. Thank you for the great work you do.

  16. Thanks for the information! If I've created the dropdown lists, how would I automatically place the source formula for the remaining cells? It seems like if I copy and paste the =indirectC3 to the row the second set of data won't show up. I've been manually typing in =indirectc3, indirectc4, so on and so forth. How can I resolve that issue?

  17. Hi there,
    Thanks for the tutorial, it is very helpful. How to do the same in two different Excel sheets, I mean having the main table in the first sheet and the data source in the second sheet?

  18. Hi,

    I have a master workbook containing a code list (B2:B2000) that gets updated throughout the year, currently with data only in cells B2:B15. I am setting up a template workbook with a data validation list referring to the code list, however because both the master workbook and template workbook need to be kept open for data validation list to work this is problematic. The template will be used by multiple users to create their personal workbook, hence why it is impractial to update the code list on all the user workbooks.

    As a workaround, in the template workbook I have referenced the list from the master workbook, and then set up the drop down list in the template using data validation. However, the drop down list now contains zeros for all of the cells in the master workbook that currently don't contain any data yet (B16:B2000). How do I go about removing the zeros from the list, seeing as I cannot a dynamic data validation list in this instance?

    Or there an better solution altogether in using a data validation list in a workbook that refers to a dynamic list in another workbook?

  19. What if I have multiple rows of drop downs?

  20. This tutorial worked brilliantly. Thank you so much for sharing this information. I have been able to setup dependent drop down list for three data collection points. I did run into one problem. I think I may have missed something. While I was able to get it to work in the initial cells that I have setup I can no make the formula apply to the entire column despite special pasting the formula. I was wondering how to get the dependent drown down list to work for multiple rows?

    Any help would be appreciated. Thank you so much.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)