Exceljet

Quick, clean, and to the point

Dynamic array

Example of dynamic array function in Excel

Above: Using the FILTER function is used get 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 7 new dynamic array functions:

These functions solve long-standing, difficult problems in Excel that could previously only be handled with complex formulas.

Note: Dynamic Arrays are a beta feature available only through the Office Insiders program. Dynamic Array functions are expected to be released in 2019.