ARRAYFORMULA makes a formula work on a whole range at once and spill its results into many cells, instead of copying the formula down row by row. One formula in the header row can fill an entire column and keep filling it as new rows are added.
Syntax
=ARRAYFORMULA(array_formula)
array_formula: A formula that uses ranges, arrays or functions that return arrays. Where you would normally writeA2, you writeA2:A.
The core idea
Without ARRAYFORMULA, a formula works on single cells. To multiply two columns you write =A2*B2 in row 2 and drag it down. With ARRAYFORMULA, you write it once:
=ARRAYFORMULA(A2:A * B2:B)
This multiplies each pair of cells and fills the results downward. If a new row is added to the data, the result grows with it. Put it in the header row of the output column to keep the column self-maintaining.
Examples
Calculate a whole column
=ARRAYFORMULA(B2:B * C2:C)
Combine text from two columns
=ARRAYFORMULA(A2:A & " " & B2:B)
Joins first and last names for every row.
Avoid filling blank rows
An open-ended range such as A2:A includes the empty cells at the bottom, so the formula would return 0 or errors for them. Guard it with IF:
=ARRAYFORMULA(IF(A2:A = "", "", B2:B * C2:C))
This is the standard pattern: if the key cell is empty, show nothing; otherwise calculate.
Add a header and the formula in one cell
=ARRAYFORMULA({"Total"; IF(A2:A = "", "", B2:B * C2:C)})
The curly braces build an array that stacks the header on top of the results. The semicolon stacks vertically; a comma places items side by side.
Conditional logic across a column
=ARRAYFORMULA(IF(C2:C > 100, "High", "Low"))
Lookups for every row
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, Prices!A:B, 2, FALSE)))
This looks up every key in one go. Functions like VLOOKUP, IF, SUMIF and REGEXEXTRACT accept ranges inside ARRAYFORMULA and process each value.
Text cleanup for a column
=ARRAYFORMULA(TRIM(PROPER(A2:A)))
Running totals
=ARRAYFORMULA(SUMIF(ROW(B2:B), "<=" & ROW(B2:B), B2:B))
For each row, this sums every value from the top down to that row.
Which functions need ARRAYFORMULA?
Many functions already return arrays and do not need it: FILTER, SORT, UNIQUE, SEQUENCE, TRANSPOSE, QUERY, SPLIT, REGEXEXTRACT on one cell, and XLOOKUP over a range. Functions that normally return one value per call need the wrapper to process a range: IF, VLOOKUP, LEN, TRIM, LEFT, arithmetic operators, and &.
A few functions never work with arrays inside ARRAYFORMULA, such as SUM, AVERAGE and MAX, because they collapse a range to a single number by design. For per-row sums use MMULT or BYROW.
Common errors
#REF!: Array result was not expanded because it would overwrite data in... Something is in the cells where the result needs to spill. Clear them, or move the formula.#VALUE!: Mismatched array sizes. The ranges combined in the formula have different heights.A2:A * B2:B50mixes an open range and a bounded one.- Results in blank rows (
0or#N/A). Add theIF(A2:A = "", "", ...)guard. - Sheet gets slow. Open-ended ranges (
A2:A) recalculate across thousands of empty rows. Bound the range (A2:A5000) in very large sheets. - Formula disappears when you drag it. Don't drag an
ARRAYFORMULA. The one formula already covers the whole column. Dragging puts other formulas in the way of the spill.
ARRAYFORMULA vs. dragging a formula down
Dragging is simple and lets you edit individual rows, but new rows added at the bottom don't get the formula and it is easy to leave gaps. ARRAYFORMULA guarantees consistency and handles new rows, but a single formula controls the whole column, so you can't override one cell without breaking the spill.
The shortcut: pressing Ctrl+Shift+Enter (or Cmd+Shift+Enter on a Mac) while editing a formula wraps it in ARRAYFORMULA for you.
Frequently asked questions
Does ARRAYFORMULA slow down my sheet? Only when it covers very large ranges. Bound them if performance suffers.
Can I use it with QUERY? QUERY is already array-native. Use ARRAYFORMULA around it only when feeding it computed arrays.
Why does my formula work in row 2 but not with ARRAYFORMULA? A function that expects a single value (like SUM) won't spread. Look for per-row alternatives such as BYROW or MMULT.
Related Functions
FILTER: Returns the rows that meet a condition.QUERY: SQL-like filtering and aggregating on a range.SEQUENCE: Generates an array of numbers.IFERROR: Replaces errors with a custom value.VLOOKUP: Vertical lookup, often used insideARRAYFORMULA.