This article will explain how to create depending drop down menu in Excel. To do that, we need 2 Excel functions:

• The UNIQUE function for the very first drop down list
• The FILTER function for the depending drop down

Table with hierarchical data

We will start from a table where we have a Category of products and also a Sub-Category.

 The data does not need to be sorted.

We want to create a drop-down list for the Sub-Category depending on the selection of the Category

Step1: Creation of the first drop down menu

1. For the first drop-down list, the first level of the selection, we will extract the unique values ​​of the Column A with the UNIQUE function.

=UNIQUE(A2:A56)

And we can use the SORT function to order the list of Categories.

2. Next, we will create a drop-down list in the menu Data > Data validation

3. Choose the List option
4. And write the following reference with the symbol #

=$E$2#

The symbol # sign means 'All the results return by the Array function'.

With this trick, Excel 'knows' all the results returned by the UNIQUE function. You don't have to update the range of cells yourself.

Step 2: Create the depending drop down menu

Now we're going to build a new formula based on the result of the previous selection. This time, we will use the FILTER function.

• The writing of the FILTER function is:

1. Select the column that contains the data to return
2. Indicate the column for the filter
3. Create the logical test (here =)
4. The value for the filter (here cell I3)

=FILTER(B2:B56,A2:A56=I3,"")

• We only need to keep unique values. So, we will use the UNIQUE function one more time.

=UNIQUE(FILTER(B2:B56,A2:A56=I3,""))

• And to make things perfect, we will also sort the data

=SORT(UNIQUE(FILTER(B2:B56,A2:A56=I3,"")))

• Again, for the depending drop down menu, we must create a Data Validation list with the spilled reference with the symbol #.

=$F$2#

And with this new function, we have created a depending drop-down menu😀👍