How to Use ARRAYFORMULA in Google Sheets

Normally you write a formula in one cell and copy it down. ARRAYFORMULA does the whole column at once: one formula in the top cell fills every row, and new rows are calculated automatically. It's especially useful with Google Forms responses, where new rows keep arriving.

The basic idea

Instead of =B2*C2 copied down, write this once in D2:

=ARRAYFORMULA(B2:B*C2:C)

Every single cell reference becomes a range, and Sheets multiplies row by row.

ARRAYFORMULA in Google Sheets multiplying price by quantity for every row from a single formula — One formula in D2 fills the whole Total column. This version also skips empty rows (see the next section).

Stop blank rows showing 0

With an open-ended range like B2:B, empty rows at the bottom show 0. Add an IF to skip them:

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

This leaves a row empty until something is typed in column A.

More examples

Task	Formula
Join first and last names	`=ARRAYFORMULA(IF(A2:A="", "", A2:A&" "&B2:B))`
Pass / Fail for every score	`=ARRAYFORMULA(IF(B2:B="", "", IF(B2:B>=50, "Pass", "Fail")))`
Add 7 days to every date	`=ARRAYFORMULA(IF(A2:A="", "", A2:A+7))`

Shortcut

Type a normal formula with ranges, such as =B2:B*C2:C, and press Ctrl + Shift + Enter (⌘ + Shift + Enter on Mac). Sheets wraps it in ARRAYFORMULA for you.

Things that don't work inside ARRAYFORMULA

SUM, AVERAGE, MAX and similar functions return one total for the whole range, not one per row. For per-row totals, add the columns directly: B2:B+C2:C+D2:D.
AND and OR also collapse to one result. Use * for "and" and + for "or" instead, e.g. IF((B2:B>50)*(C2:C="Paid"), …).

Frequently asked questions

Why do I get #REF! with "Array result was not expanded"?

Something is already typed in the column below the formula. Clear those cells. See how to fix #REF!.

Do FILTER and UNIQUE need ARRAYFORMULA?

No, they already return several rows on their own. See FILTER and UNIQUE.