Dynamic Arrays & LAMBDA

Learning objectives

By the end you can:

1. Explain the spill from first principles, why one formula now returns a whole array, where the "real" formula lives, and how the spill range resizes itself when data changes.
2. Reference a spilled range with the # spilled-range operator (A2#) so that one dynamic formula feeds another and the whole chain stays live.
3. Diagnose and clear a #SPILL! error, naming the three things that block a spill and the exact UI step that finds the obstruction.
4. Write FILTER, SORT, SORTBY, UNIQUE, and SEQUENCE from memory, including multi-condition FILTER (AND with *, OR with +) and the if_empty argument.
5. Use RANDARRAY correctly and state precisely why its volatility makes it a tool to reach for deliberately, not by habit.
6. Run a per-row, per-column, per-element, or accumulating calculation with BYROW, BYCOL, MAP, SCAN, and REDUCE, and say when each is the right iterator.
7. Refactor a long, unreadable formula with LET by naming its intermediate results, gaining both readability and speed (each name computes once).
8. Author a reusable LAMBDA, register it in Name Manager, and call it like a built-in function, building a CAGR helper as the worked case.
9. Replace helper columns and legacy Ctrl+Shift+Enter (CSE) array formulas with single spilling formulas, and build a refreshable report block that updates itself when its source table changes.

Prerequisites & connections

Builds on. EX1.01 (Formulas II) planted the array mindset with SUMIFS/COUNTIFS and the idea that a formula can act on a whole range at once; this module lets that array escape the single cell and spill. EX1.02 (Lookups Mastery) is the direct parent: you met XLOOKUP returning an entire record, and CHOOSECOLS/TAKE/DROP shaping it, those were dynamic arrays in miniature, and here they become the main event. You also need the reference discipline of EX0.02 ($ anchoring, and especially LET and Name Manager, which we deepen) and the Table habit of EX0.01, a spilling formula that reads from a real Table (Ctrl+T) grows and shrinks with the Table automatically, which is half the magic.

Feeds forward. EX2.01 (Data Tools & Integrity) uses UNIQUE and SORT to build self-maintaining data-validation lists, and FILTER behind dynamic dashboards. EX2.02's sensitivity tables are SEQUENCE-driven grids at heart. On the analyst track, dynamic arrays are how a modern model stays refreshable: M2.05's comparable-company screens are one big FILTER+SORT; M3.08's scenario blocks name their assumptions with LET; and a firm-wide library of LAMBDA helpers (a house CAGR, XNPV, XIRR, or day-count function) is exactly how a team stops re-deriving the same formula in every workbook. The habit you build here (never a helper column where a spill will do, never a magic formula where a named LAMBDA belongs) is the spine of professional model design (EX7.01).

Spill: one formula, many answers

First principles. A traditional Excel formula is a function from its inputs to one value, deposited in the one cell that holds it. A dynamic-array formula is a function from its inputs to an array of values, and Excel needs somewhere to put them. Its rule is simple and worth memorising: the formula lives in one cell (the "anchor", always the top-left), computes an entire array, and then spills that array down and to the right into the cells the result needs. Those filled cells are not formulas, they are the overflow of the single formula in the anchor. Click any spilled cell and the formula bar shows the formula greyed out; click the anchor and it shows in black, editable. Delete the anchor and the whole spill vanishes; you cannot delete just one spilled cell.

Intuition. Think of the anchor as a tap and the spill range as the water it pours. You do not fill each cup by hand; you open one tap and it fills exactly as many cups as there is water. When the source table gains a row, the tap pours one cup further, the spill resizes itself. This is the property that kills the dragged helper column: a dragged formula is a fixed number of cups you filled once and must remember to refill; a spill is a tap that always matches the data.