Most analysts still treat spill as a side effect. It should be a design pattern.
What Spill Actually Means
When you enter =UNIQUE('Raw Data'!B2:B5000), Excel returns every unique value in that column and fills downward automatically. The result occupies a "spill range" - one controlling formula in the top-left cell, read-only outputs below it. Add values to the source and the spill range grows. Remove them, it shrinks.
The 8 native dynamic array functions are FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY, XLOOKUP, and XMATCH. Beyond these, legacy functions like SUMIFS and COUNTIFS also spill in Microsoft 365 when you pass an array as the criteria argument - a behavior that shipped quietly and gets almost no coverage in guides.
As of June 2026, spill is available in Excel for Microsoft 365 and Excel 2021. It does not exist in Excel 2019 or earlier. According to Microsoft's Excel documentation, "dynamic array formulas can return multiple results to a range of cells based on a formula entered in a single cell" - which sounds obvious but has real structural implications for how you lay out a model.
If something blocks the output path, you get #SPILL!. That error and its 6 causes are covered at [/blog/excel-spill-error-causes].
The # Operator Is the Underrated Half
The spill range operator (#) lets you reference an entire spill output dynamically. Write =UNIQUE('P&L'!B:B) in Assumptions!A2 and the rest of your model can reference Assumptions!$A$2# rather than a hardcoded range like A2:A47 that silently breaks when a new cost center is added mid-quarter.
This is where spill goes from interesting to load-bearing. A contribution margin build with 40 SKUs that might become 55 by Q3 board pack has this problem constantly. In Excel 365, SUMIFS spills when the criteria argument is an array:
=SUMIFS('P&L'!$E:$E, 'P&L'!$B:$B, Assumptions!$A$2#, 'P&L'!$C:$C, ">=" & Assumptions!$D$1)
That single formula returns one margin figure per SKU, stacked vertically and aligned to the unique list. Add a SKU to the source data and it appears automatically - no formula range expansion, no pivot refresh.
Before dynamic arrays, this required either a structured table with one SUMIFS per row, or a pivot that had to be manually refreshed after every data paste. Neither approach survives a midnight data drop before a board presentation.
FILTER for Multi-Tab Data Pulls
FILTER is the function that replaces the most helper-column infrastructure. The syntax:
=FILTER(array, include, [if_empty])
A realistic scenario: Raw Transactions has 15,000 GL entries. Your Cash Flow tab needs only entries where the department code matches a dropdown on Assumptions!$B$3 and the posting date falls within the current quarter:
=FILTER(
'Raw Transactions'!A:G,
('Raw Transactions'!C:C = Assumptions!$B$3) *
('Raw Transactions'!D:D >= Assumptions!$D$1) *
('Raw Transactions'!D:D <= Assumptions!$D$2)
)
The * operator applies AND logic across conditions. The result spills a full 7-column view of matching rows. No helper column, no INDEX/MATCH per row, no pivot. When finance pastes a fresh GL export, the Cash Flow tab updates on open.
One practical note: FILTER on a 15K-row range across 7 columns returns results in under 1 second on typical hardware. At 100K+ rows with multi-condition logic, calculation lag becomes noticeable. Scope the source to actual data extent - A2:A15000 rather than A:A - to cut evaluation time significantly.
SEQUENCE for Projection Scaffolding
The least glamorous dynamic array function is also the one that quietly cleans up projection models.
=SEQUENCE(5, 1, YEAR(Assumptions!$B$1), 1)
Five consecutive years starting from the base year in your Assumptions tab. Wire this to the column headers on your FCFF tab and the year row resets when the base year changes - without touching 5 separate header cells.
More useful: combine SEQUENCE with EDATE to generate monthly period headers for a 36-month runway model:
=EDATE(Assumptions!$B$1, SEQUENCE(1, 36, 0, 1))
That spills 36 dates across a row, one per month. Format as MMM YY and you have a self-maintaining period header. Change the start date on Assumptions and all 36 periods update in one recalculation.
A Non-Obvious Pattern: Dynamic SUMIFS Rollup
Here's a pattern worth building around. Combine UNIQUE + # + SUMIFS to produce a fully dynamic rollup table that needs zero structural maintenance as SKUs or cost centers change:
On your Summary tab:
A2: =SORT(UNIQUE('P&L'!$B:$B))
B2: =SUMIFS('P&L'!$E:$E, 'P&L'!$B:$B, $A$2#, 'P&L'!$C:$C, Assumptions!$B$1)
C2: =SUMIFS('P&L'!$E:$E, 'P&L'!$B:$B, $A$2#, 'P&L'!$C:$C, Assumptions!$B$2)
Column A spills a sorted unique list of SKUs. Columns B and C spill matching revenue for two periods, aligned to Column A. The entire rollup table is 3 formulas. When a new SKU appears in the P&L, it shows up in all three columns automatically.
One detail that bites people: if you put a grand total row below this block, hardcoding =SUM(B2:B41) breaks when the list grows past row 41. Use =SUM(B2#) instead - it references the spill range and stays accurate regardless of length.
Where Spill Breaks (and When It Matters)
Spill ranges can't overlap on the same sheet. Two FILTER outputs that would collide produce #SPILL!. Plan your layout with fixed "anchor zones" per dynamic output, or push each to its own sheet.
Spill doesn't work inside an Excel Table (ListObject). A spill formula can't live in a Table column - the Table tries to fill down with copies rather than leaving the spill range alone. Source data works well in Tables; analysis output should live outside them.
The # operator doesn't exist in Excel 2019 or earlier. If your model goes to a bank syndicate running Excel 2016, references like A2# return #NAME?. For models that need to travel, either document the version requirement explicitly or convert spill-dependent sections to static ranges before sharing externally.
Cutting Formula Overhead with ModelMonkey
Writing FILTER formulas with 3-condition AND logic across multi-tab references is where most people pull up Stack Overflow for 20 minutes. ModelMonkey handles this inside Excel directly - describe the filter you need in plain English and it writes the cross-tab formula into the cell, including the correct $ anchoring for criteria arguments. Particularly useful when you're adapting a formula pattern from one model to another where tab names have changed.