Excel Dynamic Arrays Cheat Sheet: FILTER, UNIQUE, SORT, SEQUENCE

Dynamic arrays represent the biggest fundamental shift in spreadsheet computation in 30 years. Formulas now natively return arrays of values that automatically spill into neighboring cells without Ctrl+Shift+Enter.

---

1. Quick Syntax Summary

=FILTER(array, include, [if_empty])     -- Extract matching rows
=UNIQUE(array, [by_col], [exact_once])  -- Extract distinct values
=SORT(array, [sort_col], [order])       -- Sort range (1 = ASC, -1 = DESC)
=SORTBY(array, by_col1, order1, ...)    -- Sort by columns outside the output
=SEQUENCE(rows, [cols], [start], [step])-- Auto-generate number/date sequences
=RANDARRAY([rows], [cols], [min], [max])-- Generate random numbers/dates
=TOCOL(array, [ignore], [by_col])       -- Flatten 2D grid into single 1D column
=TOROW(array, [ignore], [by_col])       -- Flatten 2D grid into single 1D row
=WRAPROWS(vector, wrap_count, [pad])    -- Wrap 1D list into 2D row matrix
=CHOOSEROWS(array, row1, row2, ...)     -- Extract specific rows by number
=CHOOSECOLS(array, col1, col2, ...)     -- Extract specific columns by number

---

2. The Spilled Range Operator (#)

When a formula spills from cell B2 down to B20, you don’t need to write =SUM(B2:B20).

Simply add a # to the origin cell:

=SUM(B2#)
=COUNTA(B2#)
=XLOOKUP(Target, B2#, C2#)

If the source data expands from 20 to 100 rows, B2# automatically expands in real-time!

---

3. Power Combos (Stacking Dynamic Arrays)

Combo 1: Filter, Remove Duplicates & Sort Alphabetically

To create a clean, alphabetically sorted dropdown list of active clients:

=SORT(UNIQUE(FILTER(Clients!A2:A500, Clients!B2:B500 = "Active")))

Combo 2: Top 5 Highest Performing Sales Reps

=CHOOSEROWS(SORT(A2:D100, 4, -1), SEQUENCE(5))