Does ARRAYFORMULA work with multi-tab references in Google Sheets?

Yes. You can reference ranges on other tabs inside ARRAYFORMULA exactly as you would in a standard formula. For example, `=ARRAYFORMULA(SUMIFS('P&L'!C:C, 'P&L'!B:B, 'SKU_Map'!A2:A))` runs a SUMIFS across two tabs for every row in the SKU_Map lookup range. The only hard constraint is that functions which already return arrays - like `QUERY()` - can't be nested inside ARRAYFORMULA cleanly.

Why does my ARRAYFORMULA return a column of zeros instead of blanks?

You're missing a blank-row guard. Wrap your formula with `IF(A2:A="","",your_formula)` to return empty strings for rows where the anchor column is blank. Without it, ARRAYFORMULA calculates a result for every row in the range, including empty rows, which produces zeroes or errors that contaminate downstream `SUMIFS` and pivot totals.

Does ARRAYFORMULA work in Excel?

No - ARRAYFORMULA is Google Sheets-only syntax. Excel 365 handles the same job with dynamic arrays: just enter a multi-cell formula normally and it spills automatically into adjacent cells. In Excel versions before 2019 or outside Microsoft 365 licenses, you'd use Ctrl+Shift+Enter to create a legacy array formula, which uses different syntax and carries more restrictions. If a model needs to run in both environments - common when sharing a DCF with a bank or PE counterparty - build the Excel version separately rather than trying to port ARRAYFORMULA syntax directly.

How does ARRAYFORMULA perform on large models with 1,000+ rows?

Reasonably well as of June 2026. A single ARRAYFORMULA with a nested IF across 1,000 rows typically recalculates in under 200ms. Performance degrades meaningfully when volatile functions like `TODAY()` or `NOW()` appear inside the array, or when a single file has 10+ ARRAYFORMULA-heavy columns all recalculating simultaneously. The fastest fix for a slow model is usually to identify any volatile functions inside ARRAYFORMULA expressions and replace them with static values on the Assumptions tab that update manually on refresh.

ARRAYFORMULA in Google Sheets: FP&A Guide (2026)

Q: Can I use AND() and OR() inside ARRAYFORMULA?

No. `AND()` and `OR()` collapse an array to a single TRUE/FALSE, which breaks the row-by-row evaluation ARRAYFORMULA needs. Replace `AND(A,B)` with `(A)*(B)` and `OR(A,B)` with `((A)+(B)>0)`. Both evaluate element-wise across the array. For example, `=ARRAYFORMULA(IF(('P&L'!C2:C>0)*('P&L'!D2:D<>""), "Valid", ""))` checks both conditions on every row independently.

For a multi-tab financial model, that matters. One formula is one thing to audit. Five hundred copied formulas are five hundred chances for an accidental edit to silently break a number.

What ARRAYFORMULA Actually Does

Standard Sheets functions like =A2*B2 return a single value. Wrap it in =ARRAYFORMULA(A2:A*B2:B) and it returns a result for every row where both columns have data. Google's official documentation describes it as: "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 practical difference: in a 36-month rolling model, you write the formula once instead of 36 times. When you change the logic in month 3, you change it once instead of risking a miss on month 17.

Where It Earns Its Place in a Financial Model

The most common use case is contribution margin calculations across a product table. Say you have SKU-level data on your Revenue tab - 200+ rows, unit count in column C, ASP in column D, COGS per unit in column E:

=ARRAYFORMULA(
  IF('Revenue'!A2:A="", "",
    ('Revenue'!C2:C * 'Revenue'!D2:D) - ('Revenue'!C2:C * 'Revenue'!E2:E)
  )
)

One cell. Covers every SKU. The IF(...="","",...) wrapper handles blank rows so you don't get trailing zeroes - a real model issue, not a toy one.

Another pattern: flagging which periods in your forecast tab exceed a threshold defined on your Assumptions tab.

=ARRAYFORMULA(
  IF('P&L'!C2:C >= Assumptions!$B$5, "Over target", "Under")
)

Assumptions!$B$5 might be your EBITDA margin floor - say 18.5%. Every period gets evaluated against the same locked assumption, and if you change the threshold, every flag updates instantly.

Where ARRAYFORMULA Beats Copy-Paste (And By How Much)

The practical argument isn't elegance - it's auditability and resilience.

A model with 400 copied formulas in a column has 400 independent cells. Any of them can be overwritten, formatted differently, or accidentally left behind when rows are inserted. In a board pack with 8 linked tabs, that's a credibility risk, not a minor annoyance.

ARRAYFORMULA concentrates that risk in one cell. Your formula auditor (or your own pre-close review) checks one formula instead of scrolling through a column hunting for the cell that got edited six months ago.

Speed is also real: as of June 2026, a 1,000-row ARRAYFORMULA with a nested IF typically recalculates in under 200ms on a standard Sheets file. The equivalent 1,000 copied formulas can push recalculation time past 2 seconds when the sheet has multiple dependent tabs - that's the difference between a model that feels responsive and one that lags on every assumption change.

Multi-Tab ARRAYFORMULA: The Patterns That Actually Work

Multi-tab references inside ARRAYFORMULA work exactly as you'd expect, with one exception covered below.

Pulling categorized revenue and applying a blended margin from assumptions:

=ARRAYFORMULA(
  SUMIFS('P&L'!$C:$C, 'P&L'!$B:$B, 'SKU_Map'!A2:A, 'P&L'!$A:$A, ">=" & Assumptions!$B$3)
  * Assumptions!$C$12
)

This gives you EBITDA contribution by SKU, filtered from a period start date on the Assumptions tab, multiplied by a blended margin factor. All 200 SKUs, one formula. SUMIFS is array-native, so ARRAYFORMULA extends it cleanly across a range of lookup values. This pattern shows up constantly in contribution-margin-by-SKU reports and channel attribution models.

For lookups inside ARRAYFORMULA, INDEX/MATCH is more predictable than VLOOKUP when you're referencing across tabs with non-contiguous columns:

=ARRAYFORMULA(
  IFERROR(
    INDEX('Assumptions'!$C:$C,
      MATCH('Revenue'!B2:B, 'Assumptions'!$B:$B, 0)
    ), 0
  )
)

This pulls a rate from the Assumptions tab for every category in the Revenue tab's column B. The IFERROR wrapper handles new categories that haven't been mapped yet, rather than letting #N/A propagate into your model.

Where ARRAYFORMULA Breaks

This is the section most guides skip because it's uncomfortable.

IF with multiple conditions gets messy. Nested IFs inside ARRAYFORMULA work, but AND() and OR() don't. They collapse an array to a single TRUE/FALSE, which breaks the row-by-row evaluation. The fix is to replace AND(A,B) with (A)*(B) and OR(A,B) with ((A)+(B)>0). Both evaluate element-wise. Not obvious, and it causes late-night debugging sessions.

Some functions won't array-ify. CONCATENATE doesn't work inside ARRAYFORMULA - use & operators instead. TODAY() and NOW() inside ARRAYFORMULA create volatility issues on large models that recalculate constantly. QUERY() is itself array-returning and doesn't nest inside ARRAYFORMULA cleanly.

Google Sheets doesn't translate to Excel. ARRAYFORMULA is Google Sheets-only. In Excel 365, dynamic arrays handle the same job natively - you enter the formula normally and it spills. In older Excel (pre-2019 or non-365 licenses), you need Ctrl+Shift+Enter for legacy array formulas, and the syntax differs enough that a model built around ARRAYFORMULA won't transfer cleanly. If your deliverable ends up in a bank syndicate DCF package or a PE firm's own sensitivity model, this matters.

One formula blocks the range. An ARRAYFORMULA in B2 that outputs to B2:B500 means nothing else can live in B3:B500. Insert a helper column mid-range and you get a #REF! error. Plan your column layout before committing to ARRAYFORMULA-heavy architecture.

The Pattern Most Analysts Miss: Open-Ended Ranges

The most practical version of ARRAYFORMULA uses open-ended column ranges. Instead of C2:C500, write C2:C. The formula automatically covers new rows as data is added.

=ARRAYFORMULA(
  IF('Revenue'!A2:A="", "",
    IFERROR(
      INDEX('Rates'!$B:$B, MATCH('Revenue'!C2:C, 'Rates'!$A:$A, 0)),
      "Unmapped"
    )
  )
)

For a live model that gets new transaction rows appended daily or weekly - runway sensitivity on new hire pace, say, or weekly bookings by channel - open-ended ranges mean you never extend formulas. The data grows; the formula keeps up. There's a full breakdown of how Sheets handles open-ended ranges at [/blog/google-sheets-open-ended-range-b5-b].

The IF(...="","",...) guard is non-negotiable here. Without it, ARRAYFORMULA calculates a result for every row in the range, including empty ones, which produces trailing zeroes that contaminate downstream SUMIFS totals.

ARRAYFORMULA vs. Helper Columns: When to Use Which

Helper columns have a bad reputation they don't entirely deserve. If the logic requires multi-step conditional accumulation, a running balance, or period-over-period comparisons, breaking it into 2-3 helper columns is usually faster to build, easier to audit, and less fragile under edits.

The rule worth keeping: if you can express the logic clearly in one ARRAYFORMULA without burying it in nested parentheses, do it. If the formula runs past 120 characters and needs a comment to explain the array logic, split it into helper columns. A model you can open in six months and follow beats an elegant one-liner that takes 20 minutes to debug under quarter-close pressure.

ModelMonkey's formula assistant is useful for exactly this trade-off - describe what you want in plain language and it'll generate both the ARRAYFORMULA version and the helper-column version, which makes it easier to see which is cleaner for your specific model structure.