Dropdown style inputs are very useful in the sense that their use can expedite the data entry process in most applications. Imagine you want to use this feature to create sequential questions that affect each other, like disabling some options based on the previous selection. A pricing application where you start with higher-level product details, then move onto the specific item is the perfect example for this. One caveat is that Excel lists are typically static and inputs that pull options from a list can’t function dynamically to narrow down its results. Ultimately, using dropdown inputs in Excel like filters won’t work without creating helper columns and reorganizing the lists. You can download our sample workbook here.
You can use the INDIRECT function to update the option list of a dropdown with another list which has a static list if items to create a dependent dropdown. Although this will work in most cases, dealing with lists that contain hundreds of items can prove challenging. The INDIRECT approach is also not viable if the entire data is stored in a single place, such as a database table. Needless to say, if you are working with external sources, database sources will be necessary.
We’ve covered a good example for a database table and multiple dependent dropdowns in Transforming a Complex Pricing List into a Quoting Tool in Excel – Part 1. The pricing application has a table for drink prices that are listed based on type, brand, and size. The columns, except for Description, are tied to the previous columns and selecting an option for each is needed to find the price.
Traditionally, you would need 12 helper columns to manually create unique item lists with the Remove Duplicate feature, possibly across multiple sheets to keep layout clean and tidy. Finally, you would need additional formula work to make the list size dynamic by excluding the empty cells from list range.
Thanks to the new dynamic array features and formulas, you can now do this using 4 formulas. At the time of writing this article, Microsoft announced that this formula is currently only available to a number of select insider users. When it’s ready, the feature is planned for release for Office 365 users.
Excel Table
Begin by converting the data range into an Excel Table. This is not required, but Excel Tables are useful for managing and analyzing data in tabular format. An Excel Table provides the data in a special structure, which includes filtering, formatting, resizing, and auto populating formulas dynamically. Once a range becomes a table, you can use structured references (e.g. Table1[Description]) instead of regular A2 reference. The structured references and auto-sizing ability of tables remove the need to update the formulas when you add or remove data.
To create an Excel Table, select a cell in the table and follow either one of the following paths:
- Home > Format as Table > Select a style
- Insert > Table
Confirm the range and header availability in the Create Table dialog and click OK.
From now on, we can use structured reference Table1[Description] instead of manually selecting a range in column A.
Naming Dropdown Cells
This is an optional step. Naming cells or ranges make it easier to write and read formulas, as you won’t have to memorize cell references. We named the cells we used as in the dropdown cells in the sample file. Below is the list of these fields:
- Description
- Brand
- Size
These names refer to the value selected in the corresponding dropdown. For example, Description named range is equal to "TENNESSEE WHISKEY" in the following screenshot.
Description is the first column in the table. This means it does not have any dependencies. For this type data, all we need is a list of unique items for a dropdown. To take it one step further, you can also sort the values. We’re mainly going to be using the UNIQUE and SORT functions. These functions return an array of sorted unique values from a specified range.
=SORT(UNIQUE(Table1[Description]))
We entered the formula into cell F2. Pressing Enter will populate all values through the column F. This is behavior of dynamic arrays is called spilling.
Now, we need to assign the list to as a dropdown input for the cell Description. This means following the same steps as you would when adding Data Validation list to cell, until the part where you need to enter a reference for the Source field:
- Select the cell Description
- Follow Data > Data Validation
- Select List in Allow field
- Enter =List!F2# into Source field
F2# here refers to a spilled range starting from the cell F2. You can refer a spilled range by placing the spilled range operator (#) after the reference of the cell where the formula ends. These types of references work like a structured reference of an Excel Table, and the size is unimportant. If there the spilled range changes, formulas will continue to execute with the correct reference.
Next step is to generate a dynamic list for the Brand dropdown. Now, we’re going to utilize the FILTER formula. The FILTER function returns an array of values after a filtering process. We need the FILTER function to return only the Brands that are listed under the selected Description. For example, if the Description is "GRAIN ALCOHOL", the Brand list should include only "Everclear Alcohol 151".
The FILTER function has two required arguments:
- array which is the data we want to filter.
- include which is an array of Boolean values to determine which array values are to be returned. TRUE means the values will be returned, FALSE
Here, we want to list the Brand values, so we need the items in the column Table1[Brand] for the array.
For the include argument, we need to create a conditional test that queries whether the value of the dropdown is in the data or not. For example, Table1[Description]=Description.
The SORT & UNIQUE will help like in the previous example:
=SORT(UNIQUE(FILTER(Table1[Brand],Table1[Description]=Description)))
We entered the formula into cell H2.
The Brand list is now ready, and can be added as a dropdown list by entering =List!H2# for the reference.
The same steps should be followed for the Size list with an addition to the FILTER function's include argument. The difference is, while the Brand list depends only on the selected Description, the Size list is dependent on both the selected Description and Brand values. As a result, both selections should take place in the FILTER function. This field needs to point to two condition arrays:
- Table1[Description]=Description
- Table1[Brand]=Brand
Logical values can be combined in two ways: AND and OR. In Excel, these operations can be done using product (*) and plus (+) operators - use the product operator (*) for AND, and the plus operator (+) for OR. For our example, combining will be an AND operation. Thus, our formula is:
=SORT(UNIQUE(FILTER(Table1[Brand],(Table1[Description]=Description)*(Table1[Brand]=Brand))))
This is the screenshot of the formula which is placed in the cell J2.
The last dropdown list is now ready. The Data Validation reference will be =List!J2#.
It is time to get the Price value based on selections for the dropdowns. To lookup the price values based on multiple columns traditionally required using helper columns or an array formula that made things more challenging.
To do this, we’re again going to use the FILTER formula. Once more, we need to extend the include argument content to include the Size selection.
- Table1[Size]=Size
At this step, we do not need to the SORT and the UNIQUE functions, since we know that the result will be a single value. Here is what our formula will be after including the Size:
=FILTER(Table1[Brand],(Table1[Description]=Description)*(Table1[Brand]=Brand) *(Table1[Size]=Size))
Note that we placed this in the same sheet for demonstration purposes, and you might want to move it to a separate area.
