Excel: array formulas

In older versions of Excel, an array formula is entered by pressing ctrl+shift+enter. This is why they're also referred to as control-shift-enter or CSE formulas.

Recent versions of Microsoft 365 have introduced dynamic array formulas which allow to simply enter the formula in the top left cell of the output area and then pressing enter.

In order to assign an array formula to a range with VBA, the range's property formulaArray must be used instead of formula.

There are two types of array formulas:

• Formulas that aggregate arrays into a single value (sum, average, count, …)
• Formulas that return an array (whose values are spread into multiple adjacent cells, for example countifs(…, {…})

With the September 2018 update of Microsoft 365, the values of a formula that returns an array, are spilled over adjacent cells, either horizontally or vertically. This behavior is referred to as spilling.

A dynamic array formula is a formula that returns an array of variable length.

The values of the returned array are then spilled onto the neighbouring cells of the cell where the formula is located. Thus, they eliminate the need for legacy control shift enter (CSE) array formula.

With the introduction of dynamic array formulas in Excel, the default formula evaluation mode is array evaluation.

As part of this introduction, Excel also introduced the implicit intersection operator @.

In a formula, the values of the formula that returns an array of variable length can be referred to with the cell address plus the hash symbol, for example

• Formula in B1: =unique(A1:A100)
• Formula in C1: =sort(B3#)

A typical example for a function that needs to be entered as a control-shift-enter formula seems to be transpose

Array constants