Dynamic arrays: FILTER, SORT & UNIQUE

Dynamic arrays changed how Excel formulas are written. One formula in one cell can now return a whole block of results, which "spills" into the neighbouring cells. No more dragging a formula down 5,000 rows, and no more Ctrl+Shift+Enter array formulas. These functions are available in Microsoft 365 and Excel 2021 or newer.

Assume a Table named Sales with columns Invoice, Date, Customer, State, Taxable, Rate, GST.

The four you will use daily

=UNIQUE(Sales[State])                       ' distinct list of states
=SORT(UNIQUE(Sales[State]))                 ' the same list, alphabetical
=FILTER(Sales, Sales[State]="Rajasthan", "No records")
=SEQUENCE(12,1,1,1)                         ' 1..12 down a column

FILTER takes three arguments: the array to return, a condition that evaluates to TRUE/FALSE per row, and an optional value when nothing matches. Combine conditions with arithmetic, not AND/OR:

=FILTER(Sales[[Invoice]:[Taxable]],
        (Sales[State]="Rajasthan")*(Sales[Taxable]>50000),
        "None")

* behaves as AND (both must be 1) and + behaves as OR. AND() collapses everything to a single TRUE/FALSE, which is why it does not work here.

Function	Returns
SORTBY(Sales[Customer], Sales[Taxable], -1)	customers ordered by value, largest first
TAKE(SORTBY(a, b, -1), 10)	top 10 rows
CHOOSECOLS(Sales, 1, 3, 5)	only the 1st, 3rd and 5th columns
SEQUENCE(1, 4, DATE(2026,4,1), 31)	a row of dates a month apart
COUNTA(UNIQUE(Sales[Customer]))	number of distinct customers

The spill operator

A spilled range is referenced by its top-left cell plus #. If F2 holds =SORT(UNIQUE(Sales[State])), then F2# means "whatever that formula produced today". Use it everywhere a list can grow:

=SUMIF(Sales[State], F2#, Sales[Taxable])   ' one total per state, spilled

If something occupies a cell where the result needs to go, Excel returns #SPILL!. Clear the blocking cells, or move the formula.

LET: name your intermediate steps

LET defines names inside a formula, which makes long formulas readable and faster, because each named value is calculated once.

=LET(
   state,  H2,
   rows,   FILTER(Sales[Taxable], Sales[State]=state),
   total,  SUM(rows),
   IF(total=0, "No sales", ROUND(total,0))
)

LAMBDA: build your own function

LAMBDA turns a formula into a reusable function. Write and test it in a cell first, then save it under Formulas → Name Manager with a name such as GSTAmount:

=LAMBDA(taxable, rate, ROUND(taxable * rate / 100, 2))

After saving, =GSTAmount(50000, 18) returns 9000. The rule for GST is calculated in exactly one place, so a correction fixes every sheet that uses it. BYROW, BYCOL and MAP apply a LAMBDA across a whole array.

Common mistakes

• Entering a dynamic array formula inside an Excel Table. Tables cannot spill; put the formula outside.
• Using AND(...) in FILTER instead of *.
• Referring to F2:F20 instead of F2#, so new states are silently dropped from a total.
• Sharing the workbook with a colleague on Excel 2016, where these functions show as #NAME?.
• Forgetting the third argument of FILTER, which gives #CALC! when nothing matches.