sheets.org
Formulas & Functions

ARRAYFORMULA Explained: One Formula for a Whole Column

If you have ever added rows to a sheet and discovered your formulas did not come along, ARRAYFORMULA is the fix. It is the most distinctly "Google Sheets" function there is — one formula at the top of a column that computes every row, forever, including rows that do not exist yet.

The core idea

Wrap a normal formula in ARRAYFORMULA() and swap single cells for ranges:

Instead of =C2*D2 dragged down 1,000 rows:
=ARRAYFORMULA(C2:C*D2:D)

One cell now produces the entire column of results. New row added? It is calculated automatically. Nobody can forget to copy the formula down, and nobody can accidentally overwrite row 412 with a stale value.

Handling blank rows

Open ranges like C2:C include thousands of empty rows, which produces zeros all the way down. Guard with IF:

=ARRAYFORMULA(IF(C2:C="", "", C2:C*D2:D))

Read it as: where column C is blank, output blank; otherwise multiply. This guard pattern is so common it becomes muscle memory.

Patterns worth stealing

Full name from first and last

=ARRAYFORMULA(IF(A2:A="", "", A2:A&" "&B2:B))

Categorize with nested IF

=ARRAYFORMULA(IF(B2:B="","", IF(B2:B>=90,"A", IF(B2:B>=80,"B","C"))))

Running lookup for a whole column

=ARRAYFORMULA(IF(A2:A="","", VLOOKUP(A2:A, Products!A:C, 3, FALSE)))

What does not work inside ARRAYFORMULA

  • Aggregates like SUM, AVERAGE, and MAX collapse the whole array to one number instead of computing per row. Use SUMIF-style functions or matrix tricks instead.
  • Some functions simply refuse arrays (INDIRECT, OFFSET). If you get a single value where you expected a column, this is why.
  • A quirk to know: IFERROR works per-element, which makes it a handy wrapper for array lookups.

Header-row style

The tidiest pattern puts the label and the formula in the same cell in row 1, so data rows are purely data:

={"Total"; ARRAYFORMULA(IF(C2:C="","",C2:C*D2:D))}

The curly braces stack the header text on top of the computed column. Combined with the blank-row guard, this is the cleanest way to build sheets that other people add rows to — forms responses, shared trackers, imports — without anything breaking.