Excel Dependent Drop Down Lists Using Tables
A common question I see being asked on Excel forums is how to create Excel dependent drop down lists, that is to say that the list you get from a validation rule must depend on what has been selected in another cell.
Using tables makes it so easy
Assume you have the following list of continents and you want your drop-down to display only the Countries of that continent.
Excel Dependent Drop Down Lists
The first step to is convert range F1:F4 into a table. To do so, select F1:F4 and do CTRL+T to convert it into a table, then rename the table to Continents. Do the same for range H1:H9 and rename the table to EuropeCountries. Then I1:I5 and rename it to AsiaCountries. Then J1:J6 and rename it to AmericaCountries.
Excel Dependent Drop Down Lists
Adding the validation rules
Select cell A2, go to DATA > DATA Validation and add the following validation rule:
Inside the INDIRECT functions we are placing the reference to the first table we have created where Continents refers to the name of the table and [Continents] is the header name, so if you are using another data set you have to do: TableName[HeaderName]. Think of it as a sort of SQL where you would be doing SELECT Continents From Continents. After you have added this you can see your drop-down list:
In cell B2 we will now add our second validation rule which will depend on what we have selected in A2. Select B2, go to Data Validation and add the following rule: =INDIRECT(A2&”Countries[“&A2&”Countries]”)
The above formula is concatenating what you have selected in A2 with the word Countries. Remember we have named the countries tables as “EuropeCountries”, “AsiaCountries”, “AmericaCountries”? This is what links both drop down lists.
Excel Dependent Drop Down Lists
The magic of Excel Tables
By using tables in your validation rules, whenever you add new data at the bottom of a table your validation rule will automatically pick it up, the same goes if you delete one item from your table.
Expanding it to a cascade Drop-Down List
The above example can easily be expanded to a cascade drop-down list. Imagine that you want to add another Drop Down in column C for the cities. You would create new tables for each Country and name them CountryCities, for instance PeruCities, BulgariaCities etc. Then use the same logic to apply your validation rule in cell C2.
Excel Dependent Drop Down Lists
Creating a long Cascade
This method works well for a cascade of up until 3, maybe 4 steps. For longer cascades we will need to venture into programming, where it is also possible to do it by pulling data from a Database like Access or MySQL and doing it via a UserForm. But this will be the topic of another post.