How to Make a Dynamic Dependent Drop-Down List in Excel

Dynamic Drop Down List in Excel 365: Quick Steps

• Prepare Your Data
• Convert the List into an Excel Table
• Create the Drop-Down List Using Data Validation
• Test the Dynamic Drop-Down List

Creating dynamic dependent drop-down lists in Excel is a powerful way to improve data entry accuracy and user experience, especially when dealing with large datasets. A dependent drop-down list allows users to select values in one list based on the selection made in another list. In this guide, we’ll explore different methods to make a dynamic dependent drop-down list in Excel, including using Excel tables for structured data, and utilizing the OFFSET or UNIQUE formulas for more flexible options. Additionally, we’ll show you how to create custom input and error messages in Excel, ensuring that your drop-down lists are both intuitive and error-free.

Method 1: Using Excel Tables

A dependent drop-down list in Excel allows you to create a second drop-down list that dynamically changes based on the selection made in the first drop-down. This is especially useful for organizing data like categories and subcategories, regions and cities, or product types and items. Here’s a step-by-step guide to create a dependent drop-down list in Excel:

Step 1: Open Excel and Prepare your Data

Arrange your data in a table format where the first column contains the primary categories, and subsequent columns contain the dependent items.

Step 2: Create Named Ranges

• Select the items under each category (e.g., all items under “Fruits”).
• Go to the Formulas tab and click on Define Name.
• Assign a name to each range corresponding to the category (e.g., name the range of fruits as “Fruits”).

Step 3: Create First Drop- Down List

• Select the cell where you want the primary drop-down list to appear. (Here we have selected C8)
• Go to the Data tab and click on Data Validation.

In the Data Validation dialog box:

• Under the Settings tab, choose List in the "Allow" drop-down.
• In the Source box, enter the names of the primary categories (e.g., Fruits, Vegetables, Beverages).

Click OK to create the first drop-down list.

Step 4: Create the Dependent Drop-Down List

• Select the cell where you want the dependent drop-down list to appear. (Here we have selected B8)
• Go to the Data tab and click on Data Validation.

In the Data Validation dialog box:

• Under the Settings tab, choose List in the "Allow" drop-down.
• In the Source box, type the formula: =INDIRECT(C8)
• (Replace C8 with the cell reference containing the primary drop-down list).

Click OK to create the dependent drop-down list.

Step 5: Test Your Drop-Down Lists

Select an option from the first drop-down list (e.g., "Fruits").

Click the dependent drop-down list. You should see only the items related to the selected category (e.g., Apple, Banana, Orange).

Method 2: Using OFFSET or UNIQUE Formulas

The above method of utilizing an equation to make a drop-down rundown can be stretched out to make a powerful drop-down list too. On the off chance that you utilize the OFFSET capability, as displayed above, regardless of whether you add more things to the rundown, the drop-down won't refresh consequently. You should physically refresh it each time you change the rundown. Here is a method for making it dynamic.

Step 1: Select the Cell

Select a cell where you want to create the drop-down list (cell C2 in this example).

Step 2: Open Data Validation

Go to Data –> Data Tools –> Data Validation.

Step 3: Choose List and Enter the Formula

In the Data Validation popup, within the Settings tab, select List as the Validation criteria. As soon as you select List, the source field appears. In the source field, enter the accompanying recipe:

=OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,”<>”))

Step 4: Confirm and Apply

Ensure that the In-cell drop-down choice is checked. Click OK.

How the Formula Works:

• COUNTIF($A$2:$A$100,"<>") counts non-empty cells in A2:A100.
• OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,"<>")) adjusts the range dynamically to include only non-blank values.

Now, your drop-down list will automatically update as new data is added.

How to Create Custom Input & Error Messages In Excel

The Data Validation tool in Excel allows you to not only create drop-down lists but also improve user experience with input messages and custom error alerts. To learn more about it click here.

Conclusion

Now that you know how to make a dynamic dependent drop-down list in Excel, you can apply these methods to create more interactive and efficient spreadsheets. Whether you choose to use Excel tables, the OFFSET or UNIQUE formulas, or custom input and error messages, each method offers unique benefits for organizing and managing your data. By applying these techniques, you can enhance user interaction, minimize errors, and optimize your Excel workbooks for better data management.