Overview

Dynamic arrays are formulas that can return arrays of variable size. Dynamic arrays have been made available in Excel 2021 (and hence Excel 365).

Prior to dynamic arrays, there were static arrays, aka CSE arrays ( because you have to press Ctrl+Shift+Enter).

One of the benefits of dynamic arrays is that it is calculated at runtime.

References:

• Dynamic arrays and spill behavior
• Calculation differences between dynamic arrays vs CSE arrays

TRANSPOSE

=TRANSPOSE($A$1:$B$6)

Result:

UNIQUE

=UNIQUE($A$1:$B$6)

Result:

SORT

=SORT(UNIQUE($A$1:$B$6))

Result:

FILTER

=FILTER($A$1:$B$6, LEFT($A$1:$B$6, 1)="B")

Result:

Matrix Multiplication

Scalar Multiplication

=$A$1:$B$2*2

Result:

Matrix x Matrix

=MMULT($B$2:$D$4, $G$2:$G$4)

Result:

Case Study

Grouping non-contagious cells with FILTER

Problem: Count all of Adam's grade which are at least 95.

Solution

=LET(grades, FILTER($B2:$G2, {1,0,1,0,1,0}), greater, FILTER(grades, (grades>=95)), COUNT(greater))

Breakdown

LET
name1: grades
value   = FILTER($B2:$G2, {1,0,1,0,1,0})
        = {85, 97, 95}

name2: greater
value   = FILTER(grades, (grades>=95))
        = FILTER({85, 97, 95}, (grades>=95))
        = FILTER({85, 97, 95}, {FALSE, TRUE, TRUE})
        = {97, 95}

calculation: COUNT(LEN(greater))
= COUNT({97, 95})
= 2

Calculating differences between columns

=LET(currentColRef, COUNTA($A$2:$N$2), currentRevenues, INDIRECT(CONCAT("R2C", currentColRef,":R3C", currentColRef), FALSE), currentRevenues - OFFSET(currentRevenues,0,-1))

Breakdown

LET
currentColRef = number of non-empty cells in $A$2:$N$2
        = 3

currentRevenues = values at cells in currentColRef
        = {1 050 000, 50 000}

calculation:
        currentRevenues - column right before currentRevenues
        = {1 050 000, 50 000} - {925 000, 0}
        = {125 000, 5 000}

Result