How To Create a Dependent Drop-Down List in ExcelStep by Step Instructions with Screenshots

  |
Written By:
Lisa Borga

A drop-down list is very useful when you are creating an input form, and you are probably familiar with them.

They provide a list for the user to make a choice. These lists may include places, names, or any other choices.

But, although a drop-down menu can be very useful, there may be times when you would like to create a dependent drop-down list.

This would make it so that once you make a choice from the primary drop-down list, the choices in the dependent drop-down menu would change accordingly.

So, in this article, we will show you how to create a dependent drop-down menu.

Creating a Dependent Drop-down Box

In order to create a table that shows three options at a café, coffee, tea, and Italian soda, along with the flavors available for each drink, we want to create a drop-down list with the drinks in A1 and the flavors in another drop-down box in B1, which will have options that are dependent on what is chosen in the primary drop-down list.

This will be a dependent drop-down menu.

Below are the steps necessary to make a dependent drop-down list.

Step One

Create names for your categories, in our case, drinks and flavors.

We placed drinks in A2 and flavors in B2, and the types of drinks in E1, F1, and G1.

Now, to give names to each drink in the category row, we need to select E1:G1 and type Drinks in the name box, and press enter.

Step Two

Now we must select our different flavors below each type of drink and give each drink a range.

After this, put the chosen name in the name box.

For the first drink, coffee, the range is E2 through E4.

So, we will write the drink name “Coffee” in the name box.

We will do this for each type of drink for each of the other drinks as well, with Tea having a range of  F2 through F4 and soda G2 through G4.

Step Three

It is now time to make the main drop-down menu.

This will be for the types of drinks and will be placed in A2.

To do this, we will need to activate the cell. So, go to the “Data” tab, and in the “Data Tools” section, find “Data Validation.”

Look at the dialog box for “Data Validation and select the setting tab.

After this, choose the option “List” in the drop-down menu under Allow, and type the formula =Drinks(A2) in the box located under the heading “Source.” Then, click okay.

Step Four

In this step, we will create a dependent drop-down list. It will be placed in B2.

We will activate B2 and navigate to the “Data” tab and choose “Data Validation.” Then click on the  “Settings” tab in the dialog box and select “List” when you see the drop-down list under “Allow.”

Then put =Indirect in the box for “Source.” Click okay, and you will now be presented with a list of flavors that will depend on the drink you choose.

You may get an error message after doing this, as shown below, but just click yes to continue.

How To Automatically Reset or Clear the Contents of a Drop-down List

If you change a value in the primary drop-down, the value in the dependent drop-down box will be invalid.

In this case, you would need to manually remove the incorrect value from the list in the dependent drop-down box after selecting the value that was changed in the first drop-down box.

But, there is a way to automatically clear the list in the dependent drop-down box after selecting the changed item in the first drop-down menu.

Here is how to do this using VBA code.

Step One

Right-click the tab that has the dependent drop-down menu you want to clear automatically.

Then, choose View Code in the context menu.

Step Two

Now, when the opening window for Microsoft Visual Basic for Applications appears, copy the following code into it.

Private Sub Worksheet_Change(ByVal Target As Range)

‘Update by Extendoffice 2018/06/04

    Application.EnableEvents = False

    If Target.Column = 1 And Target.Validation.Type = 3 Then

        Target.Offset(0, 1).Value = ""

    End If

    Application.EnableEvents = True

End Sub

The column number above is 1 because my drop-down list is in column A.

Step Three

Now, Press Alt + Q. This will close the Microsoft Visual Basic for Applications window.

After doing this, when you change the first drop-down list, the dependent drop-down list should automatically be cleared.

How To Highlight the Mismatched Cells in a Dependent Drop-down List

If you prefer not to use VBA, you could instead use conditional formatting to highlight the cells that have a mismatch.

Then, you can correct the cells with the mismatch.

Here is how to highlight the mismatches in your dependent drop-down menu.

Step One

Select the cell containing your dependent drop-down menu.

Step Two

Navigate to “Home,” then choose “Conditional Formatting” and select “New Rule.”

In the dialog box for New Formatting Rule, choose “Use a formula to determine which cells to format” and enter this formula in the formula field:

=ISERROR(VLOOKUP(B2,INDEX($E$2:$G$4,,MATCH(A2,$E$1:$F$1:$G$1)),1,0)).

Step Three

Set the format, and click okay.

The formula above makes use of the VLOOKUP function to see if an item in your dependent drop-down menu is an item from the primary category.

If it is found not to be from the primary category, the formula returns an error.

Then, the ISERROR function will use this information to return TRUE, thus informing the conditional formatting to highlight the appropriate cell.

Conclusion

Creating a drop-down box is not difficult and can be very useful in data entry forms.

We have shown you how to create these boxes in this article, as well as how to clear the contents of the dependent drop-down box if you have changed an item in the parent drop-down box.

We have also shown you how to use conditional formatting to highlight mismatched cells if you prefer not to use VBA.

This will allow you to correct the mismatched cells.

In this article, you learned to make your own dependent drop-down boxes and correct them as needed to make your data entry forms more useful.