Create Dependent Drop Down Lists In Excel Easily

Drop-down lists in Excel are a great way to make data entry easier and more accurate. But what if you want to create dependent drop-down lists, where the options in one list depend on the selection made in another list? Fortunately, this is easier to achieve than you might think.

In this article, we'll show you how to create dependent drop-down lists in Excel using data validation and named ranges. We'll also cover some advanced techniques for creating more complex dependent lists.

What are Dependent Drop-Down Lists?

A dependent drop-down list is a list where the options available in one list depend on the selection made in another list. For example, if you have a list of countries and a list of cities, you might want to create a dependent list where the cities available depend on the country selected.

Why Use Dependent Drop-Down Lists?

Dependent drop-down lists are useful when you want to make data entry easier and more accurate. By limiting the options available in one list based on the selection made in another list, you can reduce errors and make it easier for users to select the correct option.

How to Create Dependent Drop-Down Lists in Excel

To create dependent drop-down lists in Excel, you'll need to use data validation and named ranges. Here's a step-by-step guide to creating a simple dependent drop-down list:

Step 1: Create a List of Options

First, create a list of options for each drop-down list. For example, if you want to create a list of countries and a list of cities, you might create two lists like this:

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

Step 2: Create Named Ranges

Next, create named ranges for each list of options. To do this, select the list of options and go to Formulas > Define Name. Give each range a name, such as "Countries" and "Cities".

Step 3: Create a Drop-Down List

Now, create a drop-down list using data validation. To do this, select the cell where you want to create the list and go to Data > Data Validation. Select "List" from the Allow dropdown menu and enter the named range you created in Step 2.

Step 4: Create a Dependent Drop-Down List

To create a dependent drop-down list, you'll need to use the INDIRECT function to reference the named range based on the selection made in the first list. For example, if you want to create a list of cities that depends on the country selected, you might use the following formula:

=INDIRECT("Cities_" & A2)

Assuming the country selected is in cell A2, this formula will return the range of cities for that country.

Step 5: Create a Drop-Down List for the Dependent List

Finally, create a drop-down list for the dependent list using the same technique as in Step 3. However, this time, use the formula you created in Step 4 as the source for the list.

Advanced Techniques for Creating Dependent Drop-Down Lists

While the technique described above is sufficient for simple dependent drop-down lists, you may need to use more advanced techniques for more complex lists. Here are a few examples:

Using Multiple Criteria

If you need to create a dependent list that depends on multiple criteria, you can use the IF function to create a formula that returns the correct range based on the selections made.

For example, if you want to create a list of cities that depends on both the country and region selected, you might use the following formula:

=IF(A2="USA", IF(B2="North", Cities_USA_North, Cities_USA_South), IF(A2="Canada", Cities_Canada, ""))

Using VLOOKUP

Another technique for creating dependent drop-down lists is to use the VLOOKUP function to return the correct range based on the selection made.

For example, if you want to create a list of cities that depends on the country selected, you might use the following formula:

=VLOOKUP(A2, Countries, 2, FALSE)

This formula will return the range of cities for the country selected in cell A2.

Gallery of Dependent Drop Down Lists Examples

Dependent Drop Down Lists Examples

Conclusion

Creating dependent drop-down lists in Excel can be a powerful way to make data entry easier and more accurate. By using data validation and named ranges, you can create lists that depend on the selection made in another list. With a little creativity, you can create complex dependent lists that depend on multiple criteria or use advanced functions like VLOOKUP. We hope this article has been helpful in explaining how to create dependent drop-down lists in Excel. Do you have any questions or need further assistance? Please leave a comment below.