Above: Using the FILTER function to return multiple matches on “red”.
In fall 2018, Microsoft announced a new feature in Excel called “dynamic array formulas”. In a nutshell, this feature allows formulas to return multiple results to a range of cells on the worksheet based on a single formula entered in one cell. This behavior is called " spilling " and results appear in a " spill range “. All results are dynamic – if source content changes, the results dynamically update to stay in sync.
To leverage this feature, Microsoft also released new dynamic array functions:
| Function | Purpose |
|---|---|
| FILTER | Filter data and return matching records |
| RANDARRAY | Generate array of random numbers |
| SEQUENCE | Generate array of sequential numbers |
| SORT | Sort range by column |
| SORTBY | Sort range by another range or array |
| UNIQUE | Extract unique values from a list or range |
| XLOOKUP | Modern replacement for VLOOKUP |
| XMATCH | Modern replacement for the MATCH function |
These functions make many long-standing and difficult problems in Excel much easier.
For details and examples, see: Dynamic Array Formulas in Excel
Dynamic Arrays are only available in Office 365 .
Dynamic Excel refers to versions of Excel that offer Dynamic Array Formulas , currently Excel 365 and Excel 2021. We use the term Legacy Excel to refer to all other (non-dynamic) versions.
In fall 2018, Microsoft announced new functionality in Excel called “dynamic array formulas”. Dynamic array formulas have the ability to natively work with multiple values and can “spill” these results onto the worksheet.
The new functionality is provided in two parts:
- New functions designed to leverage dynamic array behavior directly
- New formula engine that lets older formulas and functions use dynamic arrays.
This is a big upgrade and a radical change to the way Excel behaves in some circumstances, and it is only available in Excel 365 and Excel 2021 for now. This means all other versions of Excel, including 2016 and 2019, do not have dynamic arrays.
For a quick summary of dynamic arrays, see: Dynamic Array
For details and examples, see: Dynamic Array Formulas in Excel
Dynamic Arrays are only available in Office 365 .
