ARRAYFORMULA function in Google Sheets

ARRAYFORMULA makes an ordinary formula work on a whole range at once, filling a column from a single cell.

Normally you write a formula in the first row and copy it down. ARRAYFORMULA lets you write it once, in one cell, and have it fill the whole column. When new rows are added, they are calculated without anyone copying anything. It is the reason Sheets users can build a column that "just works" for data arriving from a form.

Syntax

=ARRAYFORMULA(array_formula)

Argument	What it is
`array_formula`	A formula that uses ranges where it would normally use single cells.

Examples

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=ARRAYFORMULA(C2:C5*2)
Result:
156
90
184
66
One formula in one cell doubles four marks and fills four cells.
=ARRAYFORMULA(C2:C5+D2:D5+E2:E5)
Result:
254
157
259
112
Row totals for four students from a single formula.
=ARRAYFORMULA(IF(C2:C5>=50,"Pass","Fail"))
Result:
Pass
Fail
Pass
Fail
IF applied to each mark in turn.
=ARRAYFORMULA(A2:A4&" ("&B2:B4&")")
Result:
Asha (8A)
Ravi (8B)
Meena (8A)
Joining two columns, row by row.
=ARRAYFORMULA(IF(A2:A9="","",LEN(A2:A9)))
Result:
4
4
5
5
4
3
4
5
The usual guard: a row where column A is empty would be left empty instead of showing 0.
=ARRAYFORMULA(SUM(C2:C9*(B2:B9="8A")))
Result: 291
Inside ARRAYFORMULA, a condition can be multiplied in: the total Maths marks of class 8A.

How it works

Take a formula you would copy down, such as =C2*2. Replace the single cell with the whole range, C2:C9, and wrap it in ARRAYFORMULA. Sheets works the formula out for every row and spills the answers down from the cell.

A quick way to add the wrapper: type the formula with ranges and press Ctrl, Shift and Enter.

Whole columns

The range can be open-ended. C2:C means "from C2 to the bottom of the sheet", so new rows are covered as they arrive:

=ARRAYFORMULA(IF(A2:A="", "", C2:C*2))

The IF(A2:A="","", ...) part matters. Without it, the formula writes a result on every empty row to the bottom of the sheet, usually a column of zeros. Testing a column that is always filled, and returning empty text when it is not, keeps the blank rows blank.

Putting the heading in the formula

A common trick places the formula in row 1 and makes it produce its own heading, so nobody can sort or delete the formula by accident:

=ARRAYFORMULA(IF(ROW(A:A)=1, "Double", IF(A:A="", "", C:C*2)))

Which functions work inside it

Arithmetic, comparisons, &, IF, text functions, date functions, VLOOKUP and most single-value functions expand row by row.

Some do not, because they already swallow a whole range and return one value:

SUM, AVERAGE, MAX, MIN and COUNT give one total, not one per row.
AND and OR collapse everything to a single TRUE or FALSE. Multiply conditions for "and" and add them for "or".
SUMIFS and COUNTIFS with several conditions do not expand. SUMIF and COUNTIF with one condition do.
INDEX and MATCH together need care. VLOOKUP expands and is the usual choice.

For a total per row, add the columns with +, as in the second example.

Functions that need no wrapper

FILTER, SORT, UNIQUE, QUERY, SPLIT and SEQUENCE return arrays by nature and spill by themselves. Wrapping them does no harm and is not needed.

Do not type in the spill area

The cells below an ARRAYFORMULA belong to it. If you type a value into one of them, the formula shows #REF! with the message that the array result was not expanded because it would overwrite data. Delete what you typed and it recovers.

Speed

One ARRAYFORMULA over a column is generally lighter than thousands of copied formulas, but an open-ended range on a sheet with many empty rows still makes Sheets process every one. Delete unused rows at the bottom of big sheets.

In Excel

Excel has no ARRAYFORMULA. In Excel 2021 and Microsoft 365, a formula given a range spills by itself: =C2:C9*2 needs no wrapper. In older Excel, the same formula is entered across the cells with Ctrl, Shift and Enter. When an Excel file is opened in Sheets, such formulas gain the wrapper.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.