ARRAYFORMULA in Google Sheets: Fill a Column
By Szabó Gergő · Updated
ARRAYFORMULA lets one formula return a column or grid of results. Use it instead of dragging a formula down, and leave the cells below empty so the array can expand.
Syntax and arguments
=ARRAYFORMULA(array_formula)- array_formula
- An expression that uses ranges, such as IF(A2:A="", "", A2:A*B2:B).
ARRAYFORMULA examples
Qty in B2:B, price in C2:C.
=ARRAYFORMULA(IF(B2:B="", "", B2:B*C2:C))Blank qty rows stay blank instead of showing 0.
A2:A first, B2:B last.
=ARRAYFORMULA(IF(A2:A="", "", A2:A&" "&B2:B))One formula fills the name column as rows are added.
Due dates in D2:D.
=ARRAYFORMULA(IF(D2:D="", "", TODAY()-D2:D))Aging fills automatically for new due dates.
Common mistakes
Values sitting in the output column
Clear every cell below the ARRAYFORMULA. Leftover values cause #REF!.
Omitting the blank-row IF
Open-ended ranges like A2:A will calculate thousands of extra zeros without a guard.
Using Excel-only dynamic arrays
Sheets often needs ARRAYFORMULA around expressions that Excel would spill by itself.
ARRAYFORMULA FAQ
Do FILTER and QUERY need ARRAYFORMULA?
Usually no. They already return arrays. ARRAYFORMULA is for ordinary operators and IF over ranges.
Why did the formula stop at row 1000?
Sheets limits how far some arrays expand. Limit the range (A2:A5000) if you hit a cap.
Can I put ARRAYFORMULA in a header row?
Put it in row 1 or 2 of the output column and do not put other formulas beneath it.