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 cascading drop-down has always been a problem. 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.

Note. This new dynamic array way of making dropdown lists only works in the latest versions of Excel 365. In pre-dynamic Excel, you will have to do it the long old-fashioned way as described in Creating a dependent drop down in Excel 2019, 2016 and earlier.

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

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

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

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)

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

  1. xaerie says:

    In Sheet 1 I have data validation/dropdown list from a range of cells in Sheet 2. I'm trying to find if it's possible to calculate costs in Sheet 1 depending on what's been selected from the dropdown in DataValidation. I've pasted the specific examples here:

    Column1 Column2 Column3 Column4 Column5
    1 text1 number1 number01 DataValidation1 Calculation based on DataValidation chosen in Column4
    2 text2 number2 number02 DataValidation2 Calculation based on DataValidation chosen in Column4
    3 text3 number3 number03 DataValidation3 Calculation based on DataValidation chosen in Column4
    4 text4 number4 number04 DataValidation4 Calculation based on DataValidation chosen in Column4
    5 text5 number5 number05 DataValidation5 Calculation based on DataValidation chosen in Column4

    A B
    1 DataValidation1 Formula for DataValidation1 to calculate costs. Example: (B2*4)*0.0029 + (B2*1+C2*29)*0.0012 + (B2 * 0.0015)
    2 DataValidation2 Formula for DataValidation2 calculate costs. Example: (B2*4)*0.0029 + (B2*1+C2*29)*0.0012 + (C2 * 0.0015)
    3 DataValidation3 Formula for DataValidation3 calculate costs. Example: (B2*4)*0.0029 + (B2*1+C2*29)*0.0012
    4 DataValidation4 Formula for DataValidation4 calculate costs. Example: (B2*1)*0.0029 + (C2*29)*0.0012
    5 DataValidation5 Formula for DataValidation5 calculate costs. Example: (B2*1)*0.01

    another example:
    A B C D E
    1 Type Capacity Weight Classification Cost
    2 Beam 12 1200 1:29 Calculation based on DataValidation chosen in D2
    3 Other 8 850 1:4 Calculation based on DataValidation chosen in D3
    4 Electronics 95 5240 1:29;1:4 Calculation based on DataValidation chosen in D4
    5 Tools 4 900 1:29;1:4:1:1 Calculation based on DataValidation chosen in D5
    6 Vehicle 3 20000 1:1 Calculation based on DataValidation chosen in D6

    A B
    1 DataValidation1 Formula for DataValidation1 to calculate costs. Example: (B2*4)*0.0029 + (B2*1+C2*29)*0.0012 + (B2 * 0.0015)
    2 DataValidation2 Formula for DataValidation2 calculate costs. Example: (B2*4)*0.0029 + (B2*1+C2*29)*0.0012 + (C2 * 0.0015)
    3 DataValidation3 Formula for DataValidation3 calculate costs. Example: (B2*4)*0.0029 + (B2*1+C2*29)*0.0012
    4 DataValidation4 Formula for DataValidation4 calculate costs. Example: (B2*1)*0.0029 + (C2*29)*0.0012
    5 DataValidation5 Formula for DataValidation5 calculate costs. Example: (B2*1)*0.01

    I don't know if those formulas are going to work but those are the calculations I'm needing to make. A bunch of multiplying and adding in order.

  2. Mario M says:

    Good post. But hod do you do in case of multi level dependendant drop down menu in case you want to have more that one entry row? I mean. The example shows only three cells where to use the drop down menus. If you want to have a matrix type 3*3? Best

  3. YW says:

    Hi, what if i have multiple rows in for the dynamic drop-down? all based on the same set of source table and dependent dropdown? how should i expand to dynamic drop down?

  4. JM says:

    Hi, the same question like Mario M and YW. How to make multiple rows in drop-down table

  5. Ian Wainwright says:

    Hi Svetlana. I really enjoyed the post.

    Currently, when you select another top-level item (fruit) the sub-lists may display invalid options - ie, where the exporter does not deal in that fruit category. Is there a simple way to clear the sub-list selections or is this a job for vba?

  6. Antony Pihut says:

    Thank you a lot!

  7. Pooja says:

    Does excel 2016 has Unique function?? If not, how would I get unique values in dropdown

  8. JackC says:

    Hello
    When I have the table and the primary & dependent drop-downs on the same sheet, it works fine
    However, when I have the table on sheet-2 and the primary & dependent drop-downs on sheet-1, I get a source error on dependent dropdown validation cell (the dropdown works fine)
    So, can the dependent dropdown list be on separate sheet from the table?
    Thank you

  9. Tarl Sagoo says:

    Hi, Thank you for the instructions on how to create multiple dependant drop down lists. I managed to create my own lists on my training spreadsheet, however this would only work where I have a single row of drop down entries. How would I be able to resolve manage multiple rows where I need to collect information for multiple columns from the multiple dependant drop down lists?

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)
Ultimate Suite for Microsoft Excel
Merge two tables
Combine Sheets
Merge Duplicates
Consolidate Sheets
Copy Sheets
Merge Cells
Vlookup Wizard