The shift started with build 16.0.11629 (Office 365, September 2019). Microsoft's documentation describes it as "a new calc engine that allows formulas to return arrays that automatically spill onto the sheet." That understates it. For FP&A work with 8-tab linked models, it's a meaningful structural change.
Legacy vs. Dynamic Formula: What Actually Changed
| Behavior | Legacy (pre-2019) | Dynamic Array (2019+) |
|---|---|---|
| Multi-value output | Ctrl+Shift+Enter, fixed range | Single cell entry, auto-spill |
| Output range sizing | Manual, breaks when data grows | Automatic, adjusts with data |
| Error handling | IFERROR wrapper required | [if_not_found] argument in XLOOKUP |
| Cross-tab arrays | Complex nested INDIRECT/OFFSET | Clean sheet references spill across tabs |
| Table compatibility | Full | Spill output can't land inside a Table |
| Referencing output | Fixed cell references | Spill reference with # operator |
The # operator is worth knowing immediately. If a FILTER formula anchored at D5 spills 12 results, referencing ='Returns'!D5# from another tab returns the entire spill range - not just D5. That single character replaces a lot of OFFSET gymnastics.
The 6 Dynamic Formula Functions and How They Actually Rank
Not all six are equally useful in a real model. Honest ranking for FP&A work:
FILTER is the one you'll use constantly. It replaces the SUMIFS-plus-manual-copy workflow for pulling subsets of data. Pull every deal above $500K from a CRM export, every headcount line for a specific cost center, every period where EBITDA margin dropped below 18.5% - one formula, auto-spill, updates when the source data does. This is the function that eliminates the most manual work.
XLOOKUP displaced INDEX/MATCH for most lookup tasks. It searches left or right, handles arrays natively, and the [if_not_found] argument kills the IFERROR wrapper. According to Microsoft's Excel blog (July 2023), XLOOKUP outperforms VLOOKUP on large datasets because it doesn't scan the entire column - it binary-searches when the data is sorted. On a 12-month rolling model with 50,000+ rows of transaction data, that difference is measurable.
SEQUENCE is the underrated one. For building date series, cohort grids, or sensitivity tables, it replaces manual numbering and drag-fill completely. A 60-period DCF timeline:
=SEQUENCE(60, 1, DATE(Assumptions!$B$3, 1, 1), 30)
Change the start date in Assumptions and the entire date spine shifts. Wire this to your period headers and every formula that references those headers updates automatically.
UNIQUE and SORT are utility functions. Useful for deduplication and sorted output in dashboard tabs or dynamic dropdowns, but rarely the centerpiece of a financial model. They earn their place when you're building a board summary that needs to show the top 10 customers by contribution margin without a pivot table.
LAMBDA is the most powerful and the least used in practice. It lets you define custom reusable functions with named parameters - no VBA, no Apps Script. If you're writing the same nested formula in 12 places across a workbook, LAMBDA is the right fix. The learning curve is real, and most analysts hit it once and back away. Start with FILTER and come back to LAMBDA when you have a genuine repetition problem.
Cross-Tab Dynamic Formula Patterns for Real Models
Single-tab examples miss the point. A real model has Assumptions, P&L, Balance Sheet, Cash Flow, FCFF, Debt Schedule, Returns, and a Summary tab. Dynamic formulas across those tabs look like this.
Pulling period-specific revenue from the P&L into the FCFF tab:
=FILTER('P&L'!C3:N3, 'P&L'!C2:N2 >= Assumptions!$B$3)
This returns only the revenue columns where the period date meets your model start-date assumption. Change the assumption, the FCFF tab updates. No copy-paste, no named range maintenance.
Building a filtered view of the debt schedule on the Returns tab, restricted to a specific facility type with outstanding balance above a threshold:
=FILTER('Debt Schedule'!A:F,
('Debt Schedule'!D:D = Returns!$B$5) *
('Debt Schedule'!E:E > Assumptions!$B$4))
The * operator is AND logic inside FILTER. Two conditions, one formula, spills the matched rows into the Returns tab.
For a quarterly board pack where the Summary tab needs the 3 most recent revenue actuals from the P&L:
=TAKE(
FILTER('P&L'!C3:N3, 'P&L'!C2:N2 <> ""),
1, -3)
FILTER strips the empty future periods, TAKE with -3 pulls the last 3 values. That's a formula that would have been a 40-character OFFSET + INDEX/MATCH + LARGE stack in 2018.
The spill output from any of these can be referenced downstream with the # operator:
='Summary'!C5#
That reference expands automatically if the spill grows. Reference it in a SUM and you don't need to update the SUM range when you add more periods.
Where Dynamic Formulas Still Break Down
The spill behavior that makes dynamic formulas useful also creates new failure modes.
Blocked spill ranges. If anything occupies a cell in the spill path, you get #SPILL!. In a shared workbook where colleagues are adding data, this happens more than expected. The fix is clearing the blocked cells or moving the formula anchor. For a full breakdown of every cause, see our Excel #SPILL! Error guide.
Structured Table incompatibility. Excel Tables (Ctrl+T) don't accept dynamic array output as a spill target. A FILTER result can't spill into an existing Table, and you can't convert a spill range directly to a Table. For models that need structured references alongside dynamic output, keep raw data in Tables and calculated output in adjacent plain ranges.
Conditional formatting and data validation. Both still require fixed ranges as of build 2504. Data validation rules don't follow a spill - if your sensitivity table grows from 5 rows to 8, the validation only covers the original 5. You either fix the output size or reapply manually.
Iterative calculations. Dynamic arrays don't change Excel's circular reference behavior. An LBO model with interest feeding into debt paydown feeding back into interest still needs iterative calculation enabled explicitly (File → Options → Formulas → Enable iterative calculation, max iterations typically set to 100). The dynamic engine is orthogonal to this.
Build a Pipeline, Not a Dynamic Formula Chain
The instinct after learning dynamic arrays is to chain everything into one massive nested expression. Resist it.
A FILTER that feeds into SORT that feeds into XLOOKUP that feeds into LAMBDA is technically correct and practically undebuggable when it breaks at 11 PM before a board deck is due.
Better pattern: land each transformation as a separate step. The FILTER output spills in column B. A SORT on that spill goes in column F. The XLOOKUP against the sorted result goes in column J. Each step is visible, auditable, and replaceable independently.
For multi-scenario models - bear/base/bull with revenue assumptions of $31.2M / $38.7M / $44.1M, EBITDA margins of 19.5% / 23.8% / 27.2%, exit multiples of 10.5x / 12.8x / 14.2x - the dynamic formula layer handles the calculation, but the scenario switching still needs a clean Assumptions tab wired up correctly. Dynamic formulas don't replace good model architecture.
ModelMonkey handles the scaffolding - building cross-tab reference structures, wiring dynamic arrays to assumption cells, and surfacing formula errors before they cascade through linked tabs. It works in both Excel and Google Sheets, which matters when the bank syndicate DCF lives in Sheets and the board pack template is in Excel.