Dynamic Array Formulas

Also known as Spilled Array Formulas


Added in Excel 2021 / Microsoft 365

Formulas that return more than one value are called Dynamic Array Formulas.
Formulas that return more than one value will automatically populate adjacent cells.
Formulas that return more than one value but cannot populate all the adjacent cells will return #SPILL!
When a function (or formula) returns more than one value the formula will create a Dynamic Array Formula.
When a function (or formula) returns multiple values this is referred to as Spilling because the results appear to spill over into adjacent cells.
link - https://support.office.com/en-gb/article/dynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-306b-4282-ae9d-aa88f5502fa2
link - https://support.office.com/en-us/article/dynamic-array-formulas-vs-legacy-cse-array-formulas-ca421f1b-fbb2-4c99-9924-df571bd4f1b4
link - https://templates.office.com/en-gb/dynamic-array-formulas-tutorial-tm67741002
link - https://www.ablebits.com/office-addins-blog/2020/07/08/excel-dynamic-arrays-functions-formulas/


Existing Functions

All the existing functions that can return more than one value will now automatically return multiple values.

MUNITThe unit matrix or the specified dimension.
OFFSETThe value in a cell which is an offset from another cell.
MUNIT
The unit matrix or the specified dimension.
OFFSET
The value in a cell which is an offset from another cell.

New Functions

Most of the "recently added functions" can return multiple values and therefore create dynamic array formulas.
Some people are referring to these new functions as Dynamic Array Functions which is a bit misleading since some of the existing functions can also create dynamic array formulas.

ARRAYTOTEXT(Microsoft 365) The text representation of an array.
BYCOL(Microsoft 365) The results from applying a LAMBDA function to each column in an array.
BYROW(Microsoft 365) The results from applying a LAMBDA function to each row in an array.
LAMBDA(Microsoft 365) The results of a formula added as a named range.
MAKEARRAY(Microsoft 365) The array calculated by applying a LAMBDA function.
MAP(Microsoft 365) The array formed by applying a map using a LAMBDA function.
REDUCE(Microsoft 365) The total value after reducing an array by applying a LAMBDA function.
SCAN(Microsoft 365) The array after appliying a LAMBDA function to each value and returns an array.
VALUETOTEXT(Microsoft 365) The text from any specified value.
FILTER(2021) The array of data in a range that satisfies multiple conditions.
LET(2021) The result of a formula that can use variables.
RANDARRAY(2021) The array of random numbers between 0 and 1.
SEQUENCE(2021) The array of sequential numbers.
SORT(2021) The array of data in a range that has been sorted.
SORTBY(2021) The array of data in a range that has been sorted based on the values in a corresponding range.
UNIQUE(2021) The array of unique values in a list, table or cell range.
XLOOKUP(2021) The value in the same row after finding a matching value in any column.
XMATCH(2021) The position of a value in a list, table or cell range.
ARRAYTOTEXT
(Microsoft 365) The text representation of an array.
BYCOL
(Microsoft 365) The results from applying a LAMBDA function to each column in an array.
BYROW
(Microsoft 365) The results from applying a LAMBDA function to each row in an array.
LAMBDA
(Microsoft 365) The results of a formula added as a named range.
MAKEARRAY
(Microsoft 365) The array calculated by applying a LAMBDA function.
MAP
(Microsoft 365) The array formed by applying a map using a LAMBDA function.
REDUCE
(Microsoft 365) The total value after reducing an array by applying a LAMBDA function.
SCAN
(Microsoft 365) The array after appliying a LAMBDA function to each value and returns an array.
VALUETOTEXT
(Microsoft 365) The text from any specified value.
FILTER
(2021) The array of data in a range that satisfies multiple conditions.
LET
(2021) The result of a formula that can use variables.
RANDARRAY
(2021) The array of random numbers between 0 and 1.
SEQUENCE
(2021) The array of sequential numbers.
SORT
(2021) The array of data in a range that has been sorted.
SORTBY
(2021) The array of data in a range that has been sorted based on the values in a corresponding range.
UNIQUE
(2021) The array of unique values in a list, table or cell range.
XLOOKUP
(2021) The value in the same row after finding a matching value in any column.
XMATCH
(2021) The position of a value in a list, table or cell range.

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext