Creating a dynamic dropdown list that expands or contracts with your source data is a fantastic way to enhance user experience in an Excel workbook! In this post, we’ll explore how to set up a dynamic dropdown list in Excel, using two types of data sources: a column in an Excel table and the output from a dynamic array generated with Python in Excel. You can follow along with the downloadable file below:
From an Excel table
Let’s start by setting up a dynamic dropdown data source using a column from an Excel table. In this example, I’m preparing to create a dynamic measures tool with Python in Excel, allowing the user to choose an aggregation type from the dropdown based on the options in the Operation column of the table. This selection will then drive the downstream calculations.
To get started, select the entries in this column and go to Formulas > Name Manager > New:
Instead of basing this range reference on specific cell locations, we’ll use the table and column names for a more flexible setup. Be sure to include the equals sign (=) in the reference.
I’ll name this range something like agg_methods.
With this dynamic input source ready, we can now use it for the data validation list. Select the cell where you want to add the dropdown, then go to Data > Data Validation > Allow: List. Set the source to the named range agg_methods — and again, don’t forget to start the reference with an equals sign (=).
Take a look at the dropdown — you’ll see the methods listed as options. Try updating the entries in the source column, and watch the dropdown update automatically. Nice!
Next, let’s explore how to achieve the same result using dynamic arrays as the data source.
From a dynamic array (including Python in Excel outputs)
Next, let’s take a look at doing the same thing, but this time using dynamic arrays as the source — the trick here is to use the spill operator.
Dynamic arrays serve as outputs for many things in Excel, including dynamic array functions. In the context of Python in Excel, outputs are also returned as dynamic arrays, and we’ll use that source for this example.
In this case, I’ll write some Python code to retrieve all the numeric columns in the dataset. We could use this, for example, to create dynamic measures within the visualization.
With my dynamic array in the workbook, I’ll go back to the Name Manager. Instead of referencing the exact cell range for the cell area reference, I’ll simply add a # after the first cell in the array.
This refers to the entire spilled range of the array, making the reference dynamic and adaptable to changes in the array itself. If you’re not familiar with this # notation, you can learn more in this blog post:
Now let’s do the same thing in the Data Validation setup, again make sure to include the equals to link to this dynamic input source, like so:
You should then similarly be able to use a dynamic set of dropdown options that will change when the source inputs change:
Using a dynamic array as the source for your dropdown list is particularly powerful, as it lets you combine functions like SORT() and FILTER() to organize and segment your data before it reaches the user. This way, you can ensure dropdowns are both relevant and easy to navigate.
Do you have any questions about setting up data sources for dynamic dropdowns in Excel? Let me know in the comments. Make sure you’re comfortable with this technique, as it’s a fantastic tool for creating more interactive and flexible data visualizations, dashboards, and reports in Excel!
Leave a Reply