For a 36-month DCF or a 500-row transaction ledger, that distinction matters. One cell means one thing to audit, one thing to break, and one thing to update when your CFO asks for a methodology change at 4pm on a Thursday.
What ARRAYFORMULA Actually Does in a Model
A regular formula like ='P&L'!C5/'P&L'!B5 calculates one row's gross margin. ARRAYFORMULA wraps that logic and evaluates it across a range:
=ARRAYFORMULA(IF('P&L'!B2:B500<>"", 'P&L'!C2:C500/'P&L'!B2:B500, ""))
According to Google's official Sheets documentation (as of June 2026), ARRAYFORMULA "enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays." The IF wrapper is standard practice here - without it, any blank denominator row throws #DIV/0! across your entire output column.
The result fills down automatically. Add row 501, the formula picks it up. Delete row 312, nothing breaks elsewhere. That's the core value proposition: one formula managing N rows.
For more on how ARRAYFORMULA handles edge cases the help docs skip, see Google Sheets ARRAYFORMULA: Beyond the Help Docs.
The 4 Places It Earns Its Keep
Calculated columns in transaction or deal data. The clearest use case is any model where you're computing a metric for every row of raw data. Revenue per unit, gross margin per SKU, days elapsed since contract start. A contribution margin analysis across 200 SKUs generating $18.3M in aggregate revenue doesn't need 200 individually-dragged formulas:
=ARRAYFORMULA(IF(Data!A2:A201<>"",
(Data!C2:C201 - Data!D2:D201) / Data!C2:C201,
""))
One formula on row 2 of your Returns Analysis tab. The product list grows to 250 next quarter: widen the range once, done.
Cross-tab lookups at scale. INDEX/MATCH or VLOOKUP applied to 36 months of projections, row by row, is where people reach for ARRAYFORMULA most. A common pattern in a quarterly board pack:
=ARRAYFORMULA(IF(Assumptions!$A$3:$A$38<>"",
IFERROR(VLOOKUP(Assumptions!$A$3:$A$38,
'Revenue Detail'!$A:$D, 4, 0), 0),
""))
This maps 36 period labels from your Assumptions tab to actuals in Revenue Detail without 36 individual VLOOKUP cells that someone will eventually overwrite with a hardcoded number.
Period and date labeling. ARRAYFORMULA pairs naturally with EDATE to generate fiscal period headers automatically:
=ARRAYFORMULA(TEXT(EDATE(Assumptions!$B$1, ROW(A1:A36)-1), "MMM-YY"))
Drop this in the header row of your projections tab. Your period labels generate from the model start date in B1. Change the start date, all 36 labels update. For a bank syndicate DCF that gets refreshed with updated assumptions every time the rate desk sends new comps, this matters.
SUMIFS returning one total per row. ARRAYFORMULA extends SUMIFS to produce a result per row rather than one aggregate. For a runway sensitivity on new hire pace across departments:
=ARRAYFORMULA(SUMIFS('Headcount'!$D:$D,
'Headcount'!$B:$B, Scenarios!$A2:$A10,
'Headcount'!$C:$C, ">="&Assumptions!$B$3))
This calculates total comp for each of 10 departments in one formula. The ARRAYFORMULA COUNTIF pattern follows the same logic.
ARRAYFORMULA vs. Fill-Down: The Real Tradeoff
| ARRAYFORMULA | Fill-Down | |
|---|---|---|
| Rows added to data | Auto-covers if range is open-ended | Requires extension |
| Auditability | 1 cell to check | N cells to check |
| Risk of partial overwrite | One formula to break | Any cell can be hardcoded |
| Works with volatile functions | Yes, but recalculates everything | Calculates individually |
| Formula visible in each row | No (downstream cells appear blank) | Yes |
| Structured table reference support | Partial | Full |
The case for fill-down isn't zero. If your model gets audited by a team that expects a formula in C37 - not an empty cell that traces back to C2 - ARRAYFORMULA is harder to explain. That's a real workflow constraint, not a technical limitation.
Where ARRAYFORMULA Breaks
The documentation doesn't call this out clearly enough. ARRAYFORMULA doesn't work with functions that already return arrays natively: QUERY, SORT, UNIQUE, and FILTER handle their own array output. Nesting them inside ARRAYFORMULA is redundant and typically errors out.
Performance degrades on large open-ended ranges. An ARRAYFORMULA over A2:A (the full column) recalculates against 1,000+ rows on every sheet edit. On a model with 15 linked tabs, this compounds. Google Sheets caps at 10 million cells per spreadsheet, and unbounded ARRAYFORMULA ranges count against that limit on each evaluation. Bounding ranges explicitly - A2:A500 instead of A2:A - matters once you're working with models over 50,000 cells.
ARRAYFORMULA also can't reference its own output column. If your ARRAYFORMULA in column C references column C, you get a circular error with an unhelpful message.
The Non-Obvious Edge Case
Here's one the Google documentation misses: ARRAYFORMULA suppresses the display of results in rows below the first, but those rows aren't technically empty. Any tool or formula checking for blank rows using ISBLANK() will return FALSE on cells that are part of an ARRAYFORMULA output range. This has broken more than one model that uses blank-row detection to identify the bottom of a dataset. Use ="" comparisons instead of ISBLANK() when working with ARRAYFORMULA output ranges.
ModelMonkey's AI assistant handles ARRAYFORMULA-heavy models correctly - it reads computed values across tabs rather than stopping at the single source cell. If you're building multi-tab models where ARRAYFORMULA is driving most of your calculated columns, it's worth knowing your AI tooling can reason about the actual output. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.