5 Ways To Create Excel Dependent Drop Downs

Excel dependent drop-downs are a powerful tool for creating interactive and dynamic spreadsheets. By allowing users to select options from a list, you can simplify data entry, reduce errors, and improve the overall user experience. In this article, we will explore five ways to create Excel dependent drop-downs, including using data validation, formulas, and VBA macros.

What are Dependent Drop-Downs?

Dependent drop-downs are a type of drop-down list that changes its options based on the selection made in another drop-down list. For example, if you have a list of countries and a list of cities, you can create a dependent drop-down that shows only the cities in the selected country.

Method 1: Using Data Validation

One way to create dependent drop-downs is by using data validation. Here's how:

• Select the cell where you want to create the dependent drop-down.
• Go to the Data tab and click on Data Validation.
• Select List from the Allow drop-down menu.
• In the Source field, enter the range of cells that contains the options for the first drop-down list.
• Click OK.
• Create a second drop-down list in another cell using the same method.
• In the Source field, enter the range of cells that contains the options for the second drop-down list, but make sure to include the cell reference to the first drop-down list.
• Click OK.

For example, if you have a list of countries in cells A1:A5 and a list of cities in cells B1:B10, you can create a dependent drop-down that shows only the cities in the selected country by using the following formula in the Source field:

=OFFSET(B1,0,0,COUNTIF(A1:A5,A2),1)

This formula uses the OFFSET function to return a range of cells that starts at cell B1 and has a height equal to the number of cities in the selected country.

Method 1 Example

Here's an example of how to create dependent drop-downs using data validation:

Country	City
USA	New York
USA	Los Angeles
Canada	Toronto
Canada	Vancouver
Mexico	Mexico City

In this example, the user can select a country from the first drop-down list, and the second drop-down list will show only the cities in the selected country.

Method 2: Using Formulas

Another way to create dependent drop-downs is by using formulas. Here's how:

• Create a list of options for the first drop-down list in a range of cells.
• Create a list of options for the second drop-down list in another range of cells.
• Use the IF function to create a formula that returns the options for the second drop-down list based on the selection made in the first drop-down list.
• Use the INDEX and MATCH functions to return the selected option from the second drop-down list.

For example, if you have a list of countries in cells A1:A5 and a list of cities in cells B1:B10, you can create a dependent drop-down that shows only the cities in the selected country by using the following formula:

=IF(A2="USA",OFFSET(B1,0,0,COUNTIF(A1:A5,"USA"),1),IF(A2="Canada",OFFSET(B1,0,0,COUNTIF(A1:A5,"Canada"),1),OFFSET(B1,0,0,COUNTIF(A1:A5,"Mexico"),1)))

This formula uses the IF function to return the options for the second drop-down list based on the selection made in the first drop-down list.

Method 2 Example

Here's an example of how to create dependent drop-downs using formulas:

Country	City
USA	New York
USA	Los Angeles
Canada	Toronto
Canada	Vancouver
Mexico	Mexico City

In this example, the user can select a country from the first drop-down list, and the second drop-down list will show only the cities in the selected country.

Method 3: Using VBA Macros

Another way to create dependent drop-downs is by using VBA macros. Here's how:

• Create a list of options for the first drop-down list in a range of cells.
• Create a list of options for the second drop-down list in another range of cells.
• Open the Visual Basic Editor by pressing Alt + F11 or by navigating to Developer > Visual Basic.
• Insert a new module by clicking Insert > Module.
• Write a VBA macro that uses the Worksheet_Change event to update the options in the second drop-down list based on the selection made in the first drop-down list.

For example, if you have a list of countries in cells A1:A5 and a list of cities in cells B1:B10, you can create a dependent drop-down that shows only the cities in the selected country by using the following VBA macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then
        Select Case Target.Value
            Case "USA"
                Range("B2:B10").Value = Range("B1:B5").Value
            Case "Canada"
                Range("B2:B10").Value = Range("B6:B10").Value
            Case "Mexico"
                Range("B2:B10").Value = Range("B11:B15").Value
        End Select
    End If
End Sub

This macro uses the Worksheet_Change event to update the options in the second drop-down list based on the selection made in the first drop-down list.

Method 3 Example

Here's an example of how to create dependent drop-downs using VBA macros:

Country	City
USA	New York
USA	Los Angeles
Canada	Toronto
Canada	Vancouver
Mexico	Mexico City

In this example, the user can select a country from the first drop-down list, and the second drop-down list will show only the cities in the selected country.

Method 4: Using Power Query

Another way to create dependent drop-downs is by using Power Query. Here's how:

• Create a list of options for the first drop-down list in a range of cells.
• Create a list of options for the second drop-down list in another range of cells.
• Go to the Data tab and click on New Query.
• Select From Other Sources > Blank Query.
• Use the Query Editor to create a query that returns the options for the second drop-down list based on the selection made in the first drop-down list.
• Use the Load To function to load the query into a table.

For example, if you have a list of countries in cells A1:A5 and a list of cities in cells B1:B10, you can create a dependent drop-down that shows only the cities in the selected country by using the following Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Selection = Source{0}[Country],
    Cities = Source{0}[City],
    FilteredCities = Table.SelectRows(Cities, each [Country] = Selection)
in
    FilteredCities

This query uses the Table.SelectRows function to return the options for the second drop-down list based on the selection made in the first drop-down list.

Method 4 Example

Here's an example of how to create dependent drop-downs using Power Query:

Country	City
USA	New York
USA	Los Angeles
Canada	Toronto
Canada	Vancouver
Mexico	Mexico City

In this example, the user can select a country from the first drop-down list, and the second drop-down list will show only the cities in the selected country.

Method 5: Using Dynamic Arrays

Another way to create dependent drop-downs is by using dynamic arrays. Here's how:

• Create a list of options for the first drop-down list in a range of cells.
• Create a list of options for the second drop-down list in another range of cells.
• Use the FILTER function to return the options for the second drop-down list based on the selection made in the first drop-down list.
• Use the UNIQUE function to return the unique options for the second drop-down list.

For example, if you have a list of countries in cells A1:A5 and a list of cities in cells B1:B10, you can create a dependent drop-down that shows only the cities in the selected country by using the following formula:

=FILTER(B:B,(A:A=A2))

This formula uses the FILTER function to return the options for the second drop-down list based on the selection made in the first drop-down list.

Method 5 Example

Here's an example of how to create dependent drop-downs using dynamic arrays:

Country	City
USA	New York
USA	Los Angeles
Canada	Toronto
Canada	Vancouver
Mexico	Mexico City

In this example, the user can select a country from the first drop-down list, and the second drop-down list will show only the cities in the selected country.

Excel Dependent Drop-Downs Image Gallery

We hope this article has provided you with a comprehensive guide on how to create Excel dependent drop-downs using different methods. Whether you're a beginner or an advanced user, these methods can help you create interactive and dynamic spreadsheets that simplify data entry and improve the user experience.

We encourage you to try out these methods and experiment with different techniques to create more complex and advanced dependent drop-downs. If you have any questions or need further assistance, please don't hesitate to ask.

Don't forget to share this article with your friends and colleagues who may benefit from learning how to create Excel dependent drop-downs. Happy spreadsheeting!