Understanding Multi-Level Dependent Drop-down Lists
Multi-level dependent drop-down lists are where the options in one list depend on the selection made in another. For example, if you select a country from one drop-down, the next drop-down list will show only the regions or states related to that
country.
Method 1: Dynamic Dependent Drop-down Lists
This method leverages
the UNIQUE, SORT, and FILTER functions to create a dynamic, spillable array that updates automatically as your data changes. It's great if you only have 2 levels.
Step 1: Set Up Your Data
Begin with your
drop-down list data structured in an Excel Table. This table should include columns for your main category (e.g., Country) which is level 1, and the dependent category (e.g., Region) which is level 2.
Step 2: Create a Unique List for Level 1
Use the UNIQUE function combined with SORT function and TRANSPOSE function to extract a list of your level 1 values, in my case it's countries, that dynamically updates.
Here's the formula:
=TRANSPOSE( SORT( UNIQUE(TblRegions[Country]) ) )
You can see it spills the results across the row:
Step 3: Extract the Regions Based on Selected Country
Use the FILTER function to create a dynamic list of regions for each country:
Cell H5:
=SORT(FILTER(TblRegions[Region], TblRegions[Country]=H4,""))
This formula filters the regions corresponding to the country in row 4:
Copy the formula across as many columns as required to allow for growth in the number of countries you might add to the table.
Tip: do not left click and drag to copy this formula. You must copy and paste to prevent the table references from
changing.
Step 4: Set Up Data Validation
Select the cells where you want the drop-down list to appear, go to the Data tab > Data Validation.
Set the validation criteria to allow a list, and reference the spilled array created by your UNIQUE function for countries:
And for the region drop-downs again, select the cells you want the drop-downs, then Data tab > Data
Validation:
=XLOOKUP(B5, $H$4:$P$4, $H$5:$P$5)
Limitations of Method 1:
This approach requires you to anticipate the maximum number of countries you might add, which can be cumbersome if your data grows significantly.
It also doesn't allow for you to add another level of dependency, like cities.
Method 2: Multi-level Dependent Drop-down Lists
This advanced method uses the CELL function to automatically detect the last edited cell, making the setup more flexible and scalable,
especially if you have multiple levels of dependent drop-down lists.
Thank you and shout out to Peter Bartholomew for sharing this technique with me.
Step 1: Define Name for First Level
Define a name for the first drop-down list (level
1). In this example it's my country column in the table (e.g., "countries";).
Step 2: Set Up the Country Drop-Down List
In your data validation setup (Data tab > Data Validation), reference the named range for the country list:
This defined name will automatically include new items in the table, even if it's on another sheet.
Step 3: Create a Dependent Drop-Down List for Regions
Use the CELL function to dynamically track the last edited cell and
use the INDIRECT function to reference it in your FILTER function (see video for demonstration):
=FILTER(RegionsTbl[Region], RegionsTbl[Country]=INDIRECT(CELL("address")), "Choose a Country
First")
This setup allows the drop-down list for regions to update
based on the country selected, without needing to predefine the number of countries.
Step 4: Trigger the Dependent Drop-Down
Ensure that the CELL function triggers correctly by editing the country cell, which will automatically refresh the dependent drop-down list for regions.
Adding
More Levels and Handling Changes
This method also allows for adding more levels, such as cities:
By simply modifying the existing formula to reference the next dependent list:
Important Note: The formula relies on the last edited cell being the previous drop-down list. If you need to edit a previous selection, make sure to re-edit the earlier drop-down list to ensure the dependent lists update correctly.
Method 2 Variation: Double-Click Drop-downs
If you want more flexibility, such as editing a previously selected drop-down list, you can use the double-click method.
Double-Click the cell containing the drop-down you want to modify > select the new option from the drop-down list:
To accommodate this, use the OFFSET function to return a reference to the cell that is one cell to the left of the last edited cell.
Limitation: This method may not be intuitive to users, but
once you demonstrate, they should be comfortable using it.
Next Steps
While both methods provide dynamic solutions for multi-level dependent drop-down lists in Excel, the second method with the CELL function offers more flexibility, especially when dealing with large datasets or multiple levels of dependency.
The use of the CELL function, in particular, is a game-changer, allowing for a more seamless and scalable setup.
If you're ready to dive deeper into the capabilities of Excel's dynamic array functions, be sure to explore our detailed guide on the FILTER function. Happy Excel-ing!
Learn With Us
