Home > Blog

Excel – how to make a drop-down dependent on another drop-down

Sometimes in Excel, you want to make a selection dependent on another choice.  For instance, you would like to select a country from a list specific to a region.  Thus the user first selects a region, and then only countries from that region are presented in the following drop-down.

To do this, you need to use Data Validation rules, the INDIRECT function and specifically named ranges.

Using the listings of countries available on the internet, for instance, here, we can build a list of countries by region.

list of countries grouped by region

First, we enter the data that will be in the first drop-down box, in this case, the regions.  Then these entries are grouped under one name.

To name this range, select all the regions and type in the name “regions” into the address box at the top left and hit return.

how to name a range in Excel

Then do the same for each list of countries, naming each the same as one of the possible selections in the 1st drop-down box (i.e. one of the regions)   

name all the ranges

Now that the data is all in place, we can set up the drop-down boxes.  To do this, we use data validation to present a list.

Cell A2 has the data validation set to

  • List
  • =regions

Cell B2 has the data validation set to

  • List
  • =indirect(a2)

country data validation

Now whatever is entered in the “Regions” drop-down listing will drive the choices offered in the Country drop-down.

It is important to note that the choices offered in the 1st listing can not have spaces in (as named ranges are not allowed to have spaces in).  If it is necessary to have spaces in the 1st choice, this restriction can be circumvented by using a Vlookup within the indirect.

To do this, you need to set up an additional range where the names in the 1st choice are linked to the named ranges that don’t have spaces.

how to deal with spaces

Miles Goodchild

Miles Goodchild has been a Program and Programme Planner, PMO lead and Project Manger since 1998.  He enjoys using MS Office, especially Project, Excel and Visio to make life easier and simpler.  In the course of this he created SummaryPro.  In this blog he shares some of the tips and techniques he has learnt over the years in the hope that they will be useful to you.