Watch Video Creating a Dependent Drop Down List in Excel Show An Excel drop down list is a useful feature when youre 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 selectionfrom 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. 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 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. Here are the steps to create a dependent drop down list in Excel:
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 thatwhen 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.
Reset/Clear Contents of Dependent Drop Down List AutomaticallyWhen 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: 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 SubThe credit for this code goes to thistutorial by Debra on clearing dependent drop down lists in Excel when the selection is changed. Here is how to make this code work:
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 youre 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 thedependent drop down lists:
The formulauses 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 isnt, 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: Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster |