What ARRAYFORMULA Does
ARRAYFORMULA takes a formula that normally works on a single cell and applies it to an entire range of cells at once. Instead of dragging a formula down hundreds of rows, you write one ARRAYFORMULA in the first cell and it fills results for every row automatically. It is essential for maintaining clean, scalable spreadsheets.
Syntax
=ARRAYFORMULA(formula)
| Parameter | Description |
|---|---|
| formula | Any formula that you want applied across a range. Replace single cell references (like A2) with range references (like A2:A). |
Basic Examples
Example 1: Multiply Two Columns
Calculate line totals for an order sheet without dragging the formula down.
| A | B | C (Formula in C2) | C (Results) | |
|---|---|---|---|---|
| 1 | Qty | Price | Total | Total |
| 2 | 10 | 25 | =ARRAYFORMULA(A2:A*B2:B) | 250 |
| 3 | 5 | 40 | 200 | |
| 4 | 8 | 15 | 120 |
One formula in C2 fills every row.
Example 2: Add a Prefix to Every Cell
Prepend "INV-" to every order number in column A:
=ARRAYFORMULA("INV-" & A2:A)
Result: INV-1001, INV-1002, INV-1003, ...
Example 3: Conditional Calculation
Combine ARRAYFORMULA with IF to apply logic across a column:
=ARRAYFORMULA(IF(A2:A="", "", B2:B * 1.1))
This adds 10% markup to column B, but only for rows that aren't blank. The IF(A2:A="","",...) pattern prevents the formula from filling empty rows with zeros.
Advanced Examples
ARRAYFORMULA with VLOOKUP
Pull category names for every product in one formula:
=ARRAYFORMULA(VLOOKUP(A2:A, Categories!A:B, 2, FALSE))
This runs a VLOOKUP for each value in column A without needing to copy the formula down.
Auto-Numbering Rows
Generate sequential row numbers that grow as data is added:
=ARRAYFORMULA(IF(B2:B="", "", ROW(B2:B)-ROW(B2)+1))
Each non-empty row gets a number (1, 2, 3, ...). New entries are numbered automatically.
Common Mistakes
- Getting zeros or results in empty rows. When the source range extends to the bottom of the sheet (e.g.,
A2:A), empty cells produce unwanted results. Always wrap withIF(A2:A="","", ...)to skip blanks. - Trying to use ARRAYFORMULA with functions that already return arrays. Functions like FILTER, UNIQUE, and QUERY already output arrays. Wrapping them in ARRAYFORMULA is unnecessary and can cause errors.
- Overwriting the array output. If any cell in the output range already has content, the ARRAYFORMULA will show a #REF! error. Make sure the cells below are clear.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "multiply quantity by price for every row" — and the formula is generated for you. Install SheetAI to try it free.