How to Use ARRAYFORMULA in Google Sheets
=ARRAYFORMULA(array_formula).
What ARRAYFORMULA does
Normally you write a formula in one cell and drag it down. With ARRAYFORMULA you write it once and the results fill the cells below. The formula is a single unit: it cannot be left half-copied, and new rows do not need a fill-down if the range covers them.
ARRAYFORMULA syntax
=ARRAYFORMULA(array_formula)
According to Google's help page, array_formula can be a range, a math expression that uses one or more ranges of the same size, or a function that returns a result larger than one cell.
- Many array formulas expand into neighboring cells by themselves, so you do not always need ARRAYFORMULA.
- While editing a formula, pressing Ctrl+Shift+Enter inserts
ARRAYFORMULA(at the start. - Array formulas cannot be exported.
A step-by-step example
Here is an order table in cells A1:C4. You want a line total (quantity times price) for every row.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Qty | Price |
| 2 | Pen | 3 | 1.5 |
| 3 | Book | 2 | 4 |
| 4 | Cap | 5 | 2 |
Without ARRAYFORMULA you would write =B2*C2 and copy it down. With it, one formula does the whole column:
=ARRAYFORMULA(B2:B4*C2:C4)
This returns 4.5, 8 and 10, one per row, in the three cells starting at the formula. Here is why:
B2:B4*C2:C4multiplies the two ranges row by row, because they are the same size.- ARRAYFORMULA tells Sheets to show every result instead of only the first one.
The cells where the results land must be empty, or Sheets shows an error instead of overwriting them.
Use it inside another function
You can also use array math to collapse a whole range into one number. Google's own example sums only the values above 5:
=ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))
For the order table, this gives the total of all line totals in one cell:
=ARRAYFORMULA(SUM(B2:B4*C2:C4))
This returns 22.5 (4.5 + 8 + 10).
Common ARRAYFORMULA mistakes
- Ranges of different sizes.
B2:B4*C2:C5does not line up and returns an error. - Blocked output cells. Anything in the cells where the result spreads stops the formula.
- Editing one result cell. The results belong to the formula in the top cell. Change that formula, not the cells below.
- Expecting a plain copy. The formula in the top cell is the only one. The cells below show its output.
Try it yourself
Use the order table above (A1:C4). Type your formula into the box, starting with =, then press Check.
Exercise 1: One formula, whole column
Write one formula that multiplies Qty (B2:B4) by Price (C2:C4) for every row and spreads the results down.
Exercise 2: Total in one cell
Write a single formula that returns the sum of all the line totals (Qty times Price) in one cell, using ARRAYFORMULA.
What to learn next
- SUMIFS: add up values that meet several conditions.
- TEXTJOIN: combine text from many cells with a separator.
- QUERY: filter, group and sort with one formula.
Syntax and rules in this guide were checked against Google's ARRAYFORMULA help page. See all guides.