Dependent Drop-down

While working with Excel, we all have created or gone through Combo box. It is a dropdown that gives you plenty of options from where you are supposed to choose. Excel dropdown is a valuable feature commonly used when creating a user form or Excel dashboard.

However, you might often require adding more than one dropdown list in your Excel worksheet, wherein the option in the second dropdown is only available if the preferred selection is made in the first dropdown list.

These are called dependent dropdown combo boxes.

This tutorial will briefly cover all the details related to dependent combo boxes, how it is created, the advantages and many more...

What is Dependent Combo Box?

For instance, in the below example, you can check

We have created two drop-down where drop-down 2 is dependent on drop-down 1. There are plenty of options in Drop Down 1, but when I selected the option 'Fruits' in Drop Down 1, only we are shown the fruit names in drop-down 2. Likewise, if we chose Vegetables in Drop Down 1, the vegetable names list is displayed in Drop Down 2.

So, here, dop-down 2 is a Dependent Drop-down.

Steps to Create a Dependent Drop-Down List

Below given are the step-by-step implementation to create a dependent drop-down list in Excel worksheet:

1. Put your cursor on the cell where you want to place the primary drop-down list.
   
2. Go to the Data tab. Click on the Data Validation option. Excel will immediately open the data validation window.
   
3. In the data validation box, there will be three tabs. Click on the settings option. From the Allow: drop-down, choose the list option.
   
4. In Source field, we will provide the reference of the cells that contains the data that we want to put in our primary drop down list. In our case: we have entered the range A2: A5.
   
5. Finally, click on the OK button. It will create your primary Drop-Down (drop-down 1) in the selected position.
   
6. It will create the main dependent drop-box for you. Refer to the below image.
   
7. Next, we will create the dependent drop-box list for fruits. Select all the options in the data set.
   
8. Go to the Formulas tab. Under the Defined Names section, click on Create from Selection option. It will automatically take the list from the defined Selection.
   
9. The 'Create Named from Selection' dialogue window will appear. Since in the selected list, the heading is on the left, we will choose the left column option in the resulting window. Once done, click on the OK button.

NOTE: Following the above step will quickly create 2 named ranges ('Fruits' and 'Vegetable'). The Fruit named range will contain the list of all fruits, whereas the named Vegetable range will have the list of all the vegetables.

10. Put your cursor on the cell where you want to place the primary drop-down list. In out case we have selected the cell
    
11. Go to the Data tab. Click on the Data Validation option. Excel will immediately open the data validation window.
    
12. The data validation window will appear. In the Allow: field, make sure to select the list option.
13. In the Source field, type the formula =INDIRECT(G2), where G2 represents the cell that contains our primary drop-down list.
    
14. Click on Ok.
15. The above steps will create a dependent drop-down list in your cell E2.
16. Now, when you make the selection in the main drop-down list, the options specified in the second Drop Down will automatically update. In the primary drop-down, if you select the option Fruits, you will notice that all the lists containing the various fruits option will be displayed in the dependent drop-down.
    
17. Similarly, if you select vegetable in the primary drop-down, the chosen vegetable options will appear in the dependent drop-down.
    

Explanation- The conditional drop-down list (present in the cell H2) contains the formula =INDIRECT(G2).

It represents that when we select 'Fruits' in cell D4, the dependent drop-down list in E3 implies to the named range 'fruits' (with the help of the INDIRECT Function) and hence returns all the items in that category.

Remove the items of Dependent Drop-Down List in Excel Automatically

After creating the parent and the child drop-down, any modification made in the parent drop-down would not apply to the child drop-down. The dependent drop-down list would not change and, therefore, bit would lead to a wrong entry in your Excel worksheet.

For instance, if you choose the 'Fruits' category and choose Apple from the dependent drop-down, then you return again to the main category and this time, you decide to change the main category to 'Vegetables', but this time the dependent drop-down will continue to show Apple as the item.

A problem right! But here is the crack.

Using Excel VBA, you can easily ensure that the contents of the dependent drop-down list should reset automatically whenever there is any change in the primary drop-down list.

Follow the steps to quickly remove the items of dependent drop-down list in Excel within a single click using VBA code:

1. From the Excel ribbon menu, click on the Developer tab.
2. From the listed options, click on the Visual Basic option. It will open the visual basic editor window. (Or you can also press the shortcut key Alt+ F11 to directly open the Editor window).
   
3. Click on Insert-> Module. It will open the new window.
   
4. Paste the VBA code to clear the contents of a dependent drop-down list:

Code:

• Once done close the visual basic editor window.

As a result, now, whenever you make any changes in the primary drop down list, the VBA code will shoot up and it will remove all the elements from the dependent drop-down list. (refer to the below image)