Microsoft Excel multiple Dependent Drop-Down Lists

To create multiple dependent drop-down lists in Excel, we can use the data validation feature along with named ranges.

Step 1: Set up your data

Organize your data in a worksheet. Each column should represent a category, and the subsequent columns should contain the options for each category.

For example, let's say we have a list of countries in column A, and in column B, you have a list of cities corresponding to each country.

Step 2: Define named ranges

Select the range containing your countries (excluding the header) and click on the Name Box (the box beside the formula bar). Enter a name for the range, e.g., "Countries", and press Enter. Repeat this process for the cities range, naming it "Cities".

Step 3: Create the first drop-down list

Select the cell where you want the first drop-down list to appear. Go to the "Data" tab in the Excel ribbon and click on "Data Validation". In the "Data Validation" dialog box, choose "List" from the "Allow" dropdown.

In the "Source" field, enter the formula "=Countries". This formula refers to the named range you defined earlier. Click "OK" to create the drop-down list.

Step 4: Create the second drop-down list (dependent on the first list)

Select the cell where you want the second drop-down list to appear. Repeat the data validation process as in Step 3, but this time, enter the formula "=INDIRECT($A$1)" in the "Source" field.

The "$A$1" refers to the cell containing the first drop-down list. This formula uses the selected country in the first drop-down to determine the corresponding cities in the second drop-down list.

Step 5: Test the dependent drop-down lists

Now, when you select a country from the first drop-down list, the second drop-down list will update to show the cities corresponding to that country.

Repeat Steps 3 and 4 for any additional dependent drop-down lists you want to create.