#SPILL! in Volatile Functions: Why OFFSET and INDIRECT cause spill loops

The #SPILL! error occurs when combining dynamic array formulas with volatile functions like OFFSET and INDIRECT because Excel cannot establish a stable, fixed size for the output range. Dynamic array engines require deterministic boundaries before allocating memory to display results. Because volatile functions recalculate on every worksheet event and dynamically alter range boundaries, pairing them with array operations often creates circular sizing conflicts, causing Excel to return #SPILL! or enter calculation loops.

Quick Fix

Replace OFFSET and INDIRECT with non-volatile, index-based functions such as INDEX, CHOOSEROWS, CHOOSECOLS, TAKE, or structured Table references. For dynamic ranges, use INDEX(Range, 1):INDEX(Range, Count) instead of OFFSET(). This gives Excel a deterministic range boundary that dynamic arrays can calculate without triggering spill errors.

---

The Underlying Conflict: Volatility vs. Array Sizing

To understand why this error happens, examine how Excel handles modern dynamic arrays versus legacy volatile functions:

┌────────────────────────────────────────────────────────┐
│               DYNAMIC ARRAY ENGINE                     │
│  1. Calculates exact return size (e.g., 5 rows, 1 col) │
│  2. Checks destination grid for space                  │
│  3. Locks the spill boundary and writes results        │
└───────────────────────────┬────────────────────────────┘
                            │
                    COLLIDES WITH
                            │
┌───────────────────────────▼────────────────────────────┐
│             VOLATILE FUNCTIONS (OFFSET / INDIRECT)     │
│  • Recalculate on EVERY worksheet change               │
│  • Range size shifts dynamically during calculation    │
│  • Return memory pointers rather than static values    │
└────────────────────────────────────────────────────────┘

When an array formula relies on OFFSET or INDIRECT, Excel encounters an unresolved sizing conflict:

1. It needs to know how many cells the formula will return to determine if the spill range is unobstructed.
2. To know the size of the return, it must evaluate OFFSET or INDIRECT.
3. Evaluating OFFSET often relies on functions like COUNTA(A:A) to establish height.
4. As the array spills, it populates cells that alter the calculation state, causing the volatile function to request a new range dimension.
5. Unable to determine whether the boundary is 5 rows, 10 rows, or infinite, Excel halts and returns #SPILL! with the diagnostic note: “Spill range is unknown or volatile.”

---

Common Breaking Scenarios

1. The Classic OFFSET Dynamic Range Trap

Before modern dynamic arrays, the standard technique to create an expanding range was:

=OFFSET(A1, 0, 0, COUNTA(A:A), 1)

If you wrap this formula inside modern functions like SORT or UNIQUE:

=SORT(OFFSET(A1, 0, 0, COUNTA(A:A), 1))   <-- Triggers #SPILL!

If this formula is placed anywhere in Column A, COUNTA(A:A) counts the formula itself, expanding the height, which prompts the formula to request another row and create an immediate loop, as detailed in #SPILL! with Indefinite References (e.g., A:A). Even if placed in another column, volatile memory mapping in OFFSET can overflow available worksheet grid coordinates, similar to the limit breaks seen in #REF! in OFFSET: When the offset exceeds sheet limits (Row 1,048,576).

The Solution:
Use non-volatile structured references or modern array trimming:

=SORT(FILTER(A:A, A:A<>""))

Or, if using formal Excel Tables:

=SORT(DataTable[ColumnName])

---

2. Using INDIRECT to Assemble Dynamic Multi-Sheet Arrays

Spreadsheet designers often attempt to consolidate multiple monthly tabs by building text strings inside INDIRECT:

=FILTER(INDIRECT("'" & A1 & "'!B2:B100"), INDIRECT("'" & A1 & "'!B2:B100")<>"")

When dragged across multiple cells or paired with functions like VSTACK, INDIRECT creates performance bottlenecks and sizing errors. Because INDIRECT cannot resolve its target range until runtime calculation passes occur, Excel’s calculation dependency tree cannot map the required spill footprint in advance. If the sheet name changes or calculation passes lag, the array fails to spill.

The Solution:
Avoid INDIRECT for multi-range stacking. Modern Excel provides native 3D consolidation tools and functions like VSTACK that accept explicit range arrays:

=VSTACK(Jan:Dec!B2:B100)

Followed by a simple FILTER to strip blank cells:

=FILTER(VSTACK(Jan:Dec!B2:B100), VSTACK(Jan:Dec!B2:B100)<>"")

---

3. Slicing Arrays: OFFSET Inside LAMBDA or MAP Iterations

Attempting to slide a calculation window down a column (such as calculating a rolling 3-period average) using OFFSET inside a dynamic array generates severe calculation errors:

=MAP(SequenceRange, LAMBDA(s, AVERAGE(OFFSET(A1, s, 0, 3, 1))))  <-- Unstable

OFFSET forces Excel to jump out of the vector engine and perform sheet-level coordinate lookups on every single step of the array loop, often resulting in calculation freezes or #SPILL! failures.

The Solution:
Use INDEX or CHOOSEROWS:

=MAP(SEQUENCE(10), LAMBDA(r, AVERAGE(CHOOSEROWS(A1:A50, SEQUENCE(3, 1, r)))))

CHOOSEROWS and INDEX operate directly on in-memory arrays without requesting worksheet coordinate updates, preserving speed and stability.

---

Non-Volatile Replacement Blueprint

To eliminate volatile #SPILL! errors permanently, replace older patterns with modern alternatives:

Legacy Volatile Pattern	Modern Non-Volatile Replacement	Operational Advantage
=OFFSET(A1, 0, 0, 10, 1)	=INDEX(A:A, 1):INDEX(A:A, 10)	Non-volatile; locks exact grid bounds.
=OFFSET(A1, 0, 0, COUNTA(A:A))	=FILTER(A:A, A:A<>"")	Native array evaluation; skips blanks.
Dynamic Row Truncation	=TAKE(Array, 10)	In-memory slicing; cannot cause spill loops.
Dynamic Column Extraction	=CHOOSECOLS(Array, 2)	Direct indexing; avoids sheet recalculation passes.

Eliminating OFFSET and INDIRECT from your array workflows not only resolves #SPILL! errors, but also prevents workbook calculation lag. For deeper coverage of system-level performance problems caused by volatility, consult The Volatile Function Bloat: How INDIRECT and OFFSET kill system performance..

For broad dynamic array troubleshooting protocols across all calculation boundaries, refer to Troubleshooting #SPILL! and #CALC! Errors: The Dynamic Array Diagnostic Guide.