Dynamic Arrays

Dynamic arrays let one formula return multiple values that spill into neighboring cells. The anchor cell holds the formula; the surrounding spill range holds the computed values. Formulon models the spill shape, dependency edges, and collision behavior as part of recalculation.

Glossary: spill / spill range

The rectangle of cells produced when a dynamic-array formula returns more than one value. The top-left cell (the anchor) holds the formula text; the other cells obtain their values from that anchor and do not hold independent formulas.

Glossary: anchor cell

The cell that owns the dynamic-array formula. Editing or clearing the anchor changes the whole spill. A host API can write to a non-anchor cell: the existing spill projection is invalidated and the anchor becomes dirty. After recalculation, a blank non-anchor cell lets the spill regenerate; a non-blank cell blocks it with #SPILL!.

Legacy CSE array formulas

A legacy Ctrl+Shift+Enter (CSE) array formula is stored as one formula over a fixed block. Writing a value, text, formula or blank to any non-anchor cell fails with kPreconditionFailed and leaves the block unchanged; writing to the anchor replaces the whole block. Every block member reports the block formula: FORMULATEXT returns it in braces ({=...}), and ISFORMULA returns TRUE.

What to expect

• Spill ranges are computed from the formula's result shape (scalar, row, column, or 2-D array).
• A formula that changes shape dirties dependent cells and recomputes their spill anchors.
• Collisions — when a spill would overwrite a non-empty cell — return #SPILL! rather than silently overwriting data.
• Spill winners and legacy array blocks resolve in a fixed recalc order, so the same workbook gives the same result on every run.
• A scalar error broadcasts against an array: the error appears in every element rather than being dropped.
• Dimension mismatches (e.g. mixing a 3-row argument with a 5-row argument under implicit broadcasting) follow Excel's error rules per function family.
• Bare ranges, arithmetic and comparison operators, and IF spill to the shape of their arguments. Blank cells in a bare-range spill become 0.
• Scalar functions evaluate range arguments element-wise. Range-returning built-ins use the common spill allocator, so their results follow the same collision and out-of-grid checks.
• @ applies implicit intersection and reduces a range or array to the value selected by the formula's anchor.
• The spill operator # can use an anchor computed by OFFSET, INDIRECT, CHOOSE, IF, a parenthesised reference, or a LET-bound name. An anchor that resolves to more than one cell returns #REF! instead of being silently narrowed to its top-left cell.

A collision writes no values at all: the anchor shows #SPILL! and the spill range stays empty.

Array size limit

An array a formula evaluates is capped at 107,374,182 elements, whatever its shape or origin. This is the bound measured on Mac Excel 365: A:CX+0 (106,954,752 elements) evaluates, while A:CY+0 and SEQUENCE(1048576,103) (108,003,328) give the number 0 as the formula's whole result. The bound is the same under every profile, although Windows Excel abandons smaller arrays. SUM, AVERAGE, MIN, MAX, PRODUCT and COUNTA fold the result of an operation over whole rows or columns without building it, so a large whole-axis aggregate stays within the budget.

Functions that spill

Spill behavior is most visible with:

=SEQUENCE(5)
=UNIQUE(A1:A100)
=SORT(A1:B20, 2, -1)
=FILTER(A1:C50, B1:B50 > 0)
=LET(x, A1:A10, x * 2)

Those formulas can be run below. The upper table comes from evaluateFormulaArray(), so the rows × columns printed beside it is the shape the engine computed, not a shape drawn to illustrate the idea; the sheet below it is a live formulon-cell grid holding the same formula written into D2 and recalculated, with the selected rectangle read back from spillInfo(). Use Block the spill range to put a value inside that rectangle: the anchor turns into #SPILL! and not one result cell is written — the collision rule above, not a special case built into the demo.

Dynamic arrays and spilling

An array formula is first read back with evaluateFormulaArray(), which returns the whole shape without mutating the workbook, then written into a real cell of the sheet below so spillInfo() can report the region the engine engaged.

Powered by the real Formulon engine (WASM) — it runs entirely in your browser, nothing is uploaded.

A short way to explore the demo

Start with =SEQUENCE(3,4) and note the preview shape. Write it to the sheet, then block its far corner to observe that the anchor becomes #SPILL! without partially writing a result. Clear the blocker, switch to =FILTER(A2:B6,B2:B6>4), and edit a source value in columns A or B. The preview changes immediately; the committed formula keeps its own spill range until it is recalculated.

Recalculation interaction

The recalc engine stores per-anchor spill metadata:

Field	Purpose
Anchor address	Sheet / row / column of the formula owner
Result shape	Rows × columns of the last successful evaluation
Spill error	#SPILL! if the result could not materialize; otherwise null
Dependents on the range	Cells that read from any address in the spill range

When the anchor recomputes to a different shape, dependents anywhere in the old or new spill rectangle are marked dirty.

Inspecting spill state

spillInfo(sheet, row, col) (spill_info() in Python) reports whether a cell anchors a spill and the anchor and size of that rectangle; the MCP formulon_trace tool reads precedents, dependents, and spill info from a session. Use these when a formula returns #SPILL! and you need to find what occupies the target cells.

Compatibility caveats

Dynamic-array semantics depend on workbook-level flags and on whether legacy CSE (Ctrl+Shift+Enter) array formulas exist in the same sheet. For a workbook that mixes the two, compare results against the same file calculated in the target Excel version before relying on them.

Read next

• Recalculation — how dirty cells and spill shape interact.
• Formula coverage — which array-aware functions are registered.
• Error model — how #SPILL! differs from host failures.