Home/Formulas/Array/ARRAYFORMULA
Arrayhard

ARRAYFORMULA()

Enables the display of values returned from an array formula into multiple rows/columns.

Syntax
fx
=ARRAYFORMULA(array_formula)
Example
fx
=ARRAYFORMULA(A2:A100 * B2:B100)

Paste this into Google Sheets to run it. The live playground on this site covers 24 of the 61 functions, and ARRAYFORMULA isn't one of them yet.

Common pitfalls

  • Not all functions are array-aware. Wrapping in ARRAYFORMULA won't always cause spilling: test with a small range first.
  • Modern Sheets often spills automatically; ARRAYFORMULA is mostly needed for legacy compatibility or IF/SUMPRODUCT patterns.
  • Don't use whole-column refs (A:A) inside ARRAYFORMULA: it expands to thousands of rows and tanks performance.
Related formulas
QUERY()
Runs a Google Visualization API Query Language query across data.
FILTER()
Returns a filtered version of the source range, returning only rows or columns which meet the conditions.
Used by
SEQUENCE()
Generates a sequence of numbers, returned as an array of the requested shape.
More Array formulas →Keyboard shortcuts for Sheets →