Google Sheets ARRAYFORMULA Function: Example

The syntax is:

=ARRAYFORMULA(array_formula)

The Google Docs Editors Help page for ARRAYFORMULA explains that the function displays array results across multiple cells and allows compatible functions to work with arrays. Google also notes that many array formulas expand automatically, so an explicit ARRAYFORMULA wrapper isn’t always required.

In finance work, ARRAYFORMULA is useful when the same row-level calculation applies to a defined dataset and you want to maintain one anchor formula instead of multiple copied formulas.

Reproducible line-total example

Create a sheet named Orders with these illustrative inputs:

Apply the custom number format 0.00 to B2:C5 so numeric values display with 2 decimal places. The currency denomination is illustrative.

Leave C2:C5 empty. Enter this formula only in C2:

=ARRAYFORMULA(IF(A2:A5="","",IF(ISNUMBER(A2:A5)*ISNUMBER(B2:B5),A2:A5*B2:B5,"Check input")))

The expected output is:

The independent arithmetic checks are:

• 3 × 12.50 = 37.50
• 0 × 9.00 = 0.00

The formula in C2 is the anchor. Because it controls the results displayed throughout C2:C5, cells C3:C5 shouldn’t contain separate formulas or values.

How the ARRAYFORMULA works

The formula applies three expressions to corresponding rows in A2:A5 and B2:B5.

1. Check for a missing quantity

A2:A5=""

When the quantity is blank, the formula returns "", which looks blank in the sheet.

This behavior reflects an explicit policy choice: a row without a quantity doesn’t receive a line total, even when the unit price is populated. The policy is asymmetric. When the quantity is populated but the unit price is missing, the result is Check input.

However, a formula result of "" isn’t a genuinely empty cell. For example, ISBLANK(C4) returns FALSE because C4 contains an array result.

2. Require numeric inputs

ISNUMBER(A2:A5)*ISNUMBER(B2:B5)

Here, multiplication acts as an AND condition. Both the quantity and unit price must be numeric.

The check preserves valid zero values while identifying text or missing prices. It also prevents malformed inputs from being silently converted into plausible zero-value totals.

3. Calculate each line total

A2:A5*B2:B5

Sheets multiplies each quantity by the unit price in the same row, then expands the results through C2:C5.

Checks that confirm the formula’s behavior

Use a disposable copy for these checks, and restore the original inputs after each one.

• Change A2 from 3 to 4. With the 0.00 format, C2 should display 50.00 because 4 × 12.50 = 50.00.
• Change B2 to 0. C2 should display 0.00, confirming that numeric zero isn’t treated as blank.
• Clear A2 while leaving B2 populated. C2 should look blank under the missing-quantity policy.
• Restore A2, then clear B2. C2 should display Check input.
• Replace B2 with text such as twelve. C2 should display Check input.
• Replace either numeric input with a single space. Whitespace is neither empty nor numeric, so the result should be Check input.
• Restore the original inputs and compare every result in C2:C5 with the expected-output table.

Together, these checks confirm the formula’s defined behavior. They don’t validate the accuracy or completeness of the source data.

How to replace copied formulas

Suppose C2:C5 currently contains these individual formulas:

=A2*B2
=A3*B3
=A4*B4
=A5*B5

To replace them with one array formula:

1. Confirm that every row should follow the same calculation and exception rules.
2. Preserve any intentional overrides in C2:C5.
3. Clear C2:C5.
4. Enter the ARRAYFORMULA in C2.
5. Confirm the expected results through C5.
6. Check zero, blank, text, and whitespace inputs.

Keep the copied formulas if different rows intentionally contain different logic or manual adjustments.

Combining ARRAYFORMULA with IF and other functions

In the worked example, IF supplies the exception logic. For each row, it determines whether to display a calculated amount, a blank-looking result, or Check input.

You can apply the same pattern to calculations such as:

• Actual less budget
• Revenue less variable cost
• Quantity multiplied by unit cost
• Local-currency value multiplied by an exchange rate
• Margin calculations with an explicit zero-denominator rule

Text functions can also operate across ranges when the nested function accepts array input. Before using one across a larger schedule, test the inner expression on a small, bounded range. ARRAYFORMULA doesn’t guarantee that every nested function will return one result per input row.

For more conditional patterns, see ARRAYFORMULA with IF in Google Sheets.

ARRAYFORMULA versus FILTER and QUERY

These functions serve different purposes:

After completing the Orders example, leave E2:G5 empty and enter this formula in E2:

=FILTER(A2:C5,ISNUMBER(A2:A5),ISNUMBER(B2:B5))

Based on the stated inputs, the result contains source rows 2 and 3 because both inputs are numeric in those rows. It returns a 2-row by 3-column array.

Because FILTER already returns multiple cells, it doesn’t need an ARRAYFORMULA wrapper. Google documents this behavior on the FILTER help page.

If no rows match, FILTER returns a no-match error. The following version provides a blank-looking fallback:

=IFNA(FILTER(A2:C5,ISNUMBER(A2:A5),ISNUMBER(B2:B5)),"")

That fallback is one cell containing an empty string. It isn’t a zero-row result or a genuinely empty cell. The distinction can matter when another formula depends on the output’s dimensions.

Choose QUERY when you need grouping or aggregation instead of one result for each source row. See ARRAYFORMULA and QUERY in Google Sheets.

Troubleshooting ARRAYFORMULA

The result can’t expand

The cells needed for the complete output must be clear.

To reproduce an obstruction safely in a disposable copy:

1. Remove the formula from C2.
2. Enter a temporary value in C3.
3. Re-enter the array formula in C2.
4. Confirm that the occupied cell prevents expansion.
5. Clear C3 and re-enter the formula.
6. Confirm that C2:C5 populates correctly.

Before clearing existing content from the output column, confirm that it isn’t required.

The ranges have different dimensions

Ranges used for row-level calculations should cover matching rows. These ranges are consistent:

A2:A5
B2:B5

These aren’t:

A2:A5
B2:B10

When the output has an unexpected size or produces an error, inspect every range endpoint.

New rows aren’t calculated

The example stops at row 5. If you add a line item in row 6, extend every applicable range:

=ARRAYFORMULA(IF(A2:A6="","",IF(ISNUMBER(A2:A6)*ISNUMBER(B2:B6),A2:A6*B2:B6,"Check input")))

Open-ended references such as A2:A automatically include later rows, while bounded references make the calculation scope explicit. Choose between them based on how the workbook is maintained. Don’t assume either design is faster without measuring it in the relevant workbook.

Blank-looking results fail an ISBLANK check

A formula that returns "" displays nothing but still produces an empty string. If a downstream calculation must distinguish empty strings from unused cells, test the value or its length instead of relying only on ISBLANK.

The formula produces a parse error

The examples use commas as argument separators. Some spreadsheet locales require semicolons:

=ARRAYFORMULA(IF(A2:A5="";"";IF(ISNUMBER(A2:A5)*ISNUMBER(B2:B5);A2:A5*B2:B5;"Check input")))

Only the argument separators change. The ranges and arithmetic remain the same.

A nested function returns only one value

The nested function may return a scalar or might not process array inputs as expected. Isolate the inner expression and test it against a small range. Then consult that function’s Google Docs Editors Help page before applying it to a larger schedule.

When manual formulas are the better choice

Use individual formulas when:

• The schedule has only a few fixed rows.
• Different rows intentionally use different calculations.
• Reviewers need to trace each row independently.
• The output column contains manual overrides.
• A single anchor formula would make the model harder to audit.

Although ARRAYFORMULA reduces the need to maintain copied formulas, it also makes the entire result range dependent on one anchor cell. Base your choice on the calculation design, exception policy, and review process.

Review the formula in its spreadsheet context

ModelMonkey can inspect the active spreadsheet, sheet, and selected range in Google Sheets, then help review or change spreadsheet formulas.

For this example, select Orders!A1:C5 and ask ModelMonkey to review the formula in C2 for blank quantities, missing prices, text inputs, and range boundaries. Check any proposed change against the arithmetic and exception cases above. AI-generated spreadsheet work still requires review.

Install ModelMonkey to review an ARRAYFORMULA in Google Sheets.