How to create dynamic dropdown lists with dynamic arrays in Excel

A dependent dropdown list updates its options based on the choice made in a previous dropdown. For instance, if a user picks a state like Ohio or Michigan in one dropdown, the next dropdown for counties will display only the counties tied to that selected “parent” state.

In earlier Excel versions, creating such dependent dropdowns required clunky workarounds with tools like INDIRECT() or VBA. Today, thanks to dynamic arrays in newer versions, it’s far easier using the spill operator and the UNIQUE() function. That’s what this post is all about.

We’ll work with a product sales dataset to build a series of dependent dropdown lists, enabling users to navigate a hierarchy of item combinations. You can follow along using the exercise file linked here:

Here, we’re dealing with a hierarchy of regions, categories, and product names. The aim is to allow the user to choose a combination where they first select a region, then see only the categories linked to that region in the next dropdown, followed by only the relevant product names in the subsequent dropdown. To start, we’ll extract a list of all unique region names from the dataset, which can be done dynamically and effortlessly using the UNIQUE() function:

For the upcoming sections, it’s key to grasp how dynamic array spill ranges and the spill operator function in Excel. If this is new to you, I recommend checking out this blog post first:

Now that we have a list of unique regions, let’s create a dropdown menu for the user to select a specific region. To do this, we’ll navigate to the classic Data > Data Validation menu and set the validation criteria to a List, just as we’ve done in Excel for years.

The interesting part comes in the Source field. Instead of using a fixed range, we’ll leverage the spill operator # with H10 to reference the dynamic array starting at that cell:

This approach ensures that if new unique values are added or existing ones are removed from the dataset, the list of unique regions will automatically update. And because we’re using the dynamic spill operator, the dropdown’s input range will adjust on its own.

The regions will appear in the dropdown in the same order as they do in the UNIQUE() list. Because we’ve already sorted the entire hierarchy alphabetically from A to Z, the unique regions will remain in alphabetical order after duplicates are removed. Alternatively, you could apply a one-time sort to the data using the SORT() function with dynamic arrays.

Next, we’ll create a list of unique values for the dependent “child” field, Category. To achieve this, we’ll combine the UNIQUE() function with the FILTER() function.

=UNIQUE(FILTER(products[Category], products[Region] = I2))

Essentially, this will extract only the unique category names that correspond to the region the user selects through data validation.

We’ll then configure a data validation list for the child field in a similar manner to the parent field, utilizing a dynamic spill operator:

Now we’ll generate a list of unique product names based on the specific region and category the user has selected so far. This ensures we only retrieve product names that exist within that particular product hierarchy. To accomplish this, we’ll apply and logic in the FILTER() function using the asterisk:

=UNIQUE(FILTER(products[Product Name],
    (products[Region] = I2) *
    (products[Category] = products!I3)))

To learn more about filtering a dataset by one or more criteria using the FILTER() function, check out this post:

Finally, add data validation to the product name cell, linking it once again to the spill operator that reflects the unique set of product names tied to the specific region and category combination.

This setup is pretty neat and can be super helpful for tasks like data entry. It ensures users only enter product names that actually exist within a given region. Beyond that, another handy use case could be a lookup search like below.

Here, the user picks a region, category, and product name, and a dynamic output is generated by filtering the dataset to pinpoint that exact entry (or entries, if there were duplicates, though in this case there aren’t):

=IFERROR(VSTACK(products[#Headers],
FILTER(products,
    (products[Region] = I2) *
    (products[Category] = I3) *
    (products[Product Name] = I4),)), "Please choose a valid combination")

As a final product, we’ve put together a handy little lookup tool using a dynamic array functions, along with some simple data validation and error handling:

What questions do you have about dependent dropdown lists in particular, or any of the data validation or dynamic array features shown in this demo? Let me know in the comments.