Google Sheets ARRAYFORMULA: Syntax, Examples, Fixes

The syntax is:

=ARRAYFORMULA(array_formula)

Google’s official ARRAYFORMULA documentation explains this behavior. It also notes that many array-capable formulas expand without an explicit wrapper and identifies Ctrl+Shift+Enter as a way to add the wrapper while editing a formula.

The example below calculates an entire line-total column while preserving blank displays and valid zero values.

Set up the Line Items sheet

Create a tab named Line Items with these headings:

Enter this sample data:

Quantity is a unit count. Unit Price and Line Total use the spreadsheet’s chosen currency. Leave D2:D clear so the array result has room to expand.

Extend one calculation down the column

A conventional formula in D2 would be:

=B2*C2

You’d then copy it down. With ARRAYFORMULA, enter this formula in D2 only:

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

It processes corresponding rows from columns B and C:

• B2:B supplies quantities.
• C2:C supplies unit prices.
• The first IF returns "" when Quantity is blank.
• The second IF returns "" when Unit Price is blank.
• Otherwise, Sheets multiplies Quantity by Unit Price.

The expected results are:

Before adapting the formula to a financial schedule, compare D2:D6 with these values. The valid zero in D4 must remain zero instead of being treated as missing.

For a numeric check, enter this formula in an unused cell:

=SUM(D2:D6)

The expected result is 36.5:

20 + 16.5 + 0 = 36.5

This total confirms the arithmetic for the sample data. It doesn’t show whether the source contains missing or duplicate line items.

Adjust the formula for your locale

Some spreadsheet locales use semicolons as argument separators. If the comma-separated formula produces a parse error, use:

=ARRAYFORMULA(IF(B2:B="";"";IF(C2:C="";"";B2:B*C2:C)))

These locales may also use a decimal comma. Enter the Paper unit price in the locale-appropriate form, such as 5,5 instead of 5.5. Depending on the sheet’s locale and number format, the expected result may appear as 16,5.

Confirm that new rows calculate automatically

With the formula still anchored in D2, enter:

D7 should display 16 because 2 × 8 = 16. There’s no need to copy the formula into the new row.

The open-ended ranges cover rows added below the sample data. If your schedule has a defined boundary, use matching bounded ranges instead:

=ARRAYFORMULA(IF(B2:B1000="","",IF(C2:C1000="","",B2:B1000*C2:C1000)))

Make sure every participating range begins and ends on the same rows.

Why the blank checks matter

Without a blank guard, the following formula evaluates every row:

=ARRAYFORMULA(B2:B*C2:C)

Arithmetic involving empty inputs can produce zeros where you intended to display a blank. The nested IF statements prevent that by returning the zero-length string "" when either required input is blank.

A comparison with ="" matches both genuinely empty cells and formulas that return "". However, it doesn’t match whitespace-only text because a cell containing a space is text, not an empty value. Google’s ISBLANK documentation distinguishes a genuinely empty cell from one containing an empty string. Its ISNONTEXT documentation indicates that "" is text.

If imported data may contain whitespace-only values, clean or test them explicitly instead of assuming this blank guard will catch them.

Keep in mind that a blank guard isn’t input validation. Text such as two in the Quantity column is neither blank nor numeric, so the formula continues to the multiplication step.

To check this behavior in a disposable copy:

1. Enter two in B7 and 6 in C7.
2. Review the affected result and its error details.
3. Confirm that the malformed quantity wasn’t silently converted to zero.
4. Restore B7 to 2 and C7 to 8.
5. Confirm that D7 returns to 16.

The exact error details can vary with the Google Sheets interface and locale.

Using ARRAYFORMULA with IF

You can place IF inside ARRAYFORMULA to apply the same condition to every row. The general structure is:

=ARRAYFORMULA(IF(condition_range,result_if_true,result_if_false))

In the line-item example, the conditions check for missing inputs. You can use the same structure to apply categories, signs, or other row-level rules, as long as those rules remain visible and independently checkable.

For more conditional examples, see ARRAYFORMULA IF in Google Sheets. For a broader function guide, see How to Use ARRAYFORMULA in Google Sheets.

FILTER and QUERY usually don’t need ARRAYFORMULA

Some Google Sheets functions already return expanding arrays, so wrapping them in ARRAYFORMULA generally adds no useful behavior.

For example, clear F2:I, then place this formula in F2:

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

Using the original sample data, the result is a 2-row by 4-column array containing the Pens and Paper rows. Samples are excluded because their line total is zero. Folder and Binder are excluded because ISNUMBER rejects the zero-length text returned in D5 and D6.

That numeric guard matters. Although D5 and D6 look blank, they contain "" returned by the formula. In Google Sheets, comparing this zero-length text with >0 can admit those rows unexpectedly. Adding ISNUMBER(D2:D6) ensures that only numeric results reach the positive-value test.

Google’s FILTER documentation states that FILTER returns rows or columns meeting its conditions. When no rows qualify, it returns #N/A rather than an empty table. If you’d prefer a one-cell explanation, use:

=IFNA(FILTER(A2:D6,ISNUMBER(D2:D6),D2:D6>0),"No matches")

Related functions serve different purposes:

For grouped financial analysis, see How to Use QUERY in Google Sheets for Finance.

Troubleshoot common ARRAYFORMULA errors

The result can’t expand

Every destination cell needed by the array must be available. If a manually entered value or another formula appears below D2, it can block the output.

To reproduce and resolve the issue safely:

1. Remove the formula from D2.
2. Enter any value in D3.
3. Restore the array formula in D2.
4. Confirm that Sheets reports an expansion problem at the anchor cell.
5. Clear D3.
6. Restore the formula if necessary.
7. Confirm that D2:D6 again shows 20, 16.5, 0, blank display, and blank display.

Before clearing a blocked destination, check whether it contains a required input or formula.

Corresponding ranges have different sizes

The following formula combines ranges with different endpoints:

=ARRAYFORMULA(B2:B500*C2:C1000)

Instead, use matching boundaries:

=ARRAYFORMULA(B2:B1000*C2:C1000)

Aligned boundaries make the intended row correspondence clear.

A zero is mistaken for a blank

The test B2:B="" distinguishes an empty value from numeric zero. As a result, the Samples row produces 0 rather than a blank display.

Avoid conditions such as IF(B2:B,...) when zero is valid. Instead, test explicitly for the missing value you intend to handle.

Text appears in a numeric column

ARRAYFORMULA doesn’t validate or repair source data. If Quantity or Unit Price contains nonnumeric text, the multiplication can produce a calculation error.

Correct the source value or add a numeric test that suits the model. Converting every error to zero can make an invalid line appear financially valid.

One result cell can’t be edited

Cells produced by an expanded array belong to the formula in the anchor cell. You can’t replace only D3 while leaving the rest of the array intact.

Instead, change the source values in columns B or C or revise the formula in D2. If one line needs a manual override, create a separate override input and incorporate it into the anchor formula.

What Google’s official documentation covers

Google’s official page presents the syntax, explains that array results can appear across multiple rows or columns, provides basic examples, and describes the Ctrl+Shift+Enter shortcut.

For a financial schedule, you must also decide:

• Which source fields define an unused row
• Whether zero is valid
• How malformed numeric inputs should be handled
• Whether open-ended or bounded ranges fit the schedule
• Which destination cells must remain clear
• Whether a native array function such as FILTER or QUERY already fits the task

By keeping repeated calculation logic in one anchor formula, ARRAYFORMULA can make a schedule easier to maintain. It doesn’t validate inputs, remove duplicates, reconcile totals, or establish that the source data is complete.

Review an ARRAYFORMULA with ModelMonkey

ModelMonkey is an AI assistant for spreadsheet work in Google Sheets and Microsoft Excel. In Google Sheets, it can inspect the active spreadsheet, sheet, and range, read formula errors, and help create or adjust formulas. When the workflow provides a preview, you can review proposed changes before proceeding.

For this example, you could request:

Inspect Line Items!A1:D. Check that the ARRAYFORMULA in D2 uses aligned ranges, explain any blocked output or calculation errors, and compare D2:D6 with the expected results 20, 16.5, 0, blank display, and blank display.

Always review formula suggestions against the workbook’s units, locale, and business rules. Install ModelMonkey to review this ARRAYFORMULA workflow in your spreadsheet.