Google Sheets ARRAYFORMULA Fill a Column
By Szabó Gergő · Updated
One formula can return a whole column. Leave the cells below empty so the array can expand.
Syntax and arguments
=ARRAYFORMULA(IF(A2:A="", "", A2:A*B2:B))- array_formula
- An expression that uses open ranges.
Fill a column examples
01
Line totals
Qty in B, price in C.
=ARRAYFORMULA(IF(B2:B="", "", B2:B*C2:C))Blank qty rows stay blank instead of showing 0.
02
Join names
First A, last B.
=ARRAYFORMULA(IF(A2:A="", "", A2:A&" "&B2:B))New rows pick up the formula automatically.
03
Status label
Amount in D.
=ARRAYFORMULA(IF(D2:D="", "", IF(D2:D>=1000,"Large","Small")))Nested IF works inside ARRAYFORMULA.
Common mistakes
Values sitting in the output column
Clear every cell below the formula. Leftovers cause #REF!.
Omitting the blank-row IF
Open ranges calculate thousands of extra zeros without a guard.
Wrapping QUERY in ARRAYFORMULA
QUERY already returns an array. ARRAYFORMULA is for ordinary operators.
Fill a column FAQ
Do FILTER and UNIQUE need it?
Usually no. They already spill.
Why did it stop at row 1000?
Limit the range, such as A2:A5000, if Sheets caps the expansion.
Can I put it in row 1?
Yes. Do not put other values beneath it.