How to create Dependent Drop down List in Excel?

How to create Dependent Drop down List in Excel?
Last Updated on 20/04/2024
Reading time: 3 minutes

How to create Dependent Drop down List in Excel? You need to use 2 Excel functions

  1. The UNIQUE function

    This function will be used for the first drop down list

  2. The FILTER function

    This function will be used for the dependent drop down list

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

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

Presentation of a depending drop down list

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)

UNIQUE function to extract unique category
  1. We can also 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. 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.

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

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 and 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 Dependent Drop down List in Excel?

Reading time: 3 minutes
Last Updated on 20/04/2024

How to create Dependent Drop down List in Excel? You need to use 2 Excel functions

  1. The UNIQUE function

    This function will be used for the first drop down list

  2. The FILTER function

    This function will be used for the dependent drop down list

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

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

Presentation of a depending drop down list

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)

UNIQUE function to extract unique category
  1. We can also 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. 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.

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

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 and 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 *