What is fungsi array formula in Google Sheets?

"Fungsi array formula" is the Indonesian term for ARRAYFORMULA, a built-in Google Sheets function that expands a single formula expression across an entire range. Instead of entering `=C2*D2` in 847 cells individually, you write `=ARRAYFORMULA(C2:C848*D2:D848)` once and the function populates all rows automatically. It supports most standard functions including IF, IFERROR, arithmetic operators, and text functions, and requires no add-ons as of July 2026.

Can ARRAYFORMULA reference other sheets?

Yes, and that's where it's most valuable in multi-tab models. Cross-tab references work exactly as expected: `=ARRAYFORMULA(IF('P&L'!B2:B500<>"", 'P&L'!C2:C500 * Assumptions!$B$3, ""))` pulls from your P&L tab and multiplies by a rate anchored in Assumptions. The `$B$3` absolute reference is critical - without it, the reference shifts row-by-row as the array expands and your numbers fall apart.

Why does ARRAYFORMULA break with VLOOKUP or SUMIFS?

VLOOKUP and SUMIFS don't natively support array expansion inside ARRAYFORMULA. For lookups across a range, use INDEX/MATCH instead: `=ARRAYFORMULA(IFERROR(INDEX('Data'!$C:$C, MATCH(A2:A500, 'Data'!$A:$A, 0)), ""))` handles array contexts correctly. For conditional sums across columns, SUMPRODUCT is more reliable than wrapping SUMIFS in ARRAYFORMULA - and it returns the aggregated result you actually want.

What's the difference between ARRAYFORMULA and LAMBDA?

ARRAYFORMULA expands an expression across a range dynamically at calculation time. LAMBDA, which Google rolled out to Sheets in March 2022 according to the Google Workspace blog, lets you define a named, reusable function with custom arguments. LAMBDA is better when you need identical logic in multiple places without repeating the full expression - define it once in Name Manager and call it anywhere. For straightforward row-by-row expansion in a single location, ARRAYFORMULA is faster to write and easier to audit.

Does ARRAYFORMULA slow down large models?

It can, and the bottleneck is usually recalculation rather than cell count. A model with 312 revenue rows, 280 projection periods, and 30+ ARRAYFORMULA expressions across 8 tabs can take 8-12 seconds to recalculate per edit. The most effective fix is moving complex conditional logic to LAMBDA (which caches intermediate steps) or converting volatile ARRAYFORMULA ranges to static helper columns once the model is finalized. Google Sheets' 10-million-cell ceiling is rarely the issue - calculation time usually hits first.

Fungsi Array Formula: ARRAYFORMULA in Google Sheets (2026)

Apa Itu Fungsi Array Formula / What It Actually Does

ARRAYFORMULA wraps any formula that operates on individual cells and makes it operate on ranges. The Google Sheets documentation defines it as a function that "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 translation: anything you'd drag down a column, you can write once.

=ARRAYFORMULA(IF('P&L'!B2:B500="Revenue", 'P&L'!C2:C500 * Assumptions!$B$3, ""))

That formula does what 499 individual IF statements would do, pulling from your P&L tab and multiplying by a growth rate locked in Assumptions. It recalculates the moment either tab changes.

Fungsi Array Formula in Multi-Tab Financial Models

This is where it earns its place. A standard three-statement model with separate P&L, Balance Sheet, and Cash Flow tabs has hundreds of cross-tab references. Without ARRAYFORMULA, each row is its own formula. With it, you write the logic once and the range handles the rest.

Here's a contribution margin calculation pulling from two tabs:

=ARRAYFORMULA(
  IF('Revenue'!A2:A500 <> "",
    ('Revenue'!C2:C500 - 'COGS'!C2:C500) / 'Revenue'!C2:C500,
    ""
  )
)

This calculates contribution margin by SKU across 500 rows, blanking out where there's no data. Change a COGS assumption and all 500 rows update simultaneously.

For a runway sensitivity - modeling headcount additions against a $4.2M cash balance - ARRAYFORMULA lets you write the burn rate logic once and apply it across 36 projection periods:

=ARRAYFORMULA(
  Assumptions!$B$2 - MMULT(
    IF(ROW(INDIRECT("A1:A"&36)) >= Hiring!$A$2:$A$10, 1, 0),
    Hiring!$C$2:$C$10
  )
)

ARRAYFORMULA vs. SUMPRODUCT vs. LAMBDA

Three functions do overlapping things. They're not interchangeable.

Function	Best For	Limitation
ARRAYFORMULA	Row-by-row expansion of IF, arithmetic, text functions	Can't call SUMIFS or VLOOKUP natively inside it
SUMPRODUCT	Conditional aggregation, weighted averages	Returns a single value, not a column
LAMBDA	Reusable named functions, recursive logic	Requires modern Sheets; rolled out March 2022 per the Google Workspace blog

SUMPRODUCT handles what ARRAYFORMULA can't: =SUMPRODUCT(('P&L'!B2:B500="Q3")*('P&L'!C2:C500)) works where =ARRAYFORMULA(SUMIF(...)) would break. Know which tool you're reaching for before you build.

Limits You'll Hit in Production with Fungsi Array Formula

Google Sheets caps at 10 million cells per spreadsheet, as documented in the official Google Sheets limits page. A 500-row ARRAYFORMULA returning 18 columns is 9,000 cells. Fine on its own. A model with 30 such formulas across 8 tabs starts pressing against that ceiling.

The more immediate bottleneck is recalculation time. ARRAYFORMULA recalculates every time any referenced cell changes. In a model with 312 revenue rows, 280 projection periods, and cross-tab links, that's 8-12 seconds per edit on a standard laptop. LAMBDA with named functions can cache intermediate results and is often faster for complex expressions.

According to Google's official documentation on Sheets array functions, wrapping ARRAYFORMULA around functions that already return arrays natively - like FILTER or SORT - is redundant and adds calculation overhead without benefit. Strip those out if you find them.

One edge case worth flagging: ARRAYFORMULA stops processing at the first blank row in your range unless you explicitly handle it. If your SKU list has gaps (common in raw ERP exports), wrap with IF(A2:A<>"", formula, "") or you'll get zeros scattered through your output.

When ModelMonkey Helps

The formulas above aren't hard to understand, but they're tedious to construct correctly the first time - especially when you're referencing 4 tabs with inconsistent column layouts. ModelMonkey can write and update ARRAYFORMULA expressions directly in your sheet. You describe the logic in plain language and it handles range construction, blank-row handling, and cross-tab references. For a quarterly board pack where 60 projection periods all need the same contribution margin logic, that's the difference between 10 minutes and 45.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.