# ARRAYFORMULA function in Google Sheets

> ARRAYFORMULA lets one formula work on whole ranges instead of single cells.

Source: https://spreadsheetpractice.com/google-sheets/functions/arrayformula · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** ARRAYFORMULA lets one formula work on whole ranges instead of single cells.
- **Syntax:** `=ARRAYFORMULA(array_formula)`
- **Most common use:** `=ARRAYFORMULA(B2:B6*C2:C6)` returns $30.00 (Line totals for all five rows from one formula in E2)
- **Works in:** Google Sheets only
- **Returns:** An array of results that fills the cells below (and to the right of) the formula.

## What it does

ARRAYFORMULA lets one formula work on whole ranges instead of single cells. Write it once in the top row and it fills the column with a result for every row, including rows added later when you use an open range like B2:B.

## Syntax

`=ARRAYFORMULA(array_formula)`

| Argument | Required? | What it is |
|---|---|---|
| `array_formula` | Required | A formula that uses ranges where you would normally use single cells, such as B2:B*C2:C or IF(B2:B>=10,"Bulk","Standard"). |

## Examples

Each result was calculated by running the formula on the sample sheet in Google Sheets mode.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=ARRAYFORMULA(B2:B6*C2:C6)` | $30.00 (spills 5 × 1) | Line totals for all five rows from one formula in E2 |
| F2 | `=ARRAYFORMULA(IF(B2:B6>=10,"Bulk","Standard"))` | Bulk (spills 5 × 1) | IF runs once per row, so every order gets a label |
| G2 | `=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))` | $30.00 (spills 45 × 1) | Open ranges cover rows added later; the IF keeps empty rows blank instead of 0 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Item | Qty | Price |  | Total | Size | Total, open range |
| 2 | Notebook | 12 | $2.50 |  | $30.00 | Bulk | $30.00 |
| 3 | Stapler | 3 | $14.00 |  | $42.00 | Standard | $42.00 |
| 4 | Pens | 40 | $0.75 |  | $30.00 | Bulk | $30.00 |
| 5 | Folder | 8 | $1.20 |  | $9.60 | Standard | $9.60 |
| 6 | Tape | 15 | $2.10 |  | $31.50 | Bulk | $31.50 |
| 7 |  |  |  |  |  |  |  |
| 8 |  |  |  |  |  |  |  |
| 9 |  |  |  |  |  |  |  |
| 10 |  |  |  |  |  |  |  |
| 11 |  |  |  |  |  |  |  |
| 12 |  |  |  |  |  |  |  |
| 13 |  |  |  |  |  |  |  |
| 14 |  |  |  |  |  |  |  |
| 15 |  |  |  |  |  |  |  |
| 16 |  |  |  |  |  |  |  |
| 17 |  |  |  |  |  |  |  |
| 18 |  |  |  |  |  |  |  |
| 19 |  |  |  |  |  |  |  |
| 20 |  |  |  |  |  |  |  |
| 21 |  |  |  |  |  |  |  |
| 22 |  |  |  |  |  |  |  |
| 23 |  |  |  |  |  |  |  |
| 24 |  |  |  |  |  |  |  |
| 25 |  |  |  |  |  |  |  |
| 26 |  |  |  |  |  |  |  |
| 27 |  |  |  |  |  |  |  |
| 28 |  |  |  |  |  |  |  |
| 29 |  |  |  |  |  |  |  |
| 30 |  |  |  |  |  |  |  |
| 31 |  |  |  |  |  |  |  |
| 32 |  |  |  |  |  |  |  |
| 33 |  |  |  |  |  |  |  |
| 34 |  |  |  |  |  |  |  |
| 35 |  |  |  |  |  |  |  |
| 36 |  |  |  |  |  |  |  |
| 37 |  |  |  |  |  |  |  |
| 38 |  |  |  |  |  |  |  |
| 39 |  |  |  |  |  |  |  |
| 40 |  |  |  |  |  |  |  |
| 41 |  |  |  |  |  |  |  |
| 42 |  |  |  |  |  |  |  |
| 43 |  |  |  |  |  |  |  |
| 44 |  |  |  |  |  |  |  |
| 45 |  |  |  |  |  |  |  |
| 46 |  |  |  |  |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| One value or an error instead of a column of results | Outside ARRAYFORMULA, Sheets only does row-by-row math on ranges inside functions built for it, such as FILTER and SUMPRODUCT. Wrap the formula: =ARRAYFORMULA(B2:B6*C2:C6). |
| #REF! with "Array result was not expanded" | Something is already in the cells the results need, often an old fill-down formula or a stray space. Clear everything below the formula. |
| Zeros all the way down the column | An open range like B2:B includes every empty row, and an empty cell times an empty cell is 0. Test for blanks first: =ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)). |

## Tips

- Press Ctrl+Shift+Enter (⌘+Shift+Enter on a Mac) while editing a formula and Sheets wraps it in ARRAYFORMULA for you.
- Keep the header and the formula together in row 1 with an array literal: ={"Total";ARRAYFORMULA(B2:B*C2:C)}.
- Functions like IF, VLOOKUP and SUMIF run once per row inside ARRAYFORMULA, so one VLOOKUP can look up a whole column of IDs.

## ARRAYFORMULA in Excel

Excel has no ARRAYFORMULA. In Excel 2021, 2024 and Microsoft 365, a formula like =B2:B6*C2:C6 spills its results on its own, and older versions use Ctrl+Shift+Enter array formulas.

## Questions

### Do I need ARRAYFORMULA with FILTER, SORT or QUERY?

No. Those functions already return several values and fill the cells they need. ARRAYFORMULA is for formulas that would otherwise return one value, like B2:B*C2:C.

### How do I stop ARRAYFORMULA filling blank rows?

Check the first column for blanks: =ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)). Empty rows get an empty string instead of 0.

### What is the Excel version of ARRAYFORMULA?

Current Excel doesn't need one: =B2:B6*C2:C6 spills on its own in Excel 2021, 2024 and Microsoft 365. In Excel 2019 and earlier you confirm the formula with Ctrl+Shift+Enter instead.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
