Home > Validation > Drop Downs > Dependent Create Dependent Drop-Down ListsEasy steps for Excel dependent drop-down list. Conditional data validation based on other cell, like Region and City lists. Videos, written steps, get free Excel file. |
What Is a Dependent Drop-Down List?
Video: Dependent Drop-Down Lists
Get Started: Dependent Drop-Down Lists
What Is a Dependent Drop-Down List?In a Microsoft Excel dependent drop down list, the list of items changes, depending on the value in another cell. For example:
There are spreadsheet setup instructions and a video in the sections below. |
Video: Dependent Drop-Down ListsIn this short video, you will see how to set up a main drop-down list, with a dependent drop-down list in the next column. The written instructions are below the video, and the full transcript is on the Dependent Drop-Down Lists Video page. |
Get Started: Dependent Drop-Down ListsHere are the step-by-step instructions for making dependent drop-down lists in Excel. This example uses Fruit and Vegetable lists. In the screen shot below, you can see the completed data entry sheet.
There are a few steps for setting up the drop-down lists, and the details are in the sections below.
Set Up the WorksheetsThis example will have a workbook with two sheets -- a data entry sheet, and a sheet with lists.
|
Create ListsNext, you will create lists with items for the drop-down lists. In this example, you will set up 3 lists. The main list has produce types, and the other two lists have items for the dependent drop downs. To create each list, you will do three things:
1. Type the ListsTo type the lists, go to the Lists sheet. Start with the main list - Produce types.
NOTE: The Produce list has one-word items in it -- Fruit and Vegetable. This is important, because those words will be used as Excel names, and two-word names are not allowed as names. If you need to use multiple-word items in the main list, see: Using Two Word Items Add the Dependent ListsNext, type the dependent lists, with the headings Fruit List (in cell D2) and Vegetable List (in cell F2). These lists can contain one-word items (apple), or multiple-word items (green beans). |
2. Format Lists as TablesNext, follow these steps, to format each list as an Excel Table. This makes your list dynamic -- the list size will adjust automatically if you add or remove items, so your drop down will show the entire list. Follow these steps for each of the 3 lists:
Here is the Lists sheet, with all 3 lists formatted as Excel Tables |
Create a Named RangeNext, follow these steps to create a named range for each formatted Excel Table. Later, you will use this name, when making the drop-down lists on the Data Entry sheet. Name the Produce List
Name the Fruit and Vegetable ListsNext, follow these steps to name the fruit and vegetable lists.
|
Add the Main Drop DownNext, you will add the main drop down (Produce Type) on the DataEntry sheet. This is a normal data validation drop-down list - not a dependent drop down Start the Data Entry SheetFirst, you will enter headings on the data entry sheet, and set up a named table.
Add the Main Drop DownNext, you will add the main drop-down list, in the Produce Type column
|
Add the Dependent Drop DownNext, you will create a dependent drop down list in the Item column. This cell will have a formula to create the data validation drop down list. Read more about the INDIRECT function here.
Note: If cell B3 is empty, you will see the message shown below. Click Yes to continue. |
Advanced Dependent Drop DownsThe instructions in the previous sections show how to set up a basic dependent drop down list. Here are a few advanced examples.
Use Two-Word ItemsIn some workbooks, you might need to have two-word items in the first Excel data validation drop-down list. For example, your choices are 'Red Fruit', 'Green Fruit' and 'Yellow Fruit'
=INDIRECT(SUBSTITUTE(A2," ","")) |
2) Lookup Table and 2nd Drop DownNext, you will create the lookup table, to match each item with its dependent item's range name.
Test 2nd Drop DownIn the example shown below, Red Fruit was selected in cell A2 In cell B2, the data validation VLOOKUP formula will return RedFruitList as the range name for the dependent list. In cell B2, for the drop-down list, the RedFruitList items are displayed when you click the drop-down arrow. |
3) More Lookup Tables and 3rd Drop DownTo create a 3rd dependent drop down list, use the same techniques as shown above, to create lookup tables and items lists. In the screen shot below, there are lookup tables named RedFruitLookup, YOFruitLookup and GreenFruitLookup. Items lists, with the code names and "List", have been added to the worksheet. In the data validation window, use this formula for the 3rd drop down: =INDIRECT(VLOOKUP(B2, INDIRECT(VLOOKUP(A2, ProductLookup,2,0)&"Lookup"),2,0) &"List") The formula finds the lookup table based on the product type selected in cell A2, e.g., RedFruitLookup, and gets the code for the selected product -- Mac. It adds "List" to the code and shows the items in the MacList range. |
Add Third Dependent Drop DownIf you need to, you can add another set of dependent data validation drop down lists that depend on the selections in the first two drop downs. select a country and region, then select a city in the selected country and region. Note that the region names are not unique -- there is a West region in both Canada and the USA -- so we cannot show a City list that is only based on the region names. To make sure that the correct list of cities appears, the city lists will be named for the country and region. There are 2 countries, and each country has 3 regions, so we will set up 6 named ranges for the 3rd level drop down lists. NOTE: If you need more than a few named ranges for your 3rd level lists, try the Dependent Lists with Tables technique instead -- it will be easier to set up and maintain. To set up 3-level dependent lists:
=INDIRECT(SUBSTITUTE(B2&C2," ","")) |
Use Dynamic ListsBecause the INDIRECT function only works with references, not formulas, the previous method for dependent data validation will not work with lists that use formula-based dynamic named ranges, such as OFFSET ranges. Use one of the following solutions when a dynamic list is required:
Named Excel TablesTo avoid the problem, use named Excel tables, instead of formula-based dynamic ranges.
Then, use one of the INDIRECT function examples shown above, to create a dependent drop-down list. Dynamic Name WorkaroundIf you cannot use the Named Excel Tables, use the following method for creating dependent lists from formula-based dynamic named ranges:
=OFFSET(INDIRECT($E2),0,0, COUNTA(INDIRECT(E2&"Col")),1) if two-word items will be used, you can include the SUBSTITUTE function in the formula: =OFFSET(INDIRECT(SUBSTITUTE($F2," ","")),0,0, COUNTA(INDIRECT(SUBSTITUTE($F2, " ","") &"Col")),1) |
Dependent Drop Down with IF FormulaThis is another example of dependent drop-down lists in Excel, with the IF function used with INDIRECT, for a more flexible drop down. In this example:
This video shows the basic set up steps, and then shows how to use the Excel IF function with a dependent drop down. There are written steps below the video, and you can download the sample file below. Select Countries and CitiesIn this example, there’s a drop down to select a country in column A. If you select USA as the country, cities from the USA appear in the dependent drop down in Column B. There are city lists in the workbook, and each list is named to match its country name.
|
Simple Dependent Drop Down FormulaOriginally, there was a simple formula for the data validation list in column B: =INDIRECT(A2) So, if you selected USA in cell A2, dependent drop-down list shows cities from the range named USA. No Country SelectedWith that simple INDIRECT formula, the drop-down list in column B does NOT work, if no country is selected in column A.
|
Add IF Function to FormulaInstead of a drop-down arrow that does not work, we will change the dependent drop down formula, and add the IF function. The revised formula will give users the option to select a World city, if they have not selected a country in column A. On the Lists worksheet, there is another range, named World, highlighted in the screen shot below. Just as you can use the IF function on the worksheet, you can use it in a data validation formula. For the dependent drop-down cells in column B, we’ll change the formula to the following:
After this change to the data validation formula, if you click on a drop down arrow in column B, and no country is selected in column A, the list of world cities appears. How the IF Formula WorksHere is what the revised formula does:
|
Show Long or Short Drop Down ListWith this dependent drop down technique, from AlexJ, you can see a full list of customers in a drop down list. Or switch to a short list, with just your top customers. The technique is driven by a formula -- no macros required. This short video shows the steps, and there are written steps below the video. |
Set Up the ListsThe first step is to create two named lists -- they will be used as the source for the drop-down lists.
Create the Drop-Down ListNext, you will create the drop-down list, by using data validation with an IF formula:
Use the Drop-Down ListSelect the cell with the data validation list and click the drop-down arrow. If the cell does not contain the text "Full List", the drop down will show the short list of top customers. If you need to see the full list, select "Full List" from the top of the drop down list, and then click the drop-down arrow again. |
Prevent Invalid SelectionsAfter 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 Lemon in the dependent drop down. If Fruit is changed to Vegetable later, the Lemon selection would be incorrect -- it is not a vegetable. To prevent invalid selections, here are a couple of techniques that you can use
|
Block Changes in First Drop DownTo block changes to the first list, you can change the data validation formula, so the list does not appear unless the second cell is empty. This video shows you how this technique works, and written instructions are below the video. |
Block Changes in First Drop DownWith dependent drop down lists, problems can occur, if someone goes back to the first list, and changes it. Then, the first and second selections are mismatched -- for example, in the screen shot below, Vegetable is the first choice, and Banana is selected in the second column. To block changes to the first list, you can change the data validation formula, so the list does not work unless the second cell is empty.
Instead of just referring to the Produce range for the Produce Type drop down, the formula will check for an entry in the Item column.
To change the formula:
=IF(C2="", Produce, INDIRECT("FakeRange")) Now, the drop down in the first column will not work if the Item has been selected in that row. |
Add Event Code to Clear the Customer CellWhen you select a Region, event code clears the cell to the right. To add the code, right-click the sheet tab, and click View Code. Paste the following code onto the sheet module. You might need to change the column number, to match the column number on your worksheet. NOTE: The sample workbook also has code for clearing multiple dependent cells. Private Sub Worksheet_Change _(ByVal Target As Range) On Error Resume Next If Target.Column = 2 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset(0, 1).ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub |
Download Sample Files
NOTE: There are other techniques for setting up dependent drop down lists, so see which setup method might be best for your needs. |
Last updated: March 22, 2023 12:55 PM