Syntax
=ARRAYFORMULA(array_formula)
array_formula- A formula that uses ranges where you would normally use single cells, such as B2:B*C2:C or IF(B2:B>50, "Yes", "No").
ARRAYFORMULA function in Google Sheets
ARRAYFORMULA lets one formula work on whole ranges instead of single cells, then spills one result per row. Instead of writing =E2/D2 and dragging it down 500 rows, you write =ARRAYFORMULA(B2:B*C2:C) once and the whole column fills itself, including rows added later by a form or an import.
Practice ARRAYFORMULA
Task 1 of 5
Work out the total (price × qty) for every row with one formula in D2.
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
ARRAYFORMULA examples
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Revenue |
| 2 | Ava | West | Monitor | 12 | $2,400.00 |
| 3 | Liam | East | Keyboard | 30 | $1,500.00 |
| 4 | Maya | West | Mouse | 45 | $900.00 |
| 5 | Noah | North | Monitor | 8 | $1,600.00 |
| 6 | Zara | East | Monitor | 15 | $3,000.00 |
| 7 | Omar | West | Keyboard | 22 | $1,100.00 |
| Formula | Result | What it shows | ||||||
|---|---|---|---|---|---|---|---|---|
=ARRAYFORMULA(E2:E7/D2:D7) |
| Revenue per unit for every row. | ||||||
=ARRAYFORMULA(IF(E2:E7>=2000, "Big", "Small")) |
| IF over the whole column. | ||||||
=ARRAYFORMULA(A2:A7&" - "&B2:B7) |
| Joins two columns row by row. | ||||||
=ARRAYFORMULA(UPPER(LEFT(B2:B7, 1))) |
| Text functions work element by element too. | ||||||
=SUM(ARRAYFORMULA((B2:B7="West")*E2:E7)) | $4,400.00 | A conditional total in one cell: the West rows count as 1, the rest as 0. |
Why use ARRAYFORMULA instead of dragging a formula down?
- Form responses and imports: new rows from Google Forms are inserted below the existing ones, and copied-down formulas don't follow them. One ARRAYFORMULA over an open range like
B2:Bcovers every new row. - One place to fix: there's a single formula to edit, not hundreds of copies that may have drifted apart.
- No helper columns: totals like
=SUM(ARRAYFORMULA(B2:B*C2:C))do row-by-row math inside one cell.
Keeping blank rows blank
Over an open range, =ARRAYFORMULA(B2:B*C2:C) also writes 0 next to every empty row. Wrap it in an IF that checks for blanks: =ARRAYFORMULA(IF(A2:A="", "", B2:B*C2:C)).
The keyboard shortcut
While editing a formula, press Ctrl+Shift+Enter (⌘+Shift+Enter on a Mac) and Sheets wraps it in ARRAYFORMULA for you.
Tips and common mistakes
- The results spill down (and across). Anything already in the way causes #REF!, so keep the column below the formula empty.
- Functions that already reduce a range to one answer, such as SUM, COUNTIF, AND and OR, still return a single value inside ARRAYFORMULA. Use * for AND and + for OR between row-by-row tests instead.
- All ranges in the formula must be the same size. B2:B10*C2:C9 gives #VALUE! with "Array arguments to MULTIPLY are of different size".
- Some functions handle ranges on their own and don't need ARRAYFORMULA at all, including FILTER, SORT, UNIQUE, QUERY and SUMPRODUCT.
Related functions
Learning step by step? ARRAYFORMULA is covered in Lesson 35: ARRAYFORMULA of the free course.