How To Create A Dynamic Drop Down List In Excel

You are viewing: How to create a dynamic drop down list in excel At HTTLEN: Share Good Articles

The tutorial shows how to create an Excel dropdown list depending on another cell by using the new dynamic array features.

Creating a simple dropdown list in Excel is easy. Making a multi-level cascading dropdown has always been a challenge. The tutorial linked above outlines four different approaches, each including a large number of steps, a bunch of different formulas, and a handful of limitations related to multi-word input, blank cells, etc.

That was the bad news. The good news is that those methods were designed for pre-dynamic versions of Excel. The introduction of dynamic arrays in Excel 365 has changed everything! With the new dynamic array features, creating a multiple dependent dropdown list is a matter of minutes, if not seconds. No gimmicks, no warnings, no bullshit. Just quick, straightforward and easy to follow solutions.

Make a Dynamic Dropdown List in Excel
Create a Multiple Dependent Dropdown List
Create a Menu expandable dropdown that excludes blank cells
Sort dropdown list alphabetically

How to make a dynamic dropdown list in Excel

This example demonstrates the general approach to creating a cascading drop down list in Excel using the new dynamic array features.

Suppose you have a list of fruits in column A and exporters in column B. An additional complication is that the fruit the names are not grouped but scattered in the column. The goal is to put the unique fruit names in the first dropdown and, based on user selection, show the relevant exporters in the second dropdown.

To create a dynamic dependent dropdown in Excel, do these steps:

1. Get Items for Main Dropdown List

To begin with, we’ll pull all the different fruit names from column A. This can be done using the SINGLE function in its simplest form: provide the list of fruits for the first argument (array) and skip the remaining optional arguments as their defaults work fine for us:

=UNIQUE(A3:A15)

The formula goes to G3, and then After pressing the Enter key button, the results are automatically spilled into the following cells.

2. Create the Primary Dropdown

To make your primary dropdown, set up an Excel Data Validation rule like this:

Select a cell in which you want the drop-down menu (D3 in our case).
On the Data tab, in the Data Tools group, click Data Validation.
Under In the Data Validation dialog box, do the following:
- Under Allow, select List.
- In the Source box, enter the reference to the output of the range of dispersion through the UNIQUE formula. To do this, write the hash tag right after the cell reference, like so: =$G$3#
  This is called a spill range reference, and this syntax refers to the entire range, regardless of how much it is expanded or contract.
- Click OK to close the dialog.

Your main dropdown is ready!

3. Getting Items for the Child Dropdown

To get inputs for the child dropdown, we’ll filter the values in column B based on the value selected in the first dropdown. This can be done with the help of another dynamic array function called FILTER:

=FILTER(B3:B15, A3:A15=D3)

Where B3:B15 is the data source data for your child dropdown, A3:A15 is the source data for your parent dropdown, and D3 is the parent dropdown cell.

To make sure the formula works correctly, you can select some value in the first dropdown menu -downlist and look at the results returned by FILTER. Perfect! 🙂

4. Make the Dropdown Bound

To create the second dropdown, set up the data validation criteria exactly as you did for the first dropdown in step 2. But this time, reference the spillover range returned by the FILTER function: =$H$3#

That’s it! Your Excel dependent dropdown is ready to use.

How to create a multiple dependent dropdown list in Excel

In the example above, we made a dependent dropdown list in another cell But what if you need a multi-level hierarchy, i.e. a third dropdown that depends on the second list, or even a fourth dropdown that depends on the third list, is that possible? any number of dependent lists (a reasonable number, of course :).

For this example, we’ve placed states/provinces in column C, and now we’re looking to add a corresponding dropdown in G3:

To create a multiple dependent dropdown list in Excel, here’s what to do:

1.Set up the first dropdown menu. egable

The parent dropdown list is created with the same steps as in the previous example (see steps 1 and 2 above). the only difference is the spill range reference that you enter in the Source box.

This time, the SINGLE formula is in E8, and the main dropdown list will be in E3. So select E3, click on Data Validation and provide this reference: =$E$8#

2. Set up the second dropdown

As you may have noticed, column B now contains multiple occurrences of the same exporters. But you only want unique names in your dropdown list, right? To skip all duplicate occurrences, wrap the UNIQUE function around your FILTER formula and enter this updated formula in F8:

=UNIQUE(FILTER(B3:B15, A3:A15=E3))

Where B3:B15 is the source data for the second dropdown, A3:A15 is the source data for the first dropdown, and E3 is the first dropdown cell.

After that , use the following dropdown range reference for data validation criteria: =$F$8#

3. Configure the Third Dropdown

To collect the items in the third dropdown, use the FILTER formula with multiple criteria. The first criterion compares the entire list of fruits with the value selected in the first dropdown (A3:A15=E3), while the second criterion compares the list of exporters with the selection in the second dropdown (B3:B15=F3). . The entire formula goes to G8:

=FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3))

If you are adding dropdowns more dependents (4th, 5th, etc.), column C will most likely contain multiple occurrences of the same element. To prevent duplicates from entering the staging table, and consequently the third dropdown, nest the FILTER formula in the UNIQUE function as we did in the previous step:

=UNIQUE(FILTER(C3 :C15, ( A3:A15=E3) * (B3:B15=F3)))

The last thing you need to do is create one more data validation rule with this source reference: =$G $8#

Your multiple dependent drop dropdown is ready! >

How to Make an Expandable Dropdown List in Excel

After creating a dropdown, your first concern may be what happens when you add new items to the source data. Will the dropdown list update automatically? If your original data is formatted as an Excel table, then yes, a dynamic dropdown list discussed in the examples above will automatically expand without any effort on your part because Excel tables are expandable by nature.

If for some reason using an Excel table is not an option, you can make your dropdown list expand like this:

To include new data automatically as they are added to the source list, add a few extra cells to the arrays referenced in your formulas.
To exclude blank cells, set the formulas to ignore empty cells until they are filled.

Keeping these two points in mind, let’s adjust the formulas in our data preparation table. The data validation rules do not require any tuning.

Formula for the main dropdown

With the names of the fruits in A3:A15, we add 5 extra cells to the matrix to attend for possible new entries. In addition, we added the FILTER function to UNIQUE to extract unique values without whitespace.

Given the above, the formula in G3 takes this form:

=UNIQUE(FILTER(A3:A20, A3:A20″”))

Formula for bound dropdown

The formula in G3 doesn’t need much tuning, just extend the arrays with a few more cells:

=FILTER(B3:B20, A3:A20 = D3)

The result is a fully dynamic expandable bound dropdown list:

How to sort dropdown list alphabetically

Want to arrange your dropdown list alphabetically without resorting to the source data? The new Dynamic Excel has a special function for this too! In your data staging table, simply wrap the SORT function around your existing formulas.

The data validation rules are set up exactly as described in the examples above.

To sort A to Z

Since ascending sort order is the default, you can simply nest your existing formulas in the array argument of SORT, ignoring all other arguments that are optional.

For the parent dropdown (the formula in G3):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20″ “)))

For the dependent dropdown(the formula in H3):

=SORT(FILTER(B3:B20, A3:A20=D3 ))

Done! Both dropdown lists are sorted alphabetically from A to Z.

To sort from Z to A

To sort in descending order, you must set the third argument (sort_order) of the SORT function to -1.

For the parent dropdown(the formula in G3):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20″” )) , 1, -1)

For the dependent dropdown(the formula in H3):

=SORT(FILTER(B3:B20, A3 :A20 =D3), 1, -1)

This will sort both the data in the staging table and the items in the dropdown lists from Z to A:

This is how you create a dynamic dropdown list in Excel with the help of the new dynamic matrix functions AC. Unlike traditional methods, this approach works perfectly for single and multi-word inputs and takes care of blank cells. Thanks for reading and we hope to see you on our blog next week!

Downloadable Practice Workbook

Excel Dependent Dropdown List (.xlsx file)

Everything else

How to create a dynamic drop down list in excel