Create Dependent Drop-down lists in excel

In today’s article, we will see how to create dependent drop-down lists in excel.

When we select one of the Countries, we get the list of cities of the respective country in other drop-down.

Selecting Country

Selecting City

To create these dependent drop-down lists, execute the following steps.

1. On second sheet, create the following named ranges. For creating named ranges. Please click Named Ranges

Name	Range Address
Country	A2:A5
India	B2:B4
USA	C2:C4
China	D2:D4
Germany	E2:E4

Creating named ranges countrywise

2. Now select B2 on first sheet, and click Data validation on the data tab.

Creating Data Validation

3. In the Allow box, select list.

4. In the source box, type =Country and click OK.

Adding to source box in Data Validation

5. And we get the list of countries in cell B2. 

After adding Data validation for country

6. Now select E2 on first sheet, and do the same thing as done above (i.e. click Data validation on the data tab).

7. In the Allow box, Select list and in the Source box, type =INDIRECT($B$2) and click OK.

Using INDIRECT function

8. And result is the list of cities for the selected country.

City wise list after adding INDIRECT function

INDIRECT function: Returns the reference specified by a text string. For example, the user selects INDIA from the first drop-down list. =INDIRECT($B$2) returns the INDIA reference. As a result, the second drop-down contains the list of cities in INDIA. For more information on INDIRECT function.

It is that simple to create dependent drop-down list in excel. Keep practicing.

Any queries or clarifications related to this article, please write me at pareshj@snpinfotech.in or visit www.snpinfotech.in

In my next article, we will see another awesome feature of excel till then keep reading.