# Excel Dependent Drop Down List (Create with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Up till now, we have learned to create drop-down lists in Excel. Today I will be showing how you to create a dependent drop down list in Excel.

## Steps to Create a Dependent Drop Down List in Excel

Here we’ve got a data set with the Names, Authors, and Book Types of some books of a bookshop called Martin Bookstore.

Our objective today is to create a dependent drop-down list.

• First, we will create a drop-down list of the Book Types (First Drop Down List)
• Then we will create a drop-down list of the Authors based on the Book Types (Middle Drop Down List)
• Finally, we will create a drop-down list of the Book Names based on the Authors (Last Drop Down List)

### Step 1: Create the First Drop Down List Using UNIQUE Function

• To create the first drop-down list, select any cell in your workbook and enter this formula:
`=UNIQUE(D5:D30,FALSE,FALSE)`

Here, D5:D30 is the range of my array (Book Type). You use your one.

• Then select the cell where you want to enter the drop-down list and go to Data > Data Validation > Data Validation in your Excel Toolbar.

• Click on Data Validation. You will get the Data Validation dialogue box.
• From the Allow option, select List.
• Then in the Source box, enter the cell reference of the formula where you entered the previous formula, along with a HashTag (#) at the end.

In this example, it is \$J\$5#

• Click on OK. You will get a drop-down list generated handsomely with the Book Types in your selected cell.

Note:

`=UNIQUE(OFFSET(E5,0,0,COUNTA(E:E)-1,1),FALSE,FALSE)`
• Don’t worry. Just replace the column array D5:D30 with an OFFSET function of this form:

=OFFSET(start_cell,0,0,COUNTA(column)-1,1)

### Step 2: Create the Middle Drop Down List (First Dependent List) with UNIQUE-FILTER Combination

Now we will create a drop-down list of the Authors based on the Book Type selected from the first list.

• Select any cell in your worksheet and enter this formula:
`=UNIQUE(FILTER(C5:C30,E5:E30=G5),FALSE,FALSE)`

• Here C5:C30 is the range of this drop-down list (Authors), and D5:D30 is the range of the previous drop-down list (Book Types). You use your one.
• And F5 is the cell reference of the first drop-down list. You use your one.
• Then select the cell where you want to insert the drop-down list and go to Data > Data Validation > Data Validation in the Excel toolbar.

• Click on Data Validation. You will get the Data Validation dialogue box.
• From the Allow option, select List.
• In the Source box, enter the reference of the cell where you inserted the previous formula, with a HashTag (#) at the end.

In this example, it is \$L\$5#.

• Click on OK. You will find your drop-down list generated in your selected cell.

• Now if you have more drop-down lists to be generated before going to the last drop-down list, follow the same procedure.
• For example, let’s have one more column Country after the column Author, containing the countries of the authors, like this.

• First, we will generate the first drop-down list (Book Type) in the mentioned way.
• Then we will generate the second drop-down list (Author) based on the first drop-down list using this formula:
`=UNIQUE(FILTER(C5:C30,(D5:D30=I5)*(E5:E30=G5)))`

• Then we will generate the third drop-down list (Country) based on the first and second drop-down lists using this formula:
`=UNIQUE(FILTER(D5:D30,E5:E30=G5))`

• If you have one more drop-down list to create in the middle, create it based on the first, second, and third drop-down lists. And so on.

Note:

• To generate a dynamic drop-down list, use this formula instead:
`=UNIQUE(FILTER(OFFSET(C5,0,0,COUNTA(C:C)-1,1),OFFSET(D5,0,0,COUNTA(D:D)-1,1)=F5),FALSE,FALSE)`
• Replaced all the column arrays, C5:C30 and D5:D30 with an OFFSET function of this form:
=OFFSET(start_cell,0,0,COUNTA(column)-1,1)

Read more: How to Make a Drop Down List in Excel

### Step 3: Create Another Dependent Drop Down List

To create the last drop-down list, you have to use all the previous lists. But you need not use the UNIQUE function this time.

In our original data set, the last drop-down list will be of the Names of the books. It will depend on the first and second drop-down lists.

• Select any cell in your worksheet and enter this formula:
`=FILTER(B5:B30,(C5:C30=G5)*(D5:D30=F5))`

• Here B5:B30 (Book Name) is the range of my last drop-down list, C5:C30 (Author) is the range of my second drop-down list.
• And D5:D30 (Book Type) is the range of my first drop-down list. You use your one.
• G5 is the cell reference of the second drop-down list, and F5 is the cell reference of the first drop-down list. You use your one.
• Then select the cell where you want to enter the drop-down list and go to Data>Data Validation>Data Validation in the Excel Toolbar.

• Click on Data Validation. You will get the Data Validation dialogue box.
• From the Allow option, select List.
• In the Source box, enter the cell reference of the previous formula along with a HashTag (#) at the end.

In this example, it is \$N\$5#

• Then click OK. You will get the drop-down list generated in your selected cell.

Note:

• To generate a dynamic drop-down list, use this formula instead:
`=FILTER(OFFSET(B5,0,0,COUNTA(B:B)-2,1),(OFFSET(C5,0,0,COUNTA(C:C)-1,1)=G5)*(OFFSET(D5,0,0,COUNTA(D:D)-1,1)=F5))`
• Replaced all the column arrays, C5:C30, D5:D30, and E5:E30 with an OFFSET function of this form:
=OFFSET(start_cell,0,0,COUNTA(column)-1,1)

## Conclusion

Using these methods, you can generate any drop-down list dependent on another list in Excel. Do you have any questions? Feel free to ask us.