Create Dependent Drop-down Lists

The dependent drop-down lists in Excel is designed to create conditional drop-down lists. Specifically, selecting an item from a drop-down list will change the available items in another drop-down list. Let’s create one by following the steps below.

1. In Sheet1 create four named ranges. (a) state [B4:B6], (b) texas [C4:C7], (c) nevada [D4:D6], and (d) florida [E4:E7].

2. In Sheet2, select cell C2.

3. From the Data tab, click “Data Validation”.

4. From the Settings tab of the Data Validation dialog box, select “List” in Allow box. In the source box, type =state.

5. Click OK and you get the drop-down list shown below.

6. Now, select cell F2.

7. From the Settings tab of the Data Validation dialog box, select “List” in Allow box. In the source box, type =INDIRECT($C$2).

8. Click OK. You find the result below.

Note: The INDIRECT function shows the list in the second drop-down list based on the item selected in the first drop-down list.

If you like to download the Excel file containing the example above, click HERE.

6 of 6 finished! Congrats!! You can now move on to Next Chapter 09: Next Example >>

<< Previous Example | Skip to Next Chapter 09: Using Templates