ARRAYFORMULA in Google Sheets: Finance Model Guide

For a three-statement model or a bank syndicate DCF with linked tabs, that reliability isn't a nice-to-have. It's the difference between a model that audits cleanly and one that fails on slide 3 of the board pack.

What ARRAYFORMULA Actually Does

A standard formula in B2 only evaluates B2. Copy it down to B2:B5001 and you've got 5,000 individual cells, each one a potential source of drift if someone edits row 847 without noticing.

ARRAYFORMULA wraps that single formula and broadcasts it across a range. The result is one formula, one point of truth, one cell to update.

// Standard approach - 5,000 cells, 5,000 failure points
B2: =IF(A2="Revenue", C2*Assumptions!$B$4, 0)
... copied to B5001

// ARRAYFORMULA - one cell, entire column
B2: =ARRAYFORMULA(IF(A2:A="Revenue", C2:C*Assumptions!$B$4, 0))

The open-ended range A2:A means the formula automatically covers any new rows appended below - critical when your data source is a live GL feed or a monthly export that grows with each close cycle.

Cross-Tab ARRAYFORMULA in Multi-Tab Google Sheets Models

Where ARRAYFORMULA earns its place in serious models is cross-tab lookups. You need contribution margin by SKU on the Returns Analysis tab, pulling from the P&L and the Assumptions tab simultaneously.

// Returns Analysis!D2 - gross profit by product line, entire column in one formula
=ARRAYFORMULA(
  SUMIFS('P&L'!E:E, 'P&L'!B:B, 'Returns Analysis'!A2:A, 'P&L'!C:C, ">=" & Assumptions!$B$3)
  - SUMIFS('P&L'!F:F, 'P&L'!B:B, 'Returns Analysis'!A2:A, 'P&L'!C:C, ">=" & Assumptions!$B$3)
)

This pulls revenue and COGS from the P&L tab, filters by product line and date floor from Assumptions, and returns the full column of gross profit figures in a single formula. If your P&L tab gains 12 new SKUs next quarter, the formula picks them up automatically.

For runway sensitivity on new hire pace, the same pattern applies:

// Headcount!G2 - cumulative burn at each staffing scenario
=ARRAYFORMULA(
  MMULT(
    Scenarios!$C$2:$E$13,                          // 12 months × 3 scenarios
    TRANSPOSE(Assumptions!$D$5:$D$7)               // loaded cost per role
  ) + SUMIF('Fixed Costs'!A:A, "Overhead", 'Fixed Costs'!C:C)
)

What Works and What Doesn't

Not every function responds to ARRAYFORMULA. This is the table that would have saved me two hours the first time I tried to wrap VLOOKUP in one.

As of July 2026, Google's Sheets documentation confirms that functions described as "returning an array" are already operating in array context and don't need (and won't accept) the wrapper.

ARRAYFORMULA Performance at Scale in Google Sheets

Google Sheets caps at 10 million cells. A 50,000-row GL detail tab with 20 columns of ARRAYFORMULA-driven classifications is well within that, but recalculation time is the real constraint.

From what I've seen in practice, a well-structured ARRAYFORMULA across 50,000 rows with 2-3 cross-tab references takes 3-8 seconds to recalculate on a full sheet recalc. The equivalent 50,000 individual formulas often takes 45-90 seconds - and occasionally crashes the tab entirely.

The performance wins come from a few structural choices:

Use open-ended column ranges sparingly. A2:A is convenient but forces Sheets to evaluate the entire column on every recalc. If your data set is bounded - say, 5,000 rows in a quarterly actuals tab - use A2:A5001 explicitly.

Avoid nesting ARRAYFORMULA inside ARRAYFORMULA. One outer wrapper is enough. Nesting is redundant and slows evaluation.

Keep the formula in one cell, not wrapped around a helper column. Helper columns that feed another ARRAYFORMULA are fine. But using ARRAYFORMULA to produce a column, then wrapping that in a second ARRAYFORMULA in another column, doubles recalculation cost without benefit.

The One Pattern That Trips Up Models

The $2.3M error in Q3 of one board pack I've seen came from this: a SUMIFS without ARRAYFORMULA in a summary tab, looking up values from a detail tab where someone had manually typed over 3 rows mid-column. The formula range stopped at the row above the manual entry. Nobody caught it until a bank covenant calculation came out wrong.

ARRAYFORMULA doesn't fully prevent manual overrides, but it does make the breach obvious. When a single-cell formula governs the entire column, a manual entry in that column produces a conflict error (#REF! or the overwrite silently breaks the formula pattern). That's visible. The silent drift of a 5,000-row copy-paste isn't.

When to Use ARRAYFORMULA vs. QUERY vs. Native Array Functions

This depends on what you're doing with the data.

ARRAYFORMULA is the right tool when you need to apply a calculation or classification formula to every row in a column - revenue categorization, loaded cost per headcount row, period-over-period variance flags.

QUERY (Google Sheets only) is better for aggregations and filters where you'd otherwise write a nested SUMIFS/COUNTIFS pile. It reads like SQL and handles grouping cleanly, but it's slower on large ranges and not compatible with ARRAYFORMULA.

Native array functions (FILTER, UNIQUE, SORT, SEQUENCE) are purpose-built and faster for their specific jobs. If you need to extract a unique list of cost centers from a 20,000-row GL, UNIQUE('GL Detail'!B2:B) beats anything you'd build with ARRAYFORMULA.

The practical split for a three-statement model: ARRAYFORMULA governs row-level classification and calculation columns, native functions handle summary lookups and unique lists, QUERY handles ad-hoc aggregation you'd otherwise do in a pivot.

For writing complex cross-tab ARRAYFORMULA patterns quickly, ModelMonkey drafts them in the sidebar based on a plain-language description of what you need - useful when you're three tabs deep in a 12-tab LBO and don't want to mentally parse column offsets to assemble the formula from scratch.