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:
In the above example, I have used the items in A2:A6 to create a drop-down in C3. Sometimes, however, you may want to use more than one drop-down list 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:
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. In the data validation dialog box, within the settings tab, select List. In Source field, specify the range that contains the items that are to be shown in the first drop down list. Click OK. This will create the Drop Down 1. Select the entire data set (A1:B6 in this example). Go to Formulas –> Defined Names –> Create from Selection (or you can use the keyboard shortcut Control + Shift + F3). 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. Click OK. Select the cell where you want the Dependent/Conditional Drop Down list (E3 in this example). Go to Data –> Data Validation. In the Data Validation dialog box, within the setting tab, make sure List in selected. In the Source field, enter the formula =INDIRECT(D3). Here, D3 is the cell that contains the main drop down. 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.
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: 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). 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. Paste the code in the code window on the right. 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).
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).
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. In the New Formatting Rule dialog box, select ‘Use a formula to determine which cells to format’. In the formula field, enter the following formula: =ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0)) Set the 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
Extract Data based on a drop-down list selection. Creating a drop-down list with search suggestions. Select multiple items from a drop-down list. Create multiple drop-down lists without repetition. Save Time with Data Entry Forms in Excel.
The expanding the data validation pattern cell by cell in excel is not a solution for me as I’m generating the excel file and not creating it manually. thanks for any tips or advice on a workaround of this nature. The original formula: =ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0)) Needs to have a “0” (zero) added to the MATCH function’s third argument. The updated formula is: =ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1,0)),1,0)) For MATCH, zero is an implied default, but some of you – like me – may have had formatting issues based on the original formula due to how it processes the range. This error – for me – was being caused by the MATCH function… so forcing a “0” in the third argument makes for an exact match. Otherwise, you have to sort your menu and that’s a pain. To the author… excellent post and thank you! This is extremely frustrating. I have been using this type of lists for years using indirect to “construct” range names, where the range name reference the list to be used. Can someone please tell me why indirect lost the functionality to construct name ranges in data validation to create lists? If not for your comment I would not have done the above to the letter. It works now. I can say why it did not worked initially. My name range was defined in the “Refer to:” section as =OFFSET(CatPr,0,0,COUNTA(Data!$L:$L)-2,1). This was to ensure we can add more items and not showing blank spaces. However, it seems that indirect does not like it when the name range has a formula in the “Refer to:” section. Which is silly, is it not? Regardless, Thanks On Error Resume Next If Target.Column = “$L” Then If Target.Validation.Type = “$M” Then Application.EnableEvents = False Target.Offset(0, 1).ClearContents I had a problem with your guidance. when I used your formula ” =INDIRECT(SUBSTITUTE(E5,” “,””)) ” Excel show me this error: (a named range you specified cannot be found) Please change ” (E3,” “,””) ” to (E5,” ”,”_”). Maybe someone like me is enough lazy to just copy the formula!!! Thank you very much. Could this be because my first drop down has numbers? I don’t know why else it would not work ?! any help would be great! was this solved? Really appreciate the help. Please let me know if you are able to write VBA script which accomplishes the task. Thanks again. 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? Is there a way cell should appear empty if we change the country ?
