For multi-tab financial models, this isn't just a quality-of-life feature. It changes what's possible without helper columns, without Ctrl+Shift+Enter, and without knowing in advance how many rows your output needs.
Microsoft released dynamic arrays to Excel 365 subscribers starting in 2018 (preview) with general availability in 2020. Excel 2021 includes them as well. As of June 2026, they're standard in any modern Excel install - but anything older than 2021 doesn't support them, which matters more than most guides admit.
How Spill Ranges Actually Behave
Enter =UNIQUE('GL'!C:C) in a cell. Excel evaluates the formula, counts the distinct values, and writes all results starting from that cell downward. The top cell shows the formula. Every cell below it shows a grayed-out ghost value - all part of the same array, all read-only.
The spill range is dynamic. Add 3 new cost centers to your GL tab and the UNIQUE output expands automatically on next calculation. Delete 2 and it contracts. No named ranges to maintain, no hardcoded row counts.
According to Microsoft's documentation: "When a formula can return multiple values to neighboring cells, it's referred to as spilling. Formulas that can return multiple results are referred to as dynamic array formulas."
The implication for models is significant. A standalone model tab that pulls a list of cost centers, product lines, or entities from a source tab stays synchronized without any maintenance.
The # Operator: The Part Most Guides Skip
The # (spill range operator) references the entire current output of a dynamic array formula, including as it resizes.
Say cost center names are spilling from Summary!B2 via =UNIQUE('GL'!C2:C). Today that's 14 cost centers. After next quarter's org restructure it might be 17. Reference the full list anywhere with Summary!B2# - the # tells Excel to use the whole spill range, not just cell B2.
A SUMIFS that maps actuals against that dynamic list:
=SUMIFS(
'GL'!E:E,
'GL'!C:C, Summary!B2#,
'GL'!A:A, ">=" & Assumptions!$B$3,
'GL'!A:A, "<=" & Assumptions!$B$4
)
This returns an array of actuals by cost center - 14 values today, 17 after the reorg - sized automatically to match whatever UNIQUE pulled. No hardcoded range for the criteria array, no updating the formula when the cost center list changes.
That's a meaningful difference from the old pattern of SUMIFS with a fixed $B$2:$B$15 criteria range that goes stale the moment anyone adds a line item.
The 6 Dynamic Array Functions (and What They Replace)
| Function | What it does | What it replaced |
|---|---|---|
| FILTER | Returns rows/columns matching conditions | Advanced Filter, manual IFERROR(INDEX(MATCH)) arrays |
| SORT | Sorts a range by one or more columns | Auxiliary sort columns, pivot workarounds |
| SORTBY | Sorts by a separate helper array | Rank-based helper columns |
| UNIQUE | Deduplicated list from a range | Remove Duplicates + manual refresh |
| SEQUENCE | Generates a numeric sequence | ROW(INDIRECT("1:"&n)) hacks |
| RANDARRAY | Random number array | Scattered RAND() cells |
XLOOKUP also returns arrays when the lookup value is an array, making it a cleaner replacement for nested MATCH/INDEX across multiple criteria.
FP&A Use Cases Worth Building
Dynamic Variance Reports
FILTER is the workhorse. Pull every P&L line where variance exceeds your materiality threshold:
=FILTER(
CHOOSE({1,2,3,4}, 'P&L'!B:B, 'P&L'!C:C, 'P&L'!D:D, 'P&L'!E:E),
ABS('P&L'!E:E) >= Assumptions!$C$2
)
Where column E is the budget vs. actuals variance and Assumptions!$C$2 is your threshold (say, $75,000). Every month when actuals land, the list refreshes. At a 38.5% gross margin on $4.2M revenue, line items swinging more than $75K are the ones that get board questions - this surfaces them automatically.
Forecast Headers Without Hardcoding
SEQUENCE replaces the tedious pattern of manually typing month labels or maintaining a header row:
=TEXT(SEQUENCE(1, 12, DATE(Assumptions!$B$1, 1, 1), 30), "mmm-yy")
12 headers starting from your assumption year. Change B1 and all 12 labels update. For a quarterly board pack that gets reused each quarter, this alone saves 5 minutes of cleanup per cycle.
For projection years in an LBO or DCF:
=SUMIFS(
'Revenue'!C:C,
'Revenue'!A:A, SEQUENCE(1, 5, Assumptions!$B$5, 1),
'Revenue'!B:B, "Recurring"
)
Where B5 is the first projection year and SEQUENCE generates the year array {2025, 2026, 2027, 2028, 2029}. Returns 5 revenue totals from a single formula. At a 14.2x EBITDA entry multiple, getting the year-5 terminal value right depends on those projections being accurate - and on the formula actually pulling the right years.
Dynamic Entity or Cost Center Lists
UNIQUE + FILTER together replace the manual step of maintaining a master list:
=SORT(UNIQUE(FILTER('GL'!C:C, 'GL'!B:B = Dashboard!$B$1)))
Unique cost centers for the selected entity, sorted alphabetically. Reference the output downstream with C2# instead of a hardcoded range. The list stays current as the GL grows.
For a bank syndicate DCF where you're pulling data from a shared GL export, this removes a whole category of "the list doesn't match" errors.
Compatibility: The Problem Nobody Warns You About
Dynamic arrays don't work in Excel 2019 or earlier. When Excel 365 opens a pre-dynamic-array file, it prepends @ to formulas that previously used implicit intersection - so you'll see =@VLOOKUP(...) in old models. That's Excel preserving backward-compatible behavior, not an error.
The painful direction is the other way. A model you build with FILTER or UNIQUE returns #NAME? on Excel 2019. No graceful degradation, no warning - just broken formulas.
If your model goes to a bank syndicate, an LP, or an acquirer's diligence team, test it first. A reasonable workaround for critical cells:
=IFERROR(FILTER('P&L'!B:D, 'P&L'!D:D<>0), "Dynamic arrays not supported")
That at least fails gracefully. For models that genuinely need to run on older Excel, stick to traditional array formulas entered with Ctrl+Shift+Enter or use SUMIFS + hardcoded ranges.
Spill Ranges Don't Work Inside Tables
One specific constraint matters for analysts who build input tables with Ctrl+T: you can't use most dynamic array functions when their output range intersects an Excel Table boundary. FILTER, UNIQUE, and SEQUENCE all return #SPILL! in this scenario.
The practical split: use Tables for input data (the structured reference syntax like Table1[Revenue] is worth keeping), but place dynamic array formulas in regular cells outside or adjacent to the Table. A separate output zone that's not formatted as a Table avoids this entirely.
This is the kind of thing that wastes 20 minutes when you first hit it because the formula looks correct and the error message isn't helpful.
A Note on XLOOKUP as a Dynamic Array Function
XLOOKUP returns an array when the lookup value is an array. Pass it a range as the lookup value and it returns a range as the result:
=XLOOKUP(
'Returns Analysis'!B3:B10,
'Assumptions'!$C$2:$C$50,
'Assumptions'!$D$2:$D$50,
"N/A"
)
This replaces 8 individual VLOOKUP or INDEX/MATCH formulas with one. The spill range adapts automatically when rows 3:10 changes to 3:15.
As of June 2026, XLOOKUP is available in Excel 365, Excel 2021, and Excel for the web. It's not available in Excel 2019 or earlier.
Building a model where spill ranges feed into other spill ranges - UNIQUE into SUMIFS into a summary tab - creates dependency chains that are harder to audit than traditional cell references. When something breaks at the summary level, tracing it back through 3 layers of dynamic formulas across tabs takes time.
ModelMonkey can trace those cross-tab formula dependencies and flag what's broken and why. If you're spending time debugging rather than analyzing, try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.