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.

Our objective is to generate a drop-down list for the Sub-Category based on the chosen Category.

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 Column A with the UNIQUE function.

=UNIQUE(A2:A56)

2. We can also use the SORT function to order the list of categories.

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

4. Choose the List option
5. 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.

Create the dependent drop down list in Excel

Now we will 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.
• 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 and the # symbol.

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

Related Articles

• Excel Tutorial – Creating a dropdown menu
• Update a Dropdown List automatically
• Excel Dropdown list – Exclude Previously Selected Items