Create Dependent Drop Down Lists in Excel

Contents

Here’s how to create dependent dropdown lists (also called cascading dropdown lists) in Excel:

1. Prepare your data:

Organize your data in a table format.
The first column should contain the main categories for the first dropdown list.
Subsequent columns should contain sub-categories relevant to each main category.

This step makes the formulas cleaner and easier to manage.
Select the main category list (first column).
Go to the Data tab and click Define Name.
In the “Create from selection” window, choose “Top row” and click OK. This creates a named range for your main categories.

Select the cell where you want the first dropdown list to appear.
Go to the Data tab and click Data Validation.
In the “Settings” tab, choose “List” under “Allow”.
In the “Source” field, enter the range containing your main categories (or the named range you created in step 2).
Click OK.

Select the cell where you want the dependent dropdown list to appear.
Go to the Data tab and click Data Validation.
In the “Settings” tab, choose “List” under “Allow”.
In the “Source” field, enter the formula: =INDIRECT(D2) (Replace D2 with the cell reference containing the selection from the first dropdown list).
- The INDIRECT function uses the value selected in the first dropdown list to dynamically determine the source for the second list.

INDIRECT takes a string argument and treats it as a cell reference.
The cell reference in our case (D2) refers to the selection from the first dropdown list.
By using INDIRECT, the formula effectively looks up the corresponding sub-category list based on the chosen main category.

You can follow steps 4 and modify the formula further to create additional dependent dropdown lists based on previous selections.