Dependent Drop Down Lists in Excel: How to Create Them Work For You!

If you spend lots of time working on Excel spreadsheets, then you know how difficult it can be to enter information into these documents. The more data you have, the slower you tend to work and the more prone to making mistakes you are. Fortunately, there’s a straightforward solution: dependent drop down lists in Excel! Here’s how they work, how to make them work for you, and why they’re so helpful.

What is a dependent drop down list?

A dependent drop down list is a list of items that change based on the user’s selection on another drop down list. A dependent drop-down list will have a second drop-down box with a list of options that correspond with the first one. The user can select an option from the first box and then select an item from the second box.

How to create a drop down list in Excel?

Please follow the below steps to create a drop down list using Data validation

You create a dropdown on cell C2 for the list of regions in column E using Data Validation

1. Select the cell C2, you want to place your drop down list

2. Go to Data menu
3. Select Data Validation
4. Select Data Validation, to open Data Validation dialog box

5. Select List from Allow Dropdown
6. Type the formula =$E$2:$E$5 in the Source input box. Your regions are listed in this range of cells
7. Press OK

You can see the Region drop down in cell C2 and check. Now you have to create one helper table for the dependent drop down

Create a helper table for dependent drop down

You have a country list for the respective region in cells I1:L6, and you have to list the countries in column G for the selected region in cell C2.

1. Go to cell G1
2. Type the formula =C2, which refer cell C2 first dropdown selection
3. Go to cell G2
4. Type the formula =HLOOKUP($G$1,$I$1:$L$6,row(),0) – here row() return an integer value row number.
5. Drag and fill the formula till cell G6

How to create a dependent drop down list in Excel?

Please follow the below steps to create a drop down list using Data validation

You create a dropdown on cell C3 for the list of countries in column G using Data Validation

1. Select the cell C3, you want to place your drop down list
2. Go to the Data menu
3. Select Data Validation
4. Select Data Validation, to open the Data Validation dialog box

5. Select List from Allow Dropdown
6. Type the below formula =$G$2:$G$6 in the Source input box. Your countries are listed in this range of cells
7. Press OK

Final Output

The drop down in the cell C3 list is based on the selected region in cell C2

Conclusion

When you are working with a lot of data, it is great if you can present the information in a way that makes it as easy as possible for people to find what they are looking for. The drop down list is a great tool for making this happen. The steps that you need to take will depend on what type of data you are working with and the specific needs of your project. However, by following these general guidelines, you can create dependent drop down lists in your spreadsheet program and make them work for your needs.