Home > Validation > Dependent > 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.

How It Works

1. A named range is set up for the main list.
2. A named range is set up for each item in the main list - using the exact text.
3. In the data entry cells, the main list has a normal data validation drop down list.
4. 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.

Dynamic Array Formulas

If your version of Excel has dynamic arrays, you can use the new Excel functions to create a dependent drop down list. See the video and written steps on the Dependent Drop Down - Dynamic Arrays page

NOTE: Dynamic arrays are available in Microsoft 365 plans.

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 Dependent Lists Using Tables page

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.

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

• If you are using the Simple Dependent Drop Down technique, see the formula and macro examples here.
• If you are using the Flexible Dependent Drop Down technique, see the macro example here.

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