How to create dependent cascading drop down list in Excel?

Supposing you have 3 lists as below demo shown, and you need to create a cascading drop down list to limit the choices in the second drop down list based on the value in the parent one. For example, parent drop down lists locate in column E with values of Coffee, Tea and Wine. When selecting Coffee from the parent drop down list, only the coffee items display in the second drop down list. This article will show you methods to achieve it.

Create dependent cascading drop down list with indirect function
Easily create dependent cascading drop down list with an amazing tool
More tutorial for drop down list...

Create dependent cascading drop down list with indirect function

Please do as follows to create a dependent cascading drop down list in Excel.

1. Select the cells which containing the values you will display in the parent drop down lists, and then name the range in the Name Box (here I name the three column headers as drinkstuff in my case). See screenshot:

2. Next, name the corresponding Coffee, Tea and Wine items as Coffee, Tea and Wine separately in the Name Box as below screenshot:

3. Select a cell or multiple cells to locate the parent drop down list, click Data > Data Validation > Data Validation. In this case, I select cell E2.

4. In the Data Validation dialog box, you need to:

• 4.1 Select List from the Allow drop down list;
• 4.2 Enter =drinksuff into the Source text box;
• 4.3 Click the OK button.

Note: Drinkstuff is the range name you specified in step 1. Please change it based on your needs.

5. Select a cell or multiple cells to locate the second drop down lists and click Data > Data Validation > Data Validation to open. Here I select cell F2.

6. In the Data Validation dialog box, please configure as follows.

• 6.1 Select List from the Allow drop down list;
• 6.2 Enter =INDIRECT(E2) into the Source text box;
• 6.3 Click the OK button.

Note: In this case, E2 is the first cell contains the parent drop down list.

Now the cascading drop down list is created. When selecting Coffee from the parent drop down list, only the coffee items display in the second drop down list. When Tea is selected in the parent drop down list, you can only select the tea items from the second drop down list. See screenshot:

Create dependent cascading drop down list with an amazing tool

If the above method is time-consuming for you. Here highly recommended the Dynamic Drop-down List feature of Kutools for Excel for you. With this feature, you can create a dependent cascading drop-down list with several clicks only.

1. Click Kutools > Drop-down List > Dynamic Drop-down List to enable the feature.

2. In the Dependent Drop-down List dialog box, please configure as follows.

• 2.1 Select 2 Levels dependent Drop-down list option in the Type section;
• 2.2 Specify the whole table range in the Data Range box;
• 2.3 Check the Dynamic drop-down list horizontally box;
• 2.4 Select cells across two columns to output the list in the Output Range box;
• 2.5 Click the OK button. See screenshot:

Now a dependent cascading drop-down list is created.

Related articles: