Spill and dynamic arrays

Modern Excel (Microsoft 365 / 2021+) introduced a revolutionary change: dynamic arrays. A single formula can now return many results that automatically "spill" into the cells below/beside it — no more copying formulas down, no more Ctrl+Shift+Enter. This changes how you write formulas fundamentally, and it's the foundation of everything advanced that follows. Let's understand spill. Let's write formulas that fill themselves.

What "spill" means

DYNAMIC ARRAYS: one formula can return an ARRAY of results that SPILL into
neighbouring cells automatically.

  =A2:A10 * 2
    -> in ONE cell, returns ALL of A2:A10 doubled, spilling down 9 cells. You
       write the formula ONCE; the results fill the range automatically.

  =SORT(A2:A100)     -> spills the sorted list.
  =UNIQUE(A2:A100)   -> spills the distinct values.
  =FILTER(...)       -> spills the matching rows.

THE SPILL RANGE:
- The formula lives in ONE cell (the top-left, the "anchor"). The rest are
  "spilled" (greyed formula, you can't edit them individually — edit the
  anchor).
- The spill AUTO-RESIZES: if the source grows/shrinks, the output grows/
  shrinks. No manual range-fixing.
- REFERENCE a whole spill range with the # operator: if C2 spills down,
  C2# means "the whole spilled range". =SUM(C2#) sums whatever it spilled.

#SPILL! ERROR — the spill is BLOCKED because something is in the way (a cell
in the spill range isn't empty). Clear the blocking cells + it spills.

Dynamic arrays mean one formula can return an array of results that spill into neighbouring cells automatically. =A2:A10*2 in one cell returns all of A2:A10 doubled, spilling down — you write the formula once and the results fill the range. The formula lives in the top-left anchor cell; the rest are spilled (you edit the anchor, not individual cells). The spill auto-resizes — if the source grows or shrinks, the output follows, with no manual range-fixing. You can reference a whole spill range with the # operator (C2# means "the whole spilled range", so =SUM(C2#) sums whatever it spilled). If a cell is in the way, you get a #SPILL! error — clear the blocking cells and it spills.

Why dynamic arrays change everything

WHY THIS IS A BIG DEAL

- NO MORE COPYING FORMULAS DOWN — one formula fills the whole result range +
  auto-resizes. Cleaner, less error-prone, self-maintaining.

- NO MORE Ctrl+Shift+Enter — old "array formulas" needed a special keystroke +
  a fixed size. Dynamic arrays just work + resize automatically.

- NEW FUNCTIONS built for this: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE,
  RANDARRAY (+ XLOOKUP returning arrays). These return spilling results ->
  powerful, live, formula-based analysis (whole lessons ahead).

- FORMULAS THAT FLOW: chain them — =SORT(UNIQUE(FILTER(...))) -> filter, then
  unique, then sort, in one live formula. Analysis without manual steps.

- LIVE + DYNAMIC — results update automatically as data changes. Build a
  formula-driven report that refreshes itself.

CAVEAT: dynamic arrays need Microsoft 365 / Excel 2021+. OLDER Excel doesn't
have them (and shows @ implicit-intersection quirks). For shared files, mind
the audience's version.

Dynamic arrays are the biggest modern shift in Excel formulas — the base for
FILTER/SORT/UNIQUE/LET/LAMBDA ahead.

Dynamic arrays are a big deal: no more copying formulas down (one formula fills and auto-resizes the range — cleaner and self-maintaining), no more Ctrl+Shift+Enter (old array formulas needed a special keystroke and fixed size; these just work), and a set of new functions built for it — FILTER, SORT, SORTBY, UNIQUE, SEQUENCE (whole lessons ahead) — that return spilling results. Best of all, formulas flow: chain them, like =SORT(UNIQUE(FILTER(...))) — filter, then unique, then sort — in one live formula that updates as data changes. The one caveat: dynamic arrays need Microsoft 365 / 2021+; older Excel doesn't have them, so mind the audience's version for shared files. They're the biggest modern shift in Excel formulas — the base for everything advanced ahead.

The mistake beginners make

The habit-based mistake is still copying formulas down when a dynamic array would spill automatically — manually filling =A22 down a thousand rows when =A2:A10002 (or the whole-column reference) spills in one formula that auto-resizes. Embrace spill — write it once. The second mistake is not understanding the #SPILL! error — panicking when a formula shows #SPILL! instead of realising something is blocking the spill range (a stray value in the cells below); clear the blockers and it spills. The third mistake is using dynamic arrays in files shared with older Excel — building spill formulas that break (show @ or errors) for colleagues on Excel 2019 or earlier. Know your audience's version; for compatibility, use the older copy-down approach. And not learning the # operator to reference spill ranges (=SUM(C2#)), so downstream formulas break when the spill resizes. Embrace spill (stop copying down), understand #SPILL!, mind version compatibility, and use # to reference spill ranges.

Your turn

---

Your turn

1. See spill in action: in one cell, write =A2:A10*2 (with numbers in A2:A10) and watch the results spill down automatically — one formula, many results, no copying.
2. Try the new functions: =SORT(A2:A100), =UNIQUE(A2:A100), and =FILTER(A2:A100, B2:B100>1000) — each spills its results. (Lessons ahead cover them fully.)
3. Cause and fix a #SPILL! error: put a value in a cell where a spill wants to go, see the #SPILL! error, then clear the blocking cell and watch it spill.
4. Use the # operator: make a formula that spills (e.g. =UNIQUE(...) in C2), then reference the whole spill with =SUM(C2#) or =COUNTA(C2#) — and see it update as the spill resizes.
5. Chain formulas: try =SORT(UNIQUE(A2:A100)) to get a sorted list of distinct values in one live formula — analysis without manual steps.

Key points

• Dynamic arrays (Microsoft 365 / 2021+): one formula can return an array of results that SPILL into neighbouring cells automatically — write it once, and the results fill the range and auto-resize as the source changes.
• The formula lives in the top-left anchor cell (edit that); reference a whole spill range with the # operator (C2# = the whole spill, =SUM(C2#)); a #SPILL! error means something is blocking the spill range (clear it).
• It's a big shift: no more copying formulas down, no more Ctrl+Shift+Enter, and new spilling functions (FILTER, SORT, SORTBY, UNIQUE, SEQUENCE) that chain into live analysis (=SORT(UNIQUE(FILTER(...)))).
• Caveat: dynamic arrays need Microsoft 365 / Excel 2021+ — older Excel doesn't have them (mind the audience's version for shared files).
• The mistakes: still copying formulas down when a dynamic array would spill, not understanding #SPILL! (something blocks the spill range), using dynamic arrays in files shared with older Excel, and not using # to reference spill ranges (downstream formulas break on resize).