ARRAYFORMULA Function in Google Sheets
Applies a formula to an entire range at once, returning an array of results that 'spills' into the surrounding cells. Lets you write a single formula in one cell instead of dragging it down 10,000 rows.
Syntax
ARRAYFORMULA(array_formula)Returns: An array of values spilled into adjacent cells from the formula cell.
Excel equivalent: No direct equivalent in Excel — modern Excel uses dynamic array formulas (spill) which behave similarly without an explicit wrapper.
Parameters
| Name | Required | Description |
|---|---|---|
| array_formula | Required | A formula expression that operates on ranges. The result is an array of values, one per input row. |
Examples
Calculate a column without dragging
=ARRAYFORMULA(A2:A1000 * B2:B1000)Multiplies A by B row-by-row for all 999 rows from a single cell. No need to drag the formula down.
Conditional formatting in formulas
=ARRAYFORMULA(IF(A2:A1000 = "", "", A2:A1000 * 1.1))Returns blank for empty rows, otherwise multiplies A by 1.1. The IF prevents zeros from appearing in unused rows.
ARRAYFORMULA with VLOOKUP
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A1000, Prices!A:B, 2, FALSE)))Performs a VLOOKUP for every row in A2:A1000 in one formula. Much faster than 1000 separate VLOOKUP calls.
When to use an alternative
- MAP / BYROW / BYCOL — You want explicit functional programming over each row/column.
- QUERY — You need filtering, grouping, or sorting alongside the calculation.
- Dragging the formula down — Your sheet is tiny and clarity matters more than performance.
Common errors and how to fix them
#REF! "would extend over multiple cells"
Cause: The spilled output collides with content in adjacent cells.
Fix: Clear the cells in the spill area, or shrink the range your formula references.
Only first row populates
Cause: Forgot to wrap with ARRAYFORMULA, so the formula evaluated for just the first row.
Fix: Add ARRAYFORMULA() around the whole expression. Tip: pressing Ctrl+Shift+Enter on a normal formula adds it automatically.
Slow performance
Cause: ARRAYFORMULA over very large ranges with volatile functions (NOW, RAND, etc.).
Fix: Limit to actual data range instead of full columns; avoid volatile functions inside the array.
Related functions
Frequently Asked Questions
When should I use ARRAYFORMULA vs MAP/BYROW?
ARRAYFORMULA is older and works with most formulas through implicit broadcasting. MAP/BYROW (added 2022) are more explicit and predictable — you pass a LAMBDA that runs per row. For simple column math, ARRAYFORMULA is fine. For complex per-row logic, MAP is cleaner. They're both supported and not going anywhere.
Does ARRAYFORMULA work with all functions?
Most, but not all. Aggregate functions (SUM, COUNT) reduce rather than broadcast. Some functions (SUBTOTAL, INDIRECT) don't play well. If a formula behaves weirdly under ARRAYFORMULA, MAP or BYROW is the safer alternative.
How do I know which row I'm processing inside ARRAYFORMULA?
Use ROW() — inside ARRAYFORMULA it returns the row number array. E.g., ARRAYFORMULA(ROW(A2:A100)) returns [2, 3, ..., 100]. For column position use COLUMN().