Excel has significantly transformed. Historically, Excel followed a straightforward rule: one formula, one cell.
Even with advanced formulas, every formula requires its own cell. However, this has changed drastically with the introduction of dynamic arrays in Excel, allowing a single formula to populate multiple cells.
Microsoft’s upgrade to Excel’s calculation engine made this revolutionary feature possible.
The term "dynamic arrays" might initially seem complex, but its simplicity and power become evident once you understand how it works. The ability to fill multiple cells with a single formula marks a fundamental shift in how Excel operates.
Note: At the time of writing, dynamic arrays are only available in Excel 365, Excel Online, and Excel 2021. Older versions like Excel 2019 and earlier will not receive this feature.
Overview of Dynamic Arrays
Let’s start with a simple example to illustrate the impact of dynamic arrays:
=B2:B6
This formula might seem unusual, but it effectively demonstrates how dynamic arrays work. Previously, Excel could only return one result per formula.
Now, a single formula can automatically return multiple results, spilling them into adjacent cells.
Before Dynamic Arrays
In Excel 2019 and earlier, a formula could only return one cell result. If a formula attempted to return multiple results, Excel made assumptions about which data to return, often leading to varying outcomes.
- If the formula aligned with the data, Excel assumed you wanted the value from the same row.
- If the formula was not in line, Excel returned a #VALUE! error.
In the image above:
- Cell D2 shows the value from B2 since it aligns with the source data.
- Cell D4 returns the value from B4 for the same reason.
- Cell D7, which does not align with the data, returns a #VALUE! error.
This behavior, known as implicit intersection, has been eliminated with dynamic arrays.
With Dynamic Arrays
In newer Excel versions that support dynamic arrays, the formula returns all five values, automatically populating multiple cells.
The terminology:
- Spilling: The action of filling multiple cells with a single formula.
- Spill Range: The range of cells filled by the formula.
Dynamic arrays enable a single formula to populate multiple cells, effectively eliminating the need for repetitive formulas in adjacent cells. Instead of copying formulas down manually, the entire result set can be obtained with a single command.
Which Versions of Excel Have Dynamic Arrays?
Dynamic arrays were introduced in September 2018 for early testers who signed up to receive previews of new Excel features. Over time, Microsoft gradually rolled out this feature to all Excel 365 users:
- Monthly Channel: From November 2019.
- Semi-Annual Channel: From July 2020.
Excel 2019 and earlier versions won’t be updated to include this feature. To access dynamic arrays, you need Excel 2021 or Microsoft 365. The dynamic array feature is exclusive to the latest versions, so it’s worth upgrading if you work with Excel frequently.
New Dynamic Array Functions
Alongside dynamic arrays, Microsoft introduced six new functions that leverage this powerful feature:
- UNIQUE: Lists unique or distinct values in a range.
- SORT: Sorts values in a range.
- SORTBY: Sorts values based on another column.
- FILTER: Returns only values meeting specific criteria.
- SEQUENCE: Generates a sequence of numbers.
- RANDARRAY: Returns an array of random numbers.
These new functions help illustrate the versatility of dynamic arrays. The real game-changer, however, is that dynamic arrays affect not only these new functions but also how Excel handles all formulas.
UNIQUE
The UNIQUE function identifies unique values in a range. For instance, consider the following formula:
=UNIQUE(A2:A10)
This formula returns only the distinct values from the specified range. Dynamic arrays enable the results to spill across multiple cells automatically.
SORT
The SORT function organizes data in ascending or descending order. Here’s an example:
=SORT(A2:A10, 1, 1)
The second argument specifies the column to sort by (1 means the first column), and the third argument indicates the sort order (1 for ascending and -1 for descending).
SORTBY
The SORTBY function sorts values based on another column or range:
=SORTBY(A2:A10, B2:B10, 1)
This sorts the range A2:A10 based on the corresponding values in the B2:B10 range.
FILTER
The FILTER function returns only values meeting specific criteria:
=FILTER(A2:A10, B2:B10="Completed")
This formula retrieves all values in A2:A10 where the corresponding value in B2:B10 equals "Completed."
SEQUENCE
The SEQUENCE function generates a sequence of numbers:
=SEQUENCE(5, 1)
This formula returns a column with five rows of consecutive numbers. The first argument specifies the number of rows, and the second argument indicates the number of columns.
RANDARRAY
The RANDARRAY function returns an array of random numbers:
=RANDARRAY(5, 2)
This formula generates a 5-row by 2-column array of random values.
Dynamic Array Formulas
Apart from the new functions, many existing functions like SUMIFS and VLOOKUP can now spill.
Before Dynamic Arrays
To calculate the total score for each person, we used to apply SUMIFS like this:
=SUMIFS($C$2:$C$9,$B$2:$B$9,E2)
This formula would then be copied down for each individual:
With Dynamic Arrays
With dynamic arrays, one formula can achieve the same result:
=SUMIFS(C2:C9,B2:B9,E2:E5)
The formula returns all results, spilling them into cells F2-F5.
The power of dynamic arrays lies in the ability to adjust to changes in data ranges automatically. When new rows are added to the data, the spill range expands or contracts accordingly, making it incredibly efficient and reducing the need for manual adjustments.
Which Formulas Spill?
Functions like SUM, AVERAGE, MIN, and MAX won’t spill themselves since they expect a range of values and only return one result.
However, functions like VLOOKUP spill when given multiple scalars (individual values). For example, VLOOKUP in cell F7 looks up "Dave" and "Jack," returning their scores across multiple rows.
In the example above, the formula is:
=VLOOKUP(E7:E8, B2:D5, 2, FALSE)
This retrieves scores for both "Dave" and "Jack" from the range B2:D5. Dynamic arrays handle the calculations instead of writing multiple formulas in one go.
Other Functions
Generally, any function with an argument that expects a single value (a scalar) is likely to spill if given multiple values instead.
- INDEX: Returns multiple values if the row or column argument is a range.
- IF: Can spill results if the logical test returns multiple values.
- MATCH: Returns multiple matches if given an array.
The new calculation engine fundamentally changes how Excel formulas operate, enabling users to work more efficiently with their data.
Spilling
When a formula spills, a blue outline appears around the spill range, indicating the area filled by the formula. Editing can only occur from the top-left cell. Other cells in the spill range will display a grayed-out formula version.
If data is blocking the spill range, Excel returns a #SPILL! error. Clicking on the error shows the spill range and indicates what’s causing the problem.
Common Reasons for #SPILL! Errors
- Spill Range Exceeds Worksheet Boundaries: The spill range extends beyond the available rows and columns of the worksheet.
- Unknown Spill Range Size: Excel cannot determine the size of the spill range.
- Dynamic Array Formula Placed Inside a Table: Excel tables do not support dynamic array formulas.
- Spill Range Contains Merged Cells: Merged cells can prevent results from spilling.
- Excel Runs Out of Memory: The spill range is too large for Excel to handle.
Resolving #SPILL! Errors
To resolve these errors, ensure that:
- The spill range fits within the worksheet.
- No data blocks the spill range.
- Dynamic array formulas are not placed inside tables.
- There are no merged cells in the spill range.
# References
Use the # symbol to refer to the entire spill range. For example, if the top-left cell is F2, then F2# will reference all cells in the spill range.
The formula =AVERAGE(F2#), and the # symbol ensures the average covers all spilled values.
Advantages of # References
- Automatic Expansion: The reference expands or contracts as the spill range changes.
- Flexibility: Makes working with dynamic data simpler and more intuitive.
- Less Maintenance: Reduces the need for manual adjustments to formulas.
Example with # References
The # reference dynamically includes all values in the spill range F2# in the following formula.
=SUM(F2#)
This ensures the total sum always includes the full range, even if the number of rows changes.
Constant Arrays
Constant arrays have existed in Excel for some time, but dynamic arrays have made their usage more popular. Here's an example with VLOOKUP:
=VLOOKUP(F6,B2:D5,{2,3},0)
This formula returns values (2 and 3) in columns G3 and H3. Commas separate columns, while semicolons separate rows.
To spill results across rows instead of columns, use semicolons:
=VLOOKUP(F6,B2:D5,{2;3},0)
Creating Constant Arrays
Constant arrays can be created manually by enclosing the desired values in curly braces {}.
- Columns: Separate values with commas {1,2,3}
- Rows: Separate values with semicolons {1;2;3}
Examples
- Horizontal Array: {1, 2, 3}
- Vertical Array: {1; 2; 3}
- 2D Array: {1, 2; 3, 4}
Application with Functions
Constant arrays can be used with various functions to produce dynamic results. For example:
=SUM({1,2,3})
This sums up the values within the constant array.
=VLOOKUP(F6,B2:D5,{2,3},0)
This retrieves values from multiple columns simultaneously using a constant array.
The @ Symbol
The @ symbol forces Excel to operate in the old implicit intersection mode. Let’s revisit our original example:
=B2:B6
Add the @ symbol to use the old implicit intersection method, which only returns values in the same row.
=@B2:B6
Backward Compatibility:
To ensure backward compatibility, the @ symbol is added automatically to some formulas. This ensures that workbooks created in older versions of Excel continue to calculate the same results when opened in the newer version.
Structured References and the @ Symbol
The @ symbol is also used in structured references with Excel tables to reference items in the same row.
=[@Column1]
This refers to the value in "Column1" within the table's current row.
Examples of @ Usage
- Single Value Lookup: =@VLOOKUP(E2,B2:C10,2,FALSE)
- Table References: =SUM([@Sales])
Summary of @ Symbol Usage:
- Backward Compatibility: Automatically added to formulas from older Excel versions.
- Structured References: Specifies items in the same row within a table.
- Implicit Intersection: Returns a single value from a range.
Final Thoughts
Dynamic arrays bring new terminology and ways of working to Excel. The changes may initially seem confusing, but they provide Excel users with powerful new tools and make Excel easier to use.
By embracing dynamic arrays, users can streamline their workflows, reduce manual effort, and think about data differently. Whether working with new functions like UNIQUE and SORT or traditional functions like SUMIFS and VLOOKUP, dynamic arrays offer unprecedented flexibility and efficiency.
Here’s a quick recap of the key points:
- Dynamic Arrays Overview: Enable one formula to populate multiple cells.
- New Dynamic Array Functions: UNIQUE, SORT, SORTBY, FILTER, SEQUENCE, RANDARRAY.
- Spilling: Blue outline indicates spill range; #SPILL! errors can occur if data blocks the spill range.
- # References: Use the # symbol to reference the entire spill range.
- Constant Arrays: Specify arrays directly using curly braces {}.
- @ Symbol: Ensures backward compatibility and allows implicit intersection.
While dynamic arrays represent a significant change in Excel's operation, they ultimately empower users to work more efficiently with their data. With practice, you’ll soon see the immense value dynamic arrays bring to Excel.
Keep Learning
» Master Excel Array Formula: A Comprehensive Guide
» What's New in Microsoft Office 2021?
» XLOOKUP vs. INDEX & MATCH vs. VLOOKUP Showdown
» Excel Skills: Essential 23 for Basic, Intermediate, and Advanced Levels
» Excel Education - Master Spreadsheets and Data Analysis