Dynamic Arrays, first introduced in Microsoft Excel 365 and Excel 2021 have powered users with a revolutionary way to handle data in spreadsheets.
Dynamic Arrays are structured at their core with spill formulas. This simplifies complex functions and tasks thereby making spreadsheets much more efficient and robust. Here, we’ll talk about what dynamic arrays are, how to use them and where to use them.
What are Dynamic Arrays?
Dynamic Arrays comprise of a sequence of multiple results that automatically get filled or spilled into adjacent cells thereby creating an array of outputs. How dynamic arrays are different from traditional Excel formulas is that the traditional formulas return results in a single cell as opposed to a series of cells in Dynamic Arrays.
This dynamism allows for real time expansion and modification of outputs with changes in the original data set. This functionality also eliminates the need for copying functions or formulas across a certain number of cells to make it work.
For example, If we enter a formula like =SEQUENCE (7) in a cell, it automatically spills or extends the results into the rows below (as shown in the screenshot below).
What are Spill Formulas?
Let’s understand what spill formulas are. These formulas are specialized functions that work well with multiple results. The results are thus not a value but a range of values that are indicated by a blue border and can automatically adjust or modify with changes in data or formula. There are wide applications for this formula, some of them being:
- Data sorting and filtering in a dynamic environment
- Random number generation
- Extraction of unique data or values
- Creating calculations which can adjust to dynamic data changes
Due to the self-adjusting nature of spill formulas and the way they work, they form the core of Dynamic Arrays.
Primary Dynamic Array Functions
The Dynamic Array toolkit contains various formulas. Let’s look at some of the most commonly used formulas leveraging the dynamic nature of these arrays.
#1: UNIQUE
The UNIQUE function extracts unique values from a range of inputs.
Syntax: =UNIQUE(array, [by_col], [exactly_once])
Problem Statement: Extract a list of unique Product IDs from a sales dataset.
The formula as shown above, =UNIQUE (A2:A11) extracts all unique values in the Product ID column. Since there are multiple unique values in column A, the UNIQUE function returns all distinct values in cells B2 through B7 as shown in the screenshot above.
#2: SORT
The SORT function organizes data in ascending or descending order, as defined by the user.
Syntax: =SORT (array, [sort_index], [sort_order], [by_col])
Problem Statement: Sort all unique product IDs in descending order.
The formula shown above, =SORT (B2#, 1 -1) takes the Dynamic Array B2# as the input array. Because the length of array starting at B2 can change based on change in Product IDs, this array defined as B2# (starting at cell B2 and flexible in size) will be flexible to the changing inputs.
For sort_index, we use parameter 1 which is the default if no reference to column is provided.
For sort_order, we defined -1 because we have to arrange the outputs in descending order. If we had to sort the array in ascending order, we would have used 1 instead of -1.
#3: FILTER
Excel FILTER function retrieves data based on given criteria.
Syntax: =FILTER (array, include, [if_empty])
Problem Statement: Filter all unique Product IDs in the given table where the sale price of the product is greater than $70.
The formula shown above =UNIQUE (FILTER ((A2:A11),B2:B11>70)) layers the two formulas FILTER and UNIQUE. The inner layer nested inside the UNIQUE formula is the FILTER function to extract all lines of data from column A where the Sale Price is greater than $70.
Once the FILTER function retrieves that data, the UNIQUE function extracts out just the distinct values obtained, further simplifying the results.
FILTER function when used with its spill functionality can be a great asset. You can learn about the complete functionality of FILTER function, using it with logical operators along with sample practice data here.
#4: XLOOKUP
The XLOOKUP function searches one array for a match and returns corresponding values from a second array.
Syntax:=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],[match_mode], [search_mode])
Problem Statement: For each unique Product ID in column C, find the corresponding Sale Price.
The formula shown above, =XLOOKUP (C2#, A2:A11, B2:B11) picks up one value at a time from the dynamic array of unique Product IDs in Column C, searches for each input in Column A and returns its corresponding value from Column B.
What are the Practical Applications of Dynamic Arrays?
Dynamic Arrays offer more than just a technical improvement—they have actual applications that save time and improve efficiency.
#1: Automation of Data Cleaning Process
Set up UNIQUE or FILTER formulas specific to your data cleanup needs and enjoy the benefit of automated data cleanup and filtering process with changes in the dataset.
#2: Creation of Interactive Dashboards
Use spill formulas like SORT and FILTER to always maintain an updated data dashboard. This also allows users to run real-time scenarios with varying data inputs to see how output behaviors change, thereby making data analysis so much more convenient.
#3: Simplification of Sales Model Testing and Forecasting
You can use RANDARRAY function to generate random sales numbers within a given range. This range of data can serve as input to test your data/sales model outputs.
#4: Building Dynamic Reports
Say goodbye to updating reports manually with changes in source data. Dynamic arrays are tied to changes in data and update dynamically to produce the latest reports at any point of time without having to run the numbers manually. This adds the new dimensions of flexibility and scalability to your reports.
How Do Dynamic Arrays and Conventional Formulas Tie Together in Excel?
Dynamic Arrays are backward-compatible with conventional Excel formulas but pose significant advancement over them. For example:
- Conventional Excel formulas require manual changes when datasets enlarge, while spill formulas adjust automatically.
- Dynamic Arrays work flawlessly with structured tables, improving their usability.
Nonetheless, users should note that the possibility of spill errors (SPILL!) exists with the use of spill formulas. More often than not, spill errors occur when there is data present in the range of cells where data needs to be displayed as the result of a spill. Simply clearing the data from obstructing cells fixes the errors.
Conclusion
Dynamic Arrays and spill formulas bridge a substantial gap between spreadsheet simplicity and database-level capability. With this change, Excel has now provided its users with the opportunity to think of data in terms of dynamic relationships rather than static values.
As Excel functionality evolves, mastering Dynamic Arrays will soon become an essential skill for data analysis, report dashboard creation and general management of data.
Get acquainted with the spill functionality of supported Excel formulas and build expert-level skills fast. If you haven’t already, try out these formulas and features in Microsoft Excel 365.
