How to Create a Dependent Drop Down List in Excel

Watch Video – Creating a Dependent Drop Down List in Excel

An Excel drop down list is a useful feature when you’re creating data entry forms or Excel Dashboards.

It shows a list of items as a drop down in a cell, and the user can make a selection from the drop down. This could be useful when you have a list of names, products, or regions that you often need to enter in a set of cells.

Below is an example of an Excel drop down list:

Dependent Drop Down List in Excel - Simple list

In the above example, I have used the items in A2:A6 to create a drop down in C3.

Read: Here is a detailed guide on how to create an Excel Drop Down List.

Sometimes, however, you may want to use more than one drop down lists in Excel such that the items available in a second drop down list are dependent on the selection made in the first drop down list.

These are called dependent drop down lists in Excel.

Below is an example of what I mean by a dependent drop down list in Excel:

Dependent Drop Down List in Excel - Demo

You can see that the options in Drop Down 2 depend on the selection made in Drop Down 1. If I select ‘Fruits’ in Drop Down 1, I am shown the fruit names, but if I select Vegetables in Drop Down 1, then I am shown the vegetable names in Drop Down 2.

This is called a conditional or dependent drop down list in Excel.

Creating a Dependent Drop Down List in Excel

Here are the steps to create a dependent drop down list in Excel:

  • Select the cell where you want the first (main) drop down list.
  • Go to Data –> Data Validation. This will open the data validation dialog box.Dependent Drop Down List in Excel - Conditional - Data Validation
  • In the data validation dialog box, within the settings tab, select List.Dependent Drop Down List in Excel - Conditional - List
  • In Source field, specify the range that contains the items that are to be shown in the first drop down list.Dependent Drop Down List in Excel - Conditional - DD1
  • Click OK. This will create the Drop Down 1.Dependent Drop Down List in Excel - DD1 Demo
  • Select the entire data set (A1:B6 in this example).Dependent Drop Down List in Excel - Select Entire Range
  • Go to Formulas –> Defined Names –> Create from Selection (or you can use the keyboard shortcut Control + Shift + F3).Dependent Drop Down List in Excel - Create from selection
  • In the ‘Create Named from Selection’ dialog box, check the Top row option and uncheck all the others. Doing this creates 2 names ranges (‘Fruits’ and ‘Vegetables’). Fruits named range refers to all the fruits in the list and Vegetables named range refers to all the vegetables in the list.Dependent Drop Down List in Excel - Top row
  • Click OK.
  • Select the cell where you want the Dependent/Conditional Drop Down list (E3 in this example).
  • Go to Data –> Data Validation.Dependent Drop Down List in Excel - Data Validation
  • In the Data Validation dialog box, within the setting tab, make sure List in selected.Dependent Drop Down List in Excel - Settings List
  • In the Source field, enter the formula =INDIRECT(D3). Here, D3 is the cell that contains the main drop down.Dependent Drop Down List in Excel - Indirect Function
  • Click OK.

Now, when you make the selection in Drop Down 1, the options listed in Drop Down List 2 would automatically update.

Download the Example File

How does this work? – The conditional drop down list (in cell E3) refers to =INDIRECT(D3). This means that when you select ‘Fruits’ in cell D3, the drop down list in E3 refers to the named range ‘Fruits’ (through the INDIRECT function) and hence lists all the items in that category.

Important Note: If the main category is more than one word (for example, ‘Seasonal Fruits’ instead of ‘Fruits’), then you need to use the formula =INDIRECT(SUBSTITUTE(D3,” “,”_”)), instead of the simple INDIRECT function shown above.

  • The reason for this is that Excel does not allow spaces in named ranges. So when you create a named range using more than one word, Excel automatically inserts an underscore in between words. For example, when you create a named range with ‘Seasonal Fruits’, it will be named Season_Fruits in the backend. Using the SUBSTITUTE function within the INDIRECT function makes sure that spaces are converted into underscores.

Reset/Clear Contents of Dependent Drop Down List Automatically

When you have made the selection and then you change the parent drop down, the dependent drop down list would not change and would, therefore, be a wrong entry.

For example, if you select the ‘Fruits’ as the category and then select Apple as the item, and then go back and change the category to ‘Vegetables’, the dependent drop down would continue to show Apple as the item.

Dependent Drop Down List in Excel - Mismatch

You can use VBA to make sure the contents of the dependent drop down list resets whenever the main drop down list is changed.

Here is the VBA code to clear the contents of a dependent drop down list:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 4 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

The credit for this code goes to this tutorial by Debra on clearing dependent drop down lists in Excel when the selection is changed. 

Here is how to make this code work:

  • Copy the VBA code.
  • In the Excel workbook where you have the dependent drop down list, go to Developer tab, and within the ‘Code’ group, click on Visual Basic (you can also use the keyboard shortcut – ALT + F11).Dependent Drop Down List in Excel - developer
  • In the VB Editor Window, on the left in the project explorer, you would see all the worksheet names. Double-click on the one that has the drop down list.Dependent Drop Down List in Excel - double click
  • Paste the code in the code window on the right.Dependent Drop Down List in Excel - code paste
  • Close the VB Editor.

Now, whenever you change the main drop down list, the VBA code would be fired and it would clear the content of the dependent drop down list (as shown below).

Dependent Drop Down List in Excel - clear content demo

Download the Example File

If you’re not a fan of VBA, you can also use a simple conditional formatting trick that will highlight the cell whenever there is a mismatch. This can help you visually see and correct the mismatch (as shown below).

Dependent Drop Down List in Excel - highlight

Here are the steps t0 highlight mismatches in the dependent drop down lists:

  • Select the cell that has the dependent drop down list(s).
  • Go to Home –> Conditional Formatting –> New Rule.Dependent Drop Down List in Excel - new rule
  • In the New Formatting Rule dialog box, select ‘Use a formula to determine which cells to format’.Dependent Drop Down List in Excel - use formula
  • In the formula field, enter the following formula: =ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0))Dependent Drop Down List in Excel - formula
  • Set the format.Dependent Drop Down List in Excel - format
  • Click OK.

The formula uses the VLOOKUP function to check whether the item in the dependent drop down list is the one from the main category or not. If it isn’t, the formula returns an error. This is used by the ISERROR function to return TRUE which tells conditional formatting to highlight the cell.

Download the Example File

You May Also Like the Following Excel Tutorials:

  • Loui says:

    Excellent stuff
    Thanks

  • D SATHISH KUMAR says:

    good

  • Qazzeem says:

    Very excellent job, thank you very much.

  • Umesh Kumar Yadav says:

    Very Helpfull task

  • Liz F says:

    Wow! This was so helpful and exactly what I was looking for. I knew the solution had to be much more simple than I was originally planning to try.

  • Brad Stines says:

    Hi Could you possibly help me with a slightly bigger formula?

  • Pravin Prasad says:

    Where did my comment go?

  • Satish Singh says:

    I have a issue with name box in excel, which does not accept hypen “/” for eg: “AM/NAME” , please give me suggestion for this issue.
    i am trying to populate dependent drop down box

  • Stef says:

    HI, How can I make the drop down list works for multiple cells and not just in a unique cell?

  • MRJONS says:

    thanks alot very usefull trick

  • Abhishek Goyanka says:

    Hi Sumit,
    How to update the dependent list when you change primary list after initially making some selection? Right now if you select the US and then Alaska, after that if you change it to India, the state still remains Alaska. Please help.
    Thanks

    • Sumit Bansal says:

      Hello Abhishek.. This can be done using VBA. Will try and create it and share with you

      • Abhishek Goyanka says:

        Hi Sumit,

        Really appreciate the help. Please let me know if you are able to write VBA script which accomplishes the task. Thanks again.

  • Cindy says:

    I want to create two cells dependent on the data entered into the first cell. So say I have a list of Company Branches listed by city. Then I have 6 multiple lists that list the Foremen that work in each city AND I have 6 lists of Superintendents that work in each city. I created the city list in cell B2. In the Superintendents cell E3 I used =Indirect(B2) and it lists all the Superintendents working in the city showing. Now in cell E2 I want to have the Foremen that work in each city. I tried =Indirect(B2) which gives me the same list in cell E3. How do I get E2 tied to B2????

  • maria says:

    i have my drop down all done, but when i to look at the list , there is nothing showing, can you help me please

  • Pablo Carrillo says:

    Hi there, is there any way you know to do this but with a list of all countries and regions of the world without having to create as many columns as countries exist? I have the list with two columns, each row per region/country… When I select one of the countries, I need the drop down list to display all the regions of that country.

  • Shanky Singh says:

    Thanks Sumit for sharing this! However, I have find two errros:

    1 – While addition to the data set (As states) and consequently using indirect formula as Indirect(States Name) doesn’t show any options in list.

    2 – Is there any way in which cells should appear empty for that particular row if we change any data set?

  • Deepak says:

    Hey Sumit, I have a doubt – when you choose US in Cell -E2 then the other drop down list in cell:F2 shows US cities. Lets assume, we select Alaska as city in F2 but at the same time we change the country again US to India in E2, then F2 field still shows Alaska.

    Is there a way cell should appear empty if we change the country ?

  • Andres N. says:

    How do i fix this? its not letting me do the data validation following the exact steps.

    • Diego Cestac says:

      I also have the same mistake…anybody could fix it?
      Thanks!!!

  • Tyler says:

    How would you this with a third drop down based on BOTH the previous drop downs? For instance, adding a “City.”

    • Sumit Bansal says:

      Hi Tyler.. Thanks for dropping in.. You can create the third drop down in the similar way. In this case. city would be dependent on selected state. You would need to create named ranges for all states, and the formula would be =INDIRECT(States Name)

  • >