Make a Dynamic Dependent Drop-Down List in Excel (Step by Step)

Here in this tutorial, we will step by step introduce how to create a dependent drop-down list that displays choices depending on the values selected in the first dropdown list. In other words, we will make an Excel data validation list based on the value of another list.

Make a dynamic dependent drop-down list
10s to make a dependent drop-down list with a handy tool
Make a dynamic dependent drop-down list in Excel 2021, Excel 365, and newer versions
Some questions you may ask about this tutorial

Free download the sample file

Video: Make an Excel dependent drop-down list

Make a dynamic dependent drop-down list

Step 1: Type The Entries For The Drop-Down Lists

1. Firstly, type the entries you want to appear in the drop-down lists, each list in a separate column.

Notice that the items in the first column (Product) will serve as Excel names for the dependent lists later. For example, here Fruit and Vegetable will be the Names for column B2:B5 and C2:C6 separately.

See screenshot:

2. Then create tables for each data list.

Select column range A1:A3, click "Insert "> "Table", then in the Create Table dialog, tick "My table has headers" checkbox. Click "OK".

Then repeat this step to create tables for the other two lists.

You can view all tables and the reference to ranges in Name Manager (press "Ctrl" + "F3 "to open it).

Step 2: Create Range Names

In this step, you need to create "Names "for the main list and each dependent list.

1. Select the items that appear in the main list ("A2:A3").

2. Then go to the "Name box" which beside "Formula bar".

3. Type the name in to it, here names it as "Product".

4. Press "Enter" key to complete.

Then repeat the above steps to separately create Names for each dependent lists.

Here names the second column (B2:B5) as Fruit, and the third column (C2:C6) as Vegetable.

You can view all range names in Name Manager (press "Ctrl "+ "F3" to open it).

Step 3: Add The Main Drop Down List

Next, add the main drop down list (Product), which is a normal data validation drop-down list, not a dependent drop down list.

1. Firstly, create a table.

Select a cell ("E1"), and type the first column header ("Product"), and move to the next column cell ("F1"), type the second column header ("Item"). This table will hold the drop-down lists.

Then select these two headers ("E1" and "F1"), click "Insert" tab, and select "Table" in the Tables group.

In the Create Table dialog, tick "My table has headers" box, and click "OK".

2. Select cell "E2" to which you want to insert the main drop down list, click "Data" tab and go to "Data Tools" group to click "Data Validation" >" Data Validation".

3. In the Data Validation dialog,

• Choose "List" in the "Allow" section,
• Type below formula into "Source" bar, Product is Name of the main list,
• Click "OK".

=Product

You can see the main drop-down list has been created.

Step 4: Add Dependent Drop Down List

1. Select cell "F2" to which you want to add the dependent drop-down list, click "Data" tab, and go to Data Tools group to click "Data Validation" > "Data Validation".

2. In the Data Validation dialog,

• Choose "List "in the "Allow "section,
• Type the below formula into "Source" bar, E2 is the cell that contains the main drop-down list.
• Click "OK".

=INDIRECT(SUBSTITUTE(E2," ","_"))

If the E2 is empty (you do not select any one item in the main drop-down list), you will see a message pops out as below, click "Yes" to continue.

Now the dependent drop-down list has been made.

Step 5: Test The Dependent Drop-Down List.

1. Select "Fruit" in the main drop-down list ("E2"), then go to the dependent drop-down list ("F2") to click the arrow icon, see if the fruit items are in the list, then select one item from the dependent drop-down list.

2. Press "Tab" key to start a new row in the data entry table, select "Vegetable", and move to the next cell to the right, see if the vegetable items are in the list, then select one item from the dependent drop-down list.

10s to make a dependent drop-down list with a handy tool

"Kutools for Excel" provides a powerful tool to make a dependent drop-down list easier and faster:

Step 1: Type The Entries For The Drop Down List

Firstly, arrange your data as below screenshot shown:

Step 2: Applying Kutools tool

1. Select the data you have created, click "Kutools" tab, and click "Drop-down list" to display the sub menu, click "Dynamic Drop-down List".

2. In the "Dependent Drop-down List":

• Check the "Mode B" that matches your data mode,
• Select the "output range", the output range column must be equal to the data range column,
• Click "Ok".

Now the dependent drop down list has been created.

Make a dynamic dependent drop-down list in Excel 2021, Excel 365, and newer versions

If you're using Excel 365, Excel 2021 or newer versions, there is another way to quickly create a dynamic dependent drop-down list by using new functions "UNIQUE" and "FILTER".

Suppose your source data is arranged as shown in the screenshot, please follow below steps to create the dynamic drop down list.

Step 1: Using formula to get items for the main drop-down list

Select a cell, for example, cell G3, and using UNIQUE and FILTER functions to extract the unique values from the "Product" list which will be the source of the main drop-down list, and press "Enter" key.

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

Step 2: Create the main drop-down list

1. Select a cell that you want to place the main drop-down list, for example, cell "D3", click "Data"tab, and go to" Data Tools group to click "Data Validation" > "Data Validation".

2. In the "Data Validation" dialog,

• Choose "List" in the "Allow" section,
• Type below formula into "Source" bar,
• Click "OK".

=$G$3#

Now the main drop-down list is created.

Step 3: Using formula to get items for the dependent drop-down list

Select a cell, for example, cell H3, using FILTER function to filter the items based on the value in cell "D3" (the selected item in main drop down list), press "Enter" key.

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

Step 4: Create the dependent drop-down list

1. Select a cell which will place the dependent drop-down list, for example, cell "E3", click "Data tab", and go to "Data Tools" group to click "Data Validation" > "Data Validation".

2. In the "Data Validation" dialog,

• Choose "List" in the "Allow" section,
• Type below formula into "Source" bar,
• Click "OK".

=$H$3#

Now the dependent drop-down list is created successfully.

When you add new items or do some changes in A3:A20, the drop-down lists will update automatically.

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

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

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

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