Fungsi ARRAYFORMULA in Google Sheets: FP&A Guide

Google Sheets built ARRAYFORMULA into the core function library before Excel had native dynamic arrays. As of June 2026, it remains the standard mechanism for self-maintaining column logic in Sheets-based financial models.

What Fungsi ARRAYFORMULA Actually Does

The syntax is =ARRAYFORMULA(expression), where the expression contains a range that Sheets evaluates row by row and outputs as an array. Instead of entering =C2*D2 in E2 and copying to E803, you enter =ARRAYFORMULA(C2:C*D2:D) in E2. Every new row added to C and D populates E automatically. No copy, no fill-down, no gap risk.

For multi-tab models, the same logic reaches across sheet references:

=ARRAYFORMULA(
  'P&L'!C2:C * Assumptions!$B$4
)

This multiplies every revenue figure in the P&L tab by a single gross margin assumption from the Assumptions tab. Update the assumption once and every row recalculates. That's the behavior a board pack model needs when Assumptions drives everything downstream.

ARRAYFORMULA with IF: The Version That Actually Works

The most common mistake is trying to nest IFS() inside ARRAYFORMULA. It doesn't propagate cleanly - Google Sheets as of June 2026 doesn't support IFS() in an array context reliably. Nested IF() works correctly:

=ARRAYFORMULA(IF(A2:A="", "", B2:B * C2:C))

The IF receives the full array from A2:A and evaluates it row by row. Use SWITCH() for multi-branch logic where you'd otherwise reach for IFS.

Fungsi ARRAYFORMULA in Multi-Tab FP&A Models

Where ARRAYFORMULA earns its place in production models is cross-tab aggregations that previously required either helper columns or repetitive SUMIFS stacks.

Consider a revenue recognition model with separate tabs for Bookings, Invoiced, and Collected. A single ARRAYFORMULA in the Summary tab can pull recognized revenue by dimension without touching the source tabs:

=ARRAYFORMULA(
  SUMIFS(
    'Invoiced'!D:D,
    'Invoiced'!A:A, ">=" & Assumptions!$B$3,
    'Invoiced'!A:A, "<" & Assumptions!$B$4,
    'Invoiced'!C:C, Summary!A2:A
  )
)

That formula runs one SUMIFS per row in Summary!A2:A - one per product line, one per region, whatever dimension lives in column A. Without ARRAYFORMULA you're copying the SUMIFS formula down manually or building a pivot that breaks when someone filters the source data.

The same pattern handles contribution margin by SKU, headcount cost by department, or cash collections by cohort. The formula structure is identical; only the source tab and dimension column change.

Performance Limits (What to Watch in 2026)

Google Sheets caps at 10 million cells per spreadsheet. ARRAYFORMULA expands formulas across entire columns - C2:C in a 50-tab model can compound fast if you're not deliberate about range bounds.

A well-scoped ARRAYFORMULA across 5,000 rows recalculates 2-3x faster than 5,000 individual cell formulas because Sheets processes the array in a single evaluation pass. The problem starts when you apply unbounded ranges (C:C instead of C2:C5000) to volatile functions like TODAY() or NOW() - that forces a full recalculation on every sheet change.

The rule: bound your ranges explicitly in any ARRAYFORMULA that touches a volatile function. Use C2:C5000 rather than C2:C when data has a known maximum size.

For models with 5,000+ rows and dozens of cross-tab ARRAYFORMULAs, formula complexity compounds recalculation time meaningfully. ModelMonkey's AI formula assistant can audit which formulas are dragging a model down and suggest bounded alternatives - useful when a board pack that opened in 4 seconds now takes 40.

When Not to Use Fungsi ARRAYFORMULA

ARRAYFORMULA isn't always the right tool. Three cases where it creates more problems than it solves:

When users need to override individual cells. ARRAYFORMULA locks the output range. If an analyst needs to manually enter an adjustment in row 47, they can't - the formula owns that cell. In models where cells are intentionally editable (budget overrides, manual adjustments), drag-down is safer.

When source data is sparse and irregular. ARRAYFORMULA on a column with many blank rows still evaluates every row. A SUMIFS ARRAYFORMULA across 800 rows where 600 are blank runs 800 evaluations and outputs 600 empty strings. Downstream SUMIFS can count empty strings differently than true blanks, which creates reconciliation headaches.

When collaborators aren't familiar with it. A formula sitting alone in B2 that populates B2:B803 confuses analysts who haven't seen ARRAYFORMULA before. "Why can't I edit B500?" kills 20 minutes in a model review. Document it explicitly or use a named range to signal the behavior.

ARRAYFORMULA vs. Drag-Down: When Each Makes Sense

For automated pipelines where rows append on a schedule - daily journal entries, a live import from accounting software - ARRAYFORMULA wins cleanly. For models where cells need manual overrides, drag-down is the right call.