Excel dependent dropdown list | dependent validation list

This Excel tutorial explains how to create dependent dropdown list where dropdown list values depend on another list value.

Excel dependent dropdown list / dependent validation list

Suppose you have a continent list in Excel (Asia, North America, South America, etc). When an user selects a continent from a dropdown list, the user can then select the second dropdown list which contains all the countries of your selected continent, so the country list is dependent on the continent value.

For example, if you Europe Country in A2, you can only select a list of Europe countries in B2.

 

If you select Asia Country in A2, you can only select Asia countries in B2.

Setup Excel dependent drop down list

Step 1: Create a new worksheet and write down all the items your want to display in the validation lists

 

Step 2: Name the data range of the first level data validation list (continent). Name it Continent_List for easy reference. We add underline in the Range Name because the Range Name does not allow space. To name the range, highlight A2:A4, and then type the name directly on the top left. Click here to learn how to create dynamic validation list.

 

Next, name the data range of column B, C and D. The Range Name should be exactly same as the corresponding values in column A, replace any space with underline.

 

Step 3: Create dropdown list for Continent

Setup a template in a new worksheet as below

 

Select range A2:A8, then navigate to Data tab > Data Validation

 

Enter information as below. The Source is the Name Range that we defined for continent.

 

Step 4: Create dependent dropdown list

Select range B2:B8, then navigate to Data tab > Data Validation

 

Setup the values as below.

To explain the formula in Source:

  • Substitute function is to replace space with underline. For example, if A2 value is Asia Country, Substitute converts the text into Asia_Country, it is because Name Range does not allow space
  • INDIRECT function is to convert the value in A2 into a Range Name. For example, if A2 value is Asia_Country, Indirect function converts this text into a Range, which contains values China, Hong Kong, Macau

Now the dependent dropdown list is ready for use.

Clear dependent validation list value if first level validation list is reselected

What happens if we select Spain in Country dropdown list, then we select Asia Country in Continent? Excel allows you to do that, but this is not right as Spain is a Europe Country.

 

To prevent this error, we need to clear the Country whenever Continent is reselected with the help of Worksheet_Change Event.

Press ALT+F11 > select Sheet1 > copy and paste the below code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A8")) Is Nothing Then
        Target.Offset(0, 1).ClearContents
    End If
End Sub

Now whenever you select a Continent, Country becomes blank.

Leave a Reply

Your email address will not be published.