Dynamic arrays can populate a range of cells from the values of an array. With dynamic arrays, any formula that returns an array of values will spill the results into the adjacent empty cells automatically. You can create tools like interactive pricing applications with ease.
This feature is meant to replace the existing array formulas (also known as Ctrl+Shift+Enter) formulas. Dynamic arrays can overcome most restrictions the existing methods impose, and make array formulas easier to work with when applying to a single cell.
The 7 functions below and 2 special operators are part of the dynamic arrays concept:
- Formulas:
- Operators:
These "dynamic array functions" can replace a complex set of formula blocks that also require helper columns. Dynamic array functions return an array of values, except for the SINGLE function. The SINGLE function is used for backwards compatibility with implicit intersection formulas in earlier versions of Excel.
Dynamic arrays also introduced some new error messages like below:
- #SPILL! indicates there is an occupied cell or cells in the spill range. As a result the formula cannot return an array of values.
- #CALC! indicates there is a scenario that Excel's calculation engine does not currently support. For example, =UNIQUE({2,2,3,3,3,4,4,4,4}, ,TRUE) formula returns #CALC! error because there isn’t any value in the array that occurs once.
At the time of writing this article, Microsoft announced that this feature is currently only available to a number of select insider users. When it’s ready, the feature is planned for release for Office 365 users.
