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 or Excel 365
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 column separately.

Notice that the items in the first column (Product) will be 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). You will add the drop-down list to this table.

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, let’s see:

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 or Excel 365

If you are in Excel 2021 or Excel 365, there is another way can quickly create a dynamic dependent drop down list by using new functions UNIQUE and FILTER.

Supposing your source data is arranged as screenshot shown, 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 list will be updated 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)