xlsgen > overview > Dynamic arrays

Dynamic arrays

Dynamic arrays is a formula mode meant to replace the array formula mode. Microsoft calls the old array formula mode, the one with {...} curly braces (obtained by hitting Ctrl+Shift+Enter on the keyboard), legacy array formulas. Legacy array formulas are still available for compatibility reasons in Excel but dynamic arrays are now the preferred mode.

Dynamic arrays are much easier to use in practice because there is no surrounding or escape symbol. Dynamic arrays are automatically created whenever the calculation engine encounters a formula result holding more than one value, i.e. a vector of values or a matrix of values. When this occurs the resulting values are spilled in other cells near the current cell. In Excel the formula bar shows the formula greyed out in all cells but the first one, making it possible to understand where the values in cells come from.

Dynamic arrays were introduced in fall 2018 in the subscription-based Excel version. And got ported to a licensed version, namely Excel 2021. xlsgen adds it too.

Example : =SORT(A2:A10), this SORT() formula returns the a range of same size than the input range, and as such is a perfect example of dynamic array formula. Dynamic array formula may appear just about everywhere where a formula result in a cell is more than one literal value.

Dynamic array formulas are not compatible with Excel version earlier than Excel 2021 so in those previous versions they appear as legacy array formulas but with a potential breaking behavior because calculating a dynamic array formula and a legacy array formula isn't the same : the first is cell-based, the other is range-based. In either case, Microsoft chose not to support dynamic array formulas in XLS files. In other words this is for XLSX, XLSB and their variants (XLSM, XLTM, ...)

Because dynamic array formulas may not be suitable in some scenarios, xlsgen provides a property (at the workbook level called DynamicArray) which can disable it altogether. Dynamic arrays are on by default because they provide an inherently better mechanism for working with formulas than legacy array formulas.