ARRAYFORMULA Function in Google Sheets: How to Use It

The basic syntax is:

=ARRAYFORMULA(array_formula)

However, ARRAYFORMULA doesn’t make every function array-aware. Some functions already produce arrays, while others still return a single result. To use it correctly, you need to understand which formulas expand and how to handle blank rows, invalid inputs, and blocked output cells.

Google describes ARRAYFORMULA as a way to display array results across multiple cells and use non-array functions with arrays. It also notes that many array formulas expand automatically without an explicit wrapper. See the official ARRAYFORMULA documentation.

Build a guarded line-total column

This example calculates order-line totals while distinguishing valid zero quantities from missing or malformed inputs.

Create a tab named Orders with these headers:

Enter this sample data:

Format columns C and D as USD with 2 decimal places. Then enter this single formula in Orders!D2:

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

The expected results are:

Here, the outer IF suppresses output for unused rows. The ISNUMBER checks prevent a missing or text quantity from being treated as a valid calculation.

To verify the numeric result, enter Control total in F1 and this formula in F2:

=SUM(D2:D5)

F2 should equal 7.50. Also confirm that D4 contains Check input. Because that value is text, SUM ignores it.

Keep in mind that this guard checks data type, not business validity. A negative quantity remains numeric and produces a negative line total unless you add a separate rule prohibiting negative quantities.

How range expansion works

In the example, B2:B*C2:C tells Google Sheets to multiply corresponding rows:

• B2 by C2
• B3 by C3
• B4 by C4
• Continuing down the columns

Although the formula exists only in D2, its results occupy D2:D. Those destination cells must remain available for the array output.

Because the source ranges are open-ended, adding Pencil, 4, and 1.25 to A6:C6 should make D6 return 5.00 without copying the formula. The formula hasn’t moved or been duplicated. Its existing input ranges now include row 6.

Open-ended ranges are convenient for recurring imports. For a fixed schedule, however, a bounded range such as A2:A500 may make the intended model boundary easier to audit.

Apply conditional logic with ARRAYFORMULA

IF is commonly combined with ARRAYFORMULA. A typical pattern looks like this:

=ARRAYFORMULA(IF(active_row_test,calculation_or_result,blank_or_alternative))

In the worked example:

• A2:A="" identifies unused rows.
• ISNUMBER(B2:B)*ISNUMBER(C2:C) requires both numeric checks to be true.
• B2:B*C2:C performs the row-level calculation.
• "Check input" identifies active rows with invalid inputs.

Be careful to keep missing values separate from zero. A test such as IF(B2:B,...) treats zero as false, even when zero is a legitimate actual or forecast assumption. An explicit blank or type check avoids that problem.

For a more focused explanation of conditional arrays, see ARRAYFORMULA with IF in Google Sheets.

Apply text operations across a column

Text functions can follow the same range-oriented pattern. For example, enter this formula in Orders!E2 to standardize product names:

=ARRAYFORMULA(IF(LEN(TRIM(A2:A))=0,"",UPPER(TRIM(A2:A))))

With the original inputs, E2:E4 returns:

PEN
PAPER
STAPLER

In this formula, TRIM removes leading, trailing, and repeated spaces, while UPPER standardizes case. The LEN(TRIM(...))=0 test also treats a cell containing only spaces as empty.

That behavior differs from the line-total formula’s A2:A="" test. If A6 contains only spaces while B6 and C6 contain numbers, the original line-total formula treats row 6 as active. If whitespace-only product cells are possible, replace its outer condition with:

LEN(TRIM(A2:A))=0

Which functions work with ARRAYFORMULA?

A function’s behavior depends on whether its inner operation accepts and preserves arrays.

In other words, ARRAYFORMULA isn’t a universal wrapper. If the inner function reduces a range to one scalar result, wrapping it may still produce only one result.

For criteria-based monthly aggregation, see COUNTIF with ARRAYFORMULA for month ranges. For grouped summaries, QUERY in Google Sheets for finance covers a more suitable approach.

When FILTER doesn’t need ARRAYFORMULA

FILTER already returns every matching row, so an ARRAYFORMULA wrapper normally adds no value.

To check this without interfering with the worked example, confirm that Orders!H2:K is empty. Enter this formula in Orders!H2:

=FILTER(A2:D5,A2:A5="Pen")

The result spills across H2:K2 and returns one four-column row:

Next, test the no-match case independently. Clear the previous formula and any spilled results from H2:K, confirm the range is empty, and then enter this formula in H2:

=FILTER(A2:D5,A2:A5="Binder")

Because no row matches, FILTER returns #N/A. To display a message instead, clear H2:K again and enter this independent formula in H2:

=IFNA(FILTER(A2:D5,A2:A5="Binder"),"No matches")

The expected result is the single-cell text No matches. Google documents both the array output and the no-match behavior in its FILTER function documentation.

Validate the important boundary cases

Run these as independent checks. Before each one, restore the original A2:C6 baseline shown above: the Pen, Paper, and Stapler rows in A2:C4, followed by blank rows 5 and 6.

Testing a blocked expansion requires a separate reset because D6 normally belongs to the array output:

1. Restore the original A2:C6 baseline.
2. Clear the array formula from D2.
3. Enter any fixed value, such as Blocked, in D6.
4. Re-enter the full ARRAYFORMULA in D2.
5. Confirm that D2 reports a #REF! array-expansion error.
6. Remove the fixed value from D6. The array should then expand normally.

An expansion error doesn’t usually mean the calculation is invalid. Start by checking the intended output range for values, spaces, or other formulas. After confirming that they aren’t required inputs, clear only the obstructing cells.

These examples use commas as argument separators. Some spreadsheet locales require semicolons, so the first formula would begin:

=ARRAYFORMULA(IF(A2:A="";"";IF(...)))

The logic is unchanged. Only the separator convention differs.

Common ARRAYFORMULA errors

The formula returns only one result

First, check whether the formula references one cell rather than a range. For example, B2*C2 describes one calculation, while B2:B*C2:C describes corresponding calculations across two columns.

The inner function may also reduce its input to a scalar before ARRAYFORMULA can expand it.

The array can’t expand

One or more destination cells already contain data. Inspect the output column below and beside the formula, depending on the expected array shape.

Merged cells can also prevent expansion, so keep spill ranges unmerged.

Blank rows show zeros or unwanted text

Add an outer row-activity test. For example:

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

If cells containing spaces should count as blank, use LEN(TRIM(A2:A))=0 instead.

Errors disappear behind IFERROR

IFERROR can make a report cleaner, but it can also conceal broken references, failed lookups, and invalid source data. During development, keep errors visible or return a specific control label such as "Check input" or "Unmapped".

Add broad error handling only after deciding which failures are acceptable.

Source and output dimensions don’t agree

Operations between arrays need compatible shapes. If one input contains 12 rows and another contains 11, a row-by-row calculation can fail or produce an unintended result. When a schedule must have a defined start and end, use bounded ranges.

Alternatives to ARRAYFORMULA

Choose the method that matches the calculation:

• Copied formulas: Appropriate when each row requires manual exceptions or the inner function doesn’t expand correctly.
• FILTER: Best for returning only rows that meet stated conditions.
• QUERY: Useful for selecting, grouping, and aggregating table data. See ARRAYFORMULA and QUERY in Google Sheets for how their roles differ.
• SORT and UNIQUE: These already return expanding arrays and generally don’t need an ARRAYFORMULA wrapper.
• Pivot tables: Useful when users need an interactive grouped summary rather than a formula-driven output column.

ARRAYFORMULA works best for a consistent row-level rule. It’s less suitable when rows need different logic, manual overrides, or criteria-based aggregation that the inner function doesn’t vectorize.

Check an ARRAYFORMULA with ModelMonkey

ModelMonkey can help with spreadsheet formulas in Google Sheets and inspect the active spreadsheet, sheet, and range context. Its spreadsheet tools can also read ranges and formula errors, which can help when an array is blocked or returns unexpected results.

For the example above, you could ask ModelMonkey to inspect Orders!A1:D6, check the formula in D2, and explain why a specific row returns blank, Check input, or an expansion error. Review formula suggestions against your model’s business rules, especially those covering negative quantities and incomplete rows.

Use ModelMonkey to help inspect and check an ARRAYFORMULA.