# ARRAYFORMULA: one formula for the whole column (Google Sheets lesson 9 of 17)

> Write the formula once at the top, and every row below gets its answer, including rows added later.

Source: https://spreadsheetpractice.com/google-sheets/learn/arrayformula · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Sheets power tools"
- **Time:** about 10 minutes, 3 graded exercises
- **Functions:** `ARRAYFORMULA`, `IF`, `{ ; }`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=ARRAYFORMULA(B2:B6*C2:C6)`: One formula, one result per row

## How it works

Most formulas work on one row and get filled down. `ARRAYFORMULA` lets a single formula work on whole ranges instead: `=ARRAYFORMULA(B2:B6*C2:C6)` multiplies each row's hours by its rate and fills the results down the column. There is only one formula to check and nothing to fill. In Sheets, press Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac) while editing a formula to wrap it in ARRAYFORMULA for you.

Pair it with an open-ended range to cover rows that don't exist yet: `B2:B*C2:C`. On its own, that returns 0 for every empty row down the sheet. Wrap the math in IF to leave those rows blank: `=ARRAYFORMULA(IF(A2:A="", "", B2:B*C2:C))`. When someone types a new task in column A, its cost appears.

The results need empty cells to land in. Type anything into the column below an ARRAYFORMULA and the whole result is replaced by an error until you clear it. Functions such as FILTER, SORT, UNIQUE and QUERY already return ranges, so they don't need the wrapper.

To keep the header and the formula together, build a small array with curly braces. A semicolon stacks items on top of each other: `={"Cost"; ARRAYFORMULA(IF(A2:A="", "", B2:B*C2:C))}` goes in the header cell and fills the column beneath it.

## Syntax

| Formula | What it does |
|---|---|
| `=ARRAYFORMULA(B2:B6*C2:C6)` | One formula, one result per row |
| `=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))` | Every row, blank where column A is empty |
| `={"Cost";ARRAYFORMULA(B2:B6*C2:C6)}` | A header stacked on top of the results |
| `Ctrl+Shift+Enter` | Wraps the formula you are editing in ARRAYFORMULA |

## Watch out for

- An open range without IF fills the rest of the column with zeros. Test column A for blanks first.
- Anything typed in the result area blocks the whole formula. Keep the column below it empty.
- Every range in the formula should start on the same row. B2:B with C3:C pairs each hours value with the next row's rate.
- This practice grid works out ranges even without the wrapper, so the exercises ask for ARRAYFORMULA by name. In Google Sheets, leave it off and you won't get the whole column of results.

## The exercises

Solve them in the live grid at [ARRAYFORMULA: one formula for the whole column](https://spreadsheetpractice.com/google-sheets/learn/arrayformula). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Every cost in one formula.** In D2, write one ARRAYFORMULA that multiplies the hours in B2:B6 by the rates in C2:C6, so every row from 2 to 6 gets its cost.
2. **Ready for new rows.** In D2, write one ARRAYFORMULA with open-ended ranges that works out hours times rate for every row, and leaves the cost blank on rows where column A is empty.
3. **Header and formula together.** Column D is already done. In E1, put the header "With tax" and, under it, each cost in column D plus the tax rate in G2. Use one formula in E1: an array that stacks the header on top of an ARRAYFORMULA, leaving empty rows blank.

Previous: [UNIQUE, COUNTUNIQUE and duplicates](https://spreadsheetpractice.com/google-sheets/learn/unique-and-duplicates) · Next: [SPLIT, JOIN and text cleanup](https://spreadsheetpractice.com/google-sheets/learn/split-join-text)

---

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