Dynamic array functions represent one of the most exciting new features in Excel. Although generally as straightforward in syntax to use as other Excel functions, their underlying mechanism for handling data differs significantly.
Unlike traditional functions that deliver a single data piece in a single cell, dynamic array functions can return multiple pieces of data, or an array, simultaneously. In this post, we will explore how to leverage the power of this spill range to construct similarly dynamic summaries and aggregations.
Please feel free to follow along using the exercise file provided below:
Dynamic array spill ranges and the spill operator
To start, let’s discuss why dynamic array functions in Excel are unique and the necessity of a spill range.
Dynamic array functions can generate multiple outputs from a single formula, filling a range of cells—known as a spill range—with results from one input formula. The spill range’s size and shape dynamically adapt based on input or data changes. For example, if you were to add a new product to the dm_sales table in the demo workbook, you will see the results in H2 expand by one row.
Should any cells within the intended spill range be occupied, Excel will display a #SPILL! error, signaling an obstruction preventing full display of the array.
Aggregating dynamic array spill ranges
Next, let’s see how to reference the complete dynamic array in further formulas or analysis, use the spill operator #. Adding # to the array’s initial cell reference (e.g., A1#) tells Excel to reference the entire spill range, not just the top-left cell. This ensures that downstream aggregations or references involving the dynamic array adjust dynamically, preserving the accuracy and relevance of your data analysis.
For example, using the COUNTA() function on the H3# spill range will essentially take a count of unique items.
Try adding or removing products from the source data table; both the unique item array and the downstream aggregation will update automatically.
Use case: dynamic drop-down
Now that you understand the basics of spill ranges and the spill operator, let’s explore a more complex use case: creating a dynamic dropdown list based on the sales reps represented in the dataset.
First, I’ll use the UNIQUE() function again to find all unique entries by combining the first name and last name columns:
To display the names in a dropdown in the format “last name, first name,” I need to rearrange the columns dynamically. This ensures that the list updates automatically if any names are added or removed.
To concatenate the columns, I’ll refer to the spill range. To reference both the first and second columns of the spill range, I can use the INDEX() function. Here’s how I’ll access the second column of the spill range and then the first:
Here, I’ve concatenated the second column with the first, using the necessary comma as a delimiter.
Now, the exciting part is that this new name format is also a dynamic array! To refer to this range with the spill operator, you can use it with data validation. Go to Data > Data Validation, then under List, set the source to =J2# to successfully link the dropdown to a dynamic array:
There’s much more potential with the spill operator when used alongside dynamic array functions. You can even integrate these with visualizations to create dynamic, interactive charts.
To master the essentials of dynamic array functions and other crucial features for creating dynamic, powerful analytics tools in Excel, check out my book Modern Data Analytics in Excel:
Leave a Reply