Dependent drop-down lists in Excel

In a recent entry I talked about how to insert drop-down lists in Excel. Today I would like to show you how to insert dependent drop-down lists. Basically what this means is to have 2 or more drop-down lists with the second one having as content a range that depends on the selection made in the first one. Look at the images below:

There is a drop-down list for a list of countries. Depending on the country chosen, the second drop-down list will display the list of cities from that country.

And how do we get that effect?

To understand how this effect is achieved we must perform four steps:

1. Have ready the data to go to drop-down lists

In a separate sheet we have all the data to be entered in the drop-down lists.

2. Assign the name of the country to each range of cities via define name

This step is very important. We assign the name of each country (with exactly the same name that was entered in the list of countries) to the range of cities in each country.

To do this quickly you can do the following:

Select the cities of a country. In my case I am selecting the cities of Peru.

Now in the box that I have highlighted, type the name of the country, as it is on the list of countries.

Make this step for all countries and cities.

3. Create the first drop-down list

Now create the first drop-down list. If you do not know how to create it, go to

drop-down lists in Excel. The drop-down list will be entered in cell C3.

With the following result:

4. Enter the dependent drop-down list

Now is the time to introduce the dependent drop-down list. It is very important to understand how the INDIRECT function works.

When creating the drop-down list, instead of a range of cells, we enter the following formula:

=INDIRECT(C3)

C3 being the cell where the first drop-down list sits.

And that's it. Now whenever you choose a country from the first drop-down list, the second will display only the cities that correspond to the country.

Download:

To download the example file click on the following link:

Dependent drop-down lists

Subscribe and get all entries via email

You can do it by providing your email into "Join the newsletter" box in the right sidebar.

Or follow me via Social Media

Facebook, Twitter and Google+