Making Dependent Drop-down Lists in Excel – How To

Excel drop down list is one of the most useful features in Excel that are used in a variety of ways from data entry in Excel to interactive Excel dashboards.

Drop down lists serve multiple purposes that includes showing a list of items as a drop down menu within Excel cell thus making it easy for Excel user to make a selection, and secondly forcing user to only select from the list of items within the provided drop down.

Here is one example of Excel drop down list:

In the illustration above you can see that items listed in column A are used to create a drop down list in cell D2.

Such single drop down lists are really simple to make. But often times, situations require using two or more drop-down lists where the second drop down list is dependent on the result selected in the first drop down.

Such drop down lists in Excel are called dependent drop down lists or conditional drop down lists where every next drop down list shows the items depending upon the selection of the previous drop-down list.

Following is an example of dependent drop down lists or conditional drop down lists:

In the above example, under category if vegetables are selected then only the list of vegetables is fetched, whereas if fruits are selected in the first drop down list, second drop down list shows only fruits.

Or in other words, the ‘items’ drop down list is dependent upon the selection under the ‘category’ drop down list. That is why it is called a dependent drop down lists or conditional drop down lists.

Creating a Dependent Drop Down List in Excel

There are many ways to make one, but here are the steps to one of the easiest methods to create dependent drop down lists using Excel tables:

Step 1: Download the example workbook with sample data to work along.

Step 2: Select the data range > hit the shortcut combo CTRL+T, from the pop-up box make shore “my table has headers” is selected > press OK. Now your simple data range is converted to an Excel table.

Alternatively, having the data selected, you can go to Insert tab > in tables group > click table icon and the Excel table pop-up box will appear > make sure my table has headers is selected > press OK.

Step 3: Select the header of the table in cells A1:B1 and go to Formulas tab > in defined names group click define name button > a new pop-up will appear, for name enter category > press OK button.

Step 4: Select the entire table using the mouse or have an active cell inside the table and press CTRL+A combo twice > hit CTRL+SHIFT+F3 combo to invoke define name pop-up > in the pop-up make sure only top row option is ticked > press OK.

Step 5: On the worksheet go to the cell where you want your first drop down list of “category” to appear. For me, it is cell D2.

Go to data tab > in data tools group click data validation button > in the pop-up that appears, from the allow drop down select “list” > press F3 key on the keyboard > select the “category” from the pop-up > press OK button.

Step 6: Go the cell where you want the dependent drop down list to appear. For me, it is cell E2. Having the cell selected > click data validation button again > under allow select list > in the source input bar type following:

=INDIRECT(D2)

Press OK button.

Remember D2 is the cell that has the first drop down list.

Don’t panic if you get the following error as you might have nothing selected in the first drop down list. Just press yes to proceed.

And now you have the fully functioning dependent drop down lists that show the appropriate results in the second list based on the selection in the first list.

And the best thing about this approach is that if we add or subtract items from the Fruit or Vegetable lists, respective drop down lists will automatically update accordingly.

Have a look:

Download Example File of this Tutorial