Dependent Drop Down Setup

Dependent Drop Down Setup

With Excel data validation, you can create dependent drop down lists to show items based on the value in another cell. For example, select "Canada" in cell B2, and see a drop down list of Canadian cities in cell C2. There are several ways to set up the dependent drop downs, and this page helps you pick the method that's best for your requirements.

Simple Dependent Drop Down Lists

This animated screen shot shows how a simple dependent drop down list works. There is a main list (Produce Type) and a dependent drop down (Item)

Fruit is selected as Produce Type in row 3, so the "Item" drop down shows the Fruit list
Vegetable is selected as Produce Type in row 4, so the drop down shows the Vegetable list

There are setup instructions and a video on the Create Dependent Drop Down Lists page.

Dependent drop-down

How It Works

A named range is set up for the main list.
A named range is set up for each item in the main list - using the exact text.
In the data entry cells, the main list has a normal data validation drop down list.
The dependent drop down uses the INDIRECT function to show the related list of items.

When to Use This Method

This simple dependent drop down lists method is good to use when:

There are only a few options in the main list
The main list items will not change
The main list items are one word, so they can be used as range names, following Excel's naming rules

If your lists don't meet these requirements, this method isn't the best fit. You will spend too much time setting up named ranges, and maintaining them.

Flexible Dependent Drop Downs

If your main list has lots of items, or if you want multiple dependent levels, a flexible dependent drop down setup is a better choice. This screen shot shows an example of this technique, with a main drop down (Regions), and 3 levels of dependent drop downs - Country, Area and City.

There are setup instructions and a video on the Create Dependent Drop Down Lists page.

flexible Dependent drop-down

How It Works

This technique requires only two tables

data entry table
lookup table

and 3 named ranges

Items in the first list (Regions)
range to find the correct column in the lookup table
range that excludes blank cells at end of correct column

When to Use This Method

This flexible dependent drop down technique is good to use if any of these conditions exist:

There are many items in the main list
Items will be added or changed in the main list
The main list items contain spaces, or other characters that cannot be used as range names, following Excel's naming rules
You need multiple levels of dependent drop down lists

If your lists meet any of these conditions, this method will be the best fit. You will set up the tables and named ranges once, then edit and add to them when the items change.

Prevent Invalid Selections

After someone selects an item from a dependent drop down, they could go back to the main drop down, and select a different item. That could result in mismatched items in that row.

For example, Fruit could be selected from the main drop down, and then Banana in the dependent drop down. If Fruit is changed to Vegetable later, the Banana selection would be incorrect -- it's not a vegetable.

dependent drop down empty

To prevent invalid selections, you can use a formula or a macro.

Dependent Drop Down Links

Click these links to see more information on dependent drop down lists. Many of the pages have sample files to download, and some have videos that show how to set up the dependent lists.

Create Simple Dependent Lists

Flexible Dependent Drop Downs

Simple Dependent Drop Downs Video

Dependent Drop Downs from a Sorted List

Dependent Lists With INDEX

Dependent Lists With Tables and INDIRECT

Dependent Drop Downs from Pivot Tables