ARRAYFORMULA in Google Sheets: Finance Guide

You’ll still need to validate the formula and its inputs. Blank row keys can hide populated inputs, text can break arithmetic, and existing values can block the output. The example below calculates margins while identifying these conditions.

Build the margin example

Create a sheet named Margin Example. Enter these headers:

Enter these illustrative inputs in A2:C5:

Leave A6:C6 empty. The month values should be actual dates, not text. If necessary, enter the first month as =DATE(2026,1,31) and use corresponding DATE formulas for the other periods.

Next, format B2:C6 as currency and D2:D6 as percentages. Clear D2:D6 before entering the formula because the array output needs those cells.

The formulas below use commas as argument separators. Some spreadsheet locales require semicolons instead.

Calculate every margin from D2

Enter this formula in D2 only:

=ARRAYFORMULA(
  IF(A2:A6="","",
    IF(
      ISNUMBER(B2:B6)*ISNUMBER(C2:C6)*(B2:B6>0),
      (B2:B6-C2:C6)/B2:B6,
      "CHECK INPUT"
    )
  )
)

It applies the same logic to rows 2 through 6:

1. IF(A2:A6="","",...) suppresses output when the Month cell is empty.
2. ISNUMBER confirms that Revenue and COGS are numeric.
3. B2:B6>0 requires positive revenue, preventing division by zero and flagging negative revenue.
4. (B2:B6-C2:C6)/B2:B6 calculates margin for valid rows.

The expected results are:

These are calculated expectations, not results from an executed workbook test.

In this example, zero and negative revenue are treated as exceptions. If returns, credits, or other negative-revenue transactions require separate treatment, add a rule based on your accounting policy instead of calculating them as ordinary sales margins.

For more conditional patterns, see ARRAYFORMULA with IF.

How ARRAYFORMULA and range references work

Google documents the syntax as:

=ARRAYFORMULA(array_formula)

A single-row margin formula would be:

=(B2-C2)/B2

Its multirow equivalent is:

=ARRAYFORMULA((B2:B6-C2:C6)/B2:B6)

The full example builds on that calculation by checking for blank cells, input types, and positive revenue.

All corresponding ranges should have the same boundaries. Here, A2:A6, B2:B6, and C2:C6 each contain 5 rows. Output starts in D2 and expands through D6.

Google Sheets also accepts open-ended references such as A2:A. They automatically include later rows, but they also cover every remaining row in the sheet. When a schedule has a known endpoint, a bounded range can be easier to inspect.

See Google’s official ARRAYFORMULA documentation for the function syntax.

Using ARRAYFORMULA with IF, SUM, and FILTER

Wrapping a function in ARRAYFORMULA doesn’t guarantee one result per row. The function’s existing range behavior still applies.

IF provides row-level controls

The example uses IF twice. The first instance suppresses unused rows, while the second returns either a numeric margin or CHECK INPUT.

As a result, malformed finance data isn’t silently converted to zero. Zero may be a valid business result, while N/A in a revenue column indicates a data-quality issue.

SUM normally returns one aggregate

SUM already accepts a range and returns a single result:

=SUM(B2:B6)

An ARRAYFORMULA wrapper isn’t necessary when the goal is one total. SUM can also ignore text such as the N/A value in B5, so reconcile or flag invalid source rows before relying on the total.

A practical model structure is:

• Use ARRAYFORMULA for row-level fields such as margin, variance, or account classification.
• Use SUM, SUMIFS, or a pivot table to aggregate the reviewed fields.

FILTER already returns multiple rows

To return only rows with numeric margins, keep F2:I6 clear and enter this formula in F2:

=FILTER(A2:D6,ISNUMBER(D2:D6))

The expected output contains the January and February rows. March and April are excluded because their margin results are text. Row 6 is also excluded because its result is an empty string.

FILTER already returns an array, so it doesn’t need an outer ARRAYFORMULA. Google documents its conditions and no-match behavior in the FILTER function reference.

For range-query alternatives, see ARRAYFORMULA and QUERY in Google Sheets.

Handle blank rows without hiding incomplete records

The gate in the example is:

IF(A2:A6="","",calculation)

This structure assumes every valid record has a Month value. If A6 is blank but someone enters an amount in B6 or C6, the margin remains visually blank. The formula hasn’t validated the entire row. It has only applied the selected row-key rule.

Review any rows where the required key is blank but other inputs are populated. If Month isn’t a reliable required field in your workbook, choose a different gate or add a separate exception check.

Whitespace also matters. A cell containing a space isn’t equal to "". If A6 contains a space while the amount cells remain empty, this formula should return CHECK INPUT rather than a blank. Remove the whitespace and make sure reporting periods contain valid dates.

A formula-generated "" looks blank, but it isn’t the same as an untouched cell. You can inspect the distinction outside the array output:

=D6=""

Expected result: TRUE.

=ISBLANK(D6)

Expected result: FALSE.

=COUNTA(D6)

Expected result: 1.

This distinction matters when downstream controls use ISBLANK or COUNTA.

Diagnose common ARRAYFORMULA problems

The output cannot expand

Every destination cell controlled by the array must be available.

To check this in a disposable copy:

1. Remove the formula from D2.
2. Enter a temporary value in D4.
3. Re-enter the formula in D2.

Expected behavior: the array cannot produce its complete output because doing so would overwrite D4. Confirm the obstructing cell, clear only the temporary value, and restore the formula.

Before clearing a production range, check whether it contains manual adjustments or other required data.

Referenced ranges have different sizes

Temporarily change B2:B6 to B2:B5 while leaving the other ranges at rows 2 through 6:

=ARRAYFORMULA(
  IF(A2:A6="","",
    IF(
      ISNUMBER(B2:B5)*ISNUMBER(C2:C6)*(B2:B5>0),
      (B2:B5-C2:C6)/B2:B5,
      "CHECK INPUT"
    )
  )
)

Expected behavior: Google Sheets reports a range-size error instead of returning a valid five-row result. Error wording can vary. Restore every range to rows 2 through 6 after the check.

Imported text breaks arithmetic

Text such as N/A can’t be used in the margin calculation. The ISNUMBER tests return CHECK INPUT before the formula attempts the arithmetic.

Data validation can guide manual entry. However, pasted or imported records still require formula-level and reconciliation controls. Google describes available rules in its data-validation documentation.

FILTER returns no rows

If no record satisfies its conditions, FILTER returns a no-match error rather than an empty table. Decide whether that error should remain visible as a control or be replaced with a specific message:

=IFNA(
  FILTER(A2:D6,ISNUMBER(D2:D6),A2:A6>=DATE(2027,1,1)),
  "No qualifying rows"
)

For this example, the expected output is No qualifying rows.

Also account for the output shape. The message occupies one cell, while a successful FILTER result occupies 4 columns. Reports that use the result should handle both states.

Choose ARRAYFORMULA or an alternative

Use ARRAYFORMULA when:

• Each populated row follows the same calculation.
• One formula should control a recurring reporting field.
• New records should receive consistent logic.
• A helper column feeds a later filter, pivot, or aggregation.
• Individual copied formulas could differ from the intended logic.

Copied formulas may be easier when individual rows intentionally use different logic or require manual overrides. If you need overrides alongside an array-controlled calculation, place them in a separate, clearly labeled column.

Some functions already accept whole ranges or return array results. SUM, FILTER, and QUERY often don’t need an outer wrapper. SUMIFS and COUNTIFS can appear inside ARRAYFORMULA, but the wrapper doesn’t automatically convert a scalar aggregation into a row-by-row calculation. For a dedicated example, see ARRAYFORMULA with COUNTIF.

Excel uses dynamic-array spill behavior rather than Google Sheets’ ARRAYFORMULA() function, so the equivalent Excel formula requires different syntax. Microsoft explains that behavior in its guide to dynamic-array formulas and spilled arrays.

Review checklist

Before relying on an ARRAYFORMULA in a finance schedule, confirm that:

• The row key is present for every valid record.
• All corresponding ranges start and end on the same rows.
• Numeric inputs are checked before arithmetic.
• Zero and negative revenue follow documented business rules.
• Destination cells are clear.
• Formula-generated empty strings behave as expected downstream.
• Rows hidden by a blank key do not contain overlooked amounts.
• Aggregated totals reconcile to the reviewed source data.
• Boundary rows and malformed inputs produce the intended result.

Adapt the formula to your workbook

ModelMonkey can inspect spreadsheet context and help create or adjust formulas in Google Sheets and Excel. For this example, you could ask it to adapt the margin formula to your column names, reporting range, and rules for zero or negative revenue.

Review the proposed formula against independently calculated rows and relevant exceptions. When the workflow presents a preview, inspect the proposed changes before proceeding.

Use ModelMonkey to adapt this ARRAYFORMULA to your sheet.