With dependent drop downs, select an item from one Excel drop down list, and the next drop down only show the related items. For example, choose Canada in column C, and only see Canadian provinces in column D's drop down. Now, go beyond those basics – see how to create multiple dependent drop downs – Region, Country, Area and City.
Video: Multiple Dependent Drop Downs
Thanks to Roger Govier, who shared this multiple dependent drop downs technique on my Contextures website, way back in 2014.
Recently, I made a video that shows the steps for setting things up in a workbook, to complement Roger's written steps.
Watch the video to see the steps, from beginning to end. You'll find written instructions and the completed sample file on my website – Dependent Lists Using Tables.
Here is a timeline that shows where each section of the video starts.
NOTE: The Named Range sections go into detail on how the range definition formulas work. You can skip those sections, if you prefer, and just copy the formulas from the website or sample file.
- 00:00 Intro
- 00:24 Source Lists
- 01:41 Start the Workbook
- 04:06 Add More Columns
- 5:59 Named Ranges
- 06:61 Named Range #1
- 08:34 Named Range #2
- 11:45 Named Range #3
- 13:03 Drop Down Lists
- 15:23 Get the Sample File
Quick Overview: Multiple Dependent Drop Downs
Here is a quick overview of how the multiple dependent drop downs are set up.
Create 2 Tables
- Create a data entry table with 4 columns – Region, Country, Area and City
- On another sheet, start a values table:
- Type a list of items for the main list (Region)
- Starting in the next column, type a country list for each Region – do not leave blank columns
- Starting in the next column, type area and city lists.
- Format all these Region/Country/Area/City list as a single formatted Excel table, named tblVal.
Create 3 Named Ranges
Next, set up 3 named ranges –
- _Regions – list of region names for the first drop down
- _MainList – dynamic range will the full column for dependent list
- _UseList – dynamic range with items for dependent list (no blanks)
The formulas for these named ranges are described in the video, and are shown on the instruction page, and in the sample file.
Add the Drop Down Lists
Finally, create data validation drop down lists in the Data Entry table.
- The Regions column uses _Regions as its source list
- Country, Area and City columns have _UseList as their source list
Get the Sample Workbook
The written instructions and completed sample file are on my website – Dependent Lists Using Tables.
There are two sample files to choose from:
- one file has macros to automatically clear dependent cells if a higher level cell is changed
- the other file has NO macros – you can clear the dependent cells manually, when necessary.