Home » Function » Dynamic Arrays » How to create Depending drop down menu in Excel?

How to create Depending drop down menu in Excel?

Reading time: 3 minutes
Last Updated on 12/05/2023 by Frédéric LE GUEN

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
Depending drop down menu

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.

Hierarchical data

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

Presentation of a depending drop down list

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)

UNIQUE function to extract unique category

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

SORT function to reorder the result
  1. Next, we will create a drop-down list in the menu Data > Data validation
Menu Data Validation
Menu Data Validation
  1. Choose the List option
  2. And write the following reference with the symbol #

=$E$2#

Drop down list with dynamic array references

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,"")

FILTER function to create the dependent drop down list
  • 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,"")))

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

=$F$2#

Reference for the Depending drop down menu

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

Depending drop down menu

2 Comments

  1. Jun
    13/09/2021 @ 12:17

    hi, please advise why unique formula is not working. Thanks.

    Reply

    • Frédéric LE GUEN
      13/09/2021 @ 12:30

      What is your Excel version?

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

How to create Depending drop down menu in Excel?

Reading time: 3 minutes
Last Updated on 12/05/2023 by Frédéric LE GUEN

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
Depending drop down menu

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.

Hierarchical data

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

Presentation of a depending drop down list

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)

UNIQUE function to extract unique category

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

SORT function to reorder the result
  1. Next, we will create a drop-down list in the menu Data > Data validation
Menu Data Validation
Menu Data Validation
  1. Choose the List option
  2. And write the following reference with the symbol #

=$E$2#

Drop down list with dynamic array references

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,"")

FILTER function to create the dependent drop down list
  • 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,"")))

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

=$F$2#

Reference for the Depending drop down menu

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

Depending drop down menu

2 Comments

  1. Jun
    13/09/2021 @ 12:17

    hi, please advise why unique formula is not working. Thanks.

    Reply

    • Frédéric LE GUEN
      13/09/2021 @ 12:30

      What is your Excel version?

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *