You have a formula in C2 that calculates =A2*B2. It works perfectly. Now you need it in C3, C4, C5, all the way to C10000. You drag it down. Tomorrow someone adds new rows at the bottom. Your formula doesn't cover them.
ARRAYFORMULA solves this forever. One formula in C1, and every row calculates automatically.
The Basics
C2: =A2*B2
C3: =A3*B3
C4: =A4*B4
...drag down for 10,000 rows...
C1: =ARRAYFORMULA(A1:A * B1:B)
That single formula in C1 multiplies every row in column A by the corresponding row in column B. New rows are automatically included.
Type a regular formula and press Ctrl+Shift+Enter (Cmd+Shift+Enter on Mac). Google Sheets automatically wraps it in ARRAYFORMULA.
Handling Blank Rows
The biggest ARRAYFORMULA problem: it fills results for every row, including empty ones. Row 5001 has no data, but the formula shows 0 (or an error). Fix it with IF:
=ARRAYFORMULA(
IF(A2:A = "", "",
A2:A * B2:B
)
)
This checks if column A is empty. If yes, return blank. If no, calculate. This pattern, ARRAYFORMULA(IF(col="","", formula)), is the single most useful thing to memorize in Google Sheets.
ARRAYFORMULA + IF for Conditional Logic
=ARRAYFORMULA(
IF(A2:A = "", "",
IF(C2:C >= 1000, "High Value",
IF(C2:C >= 500, "Medium",
"Low"
)
)
)
)
=ARRAYFORMULA(
IF(A2:A = "", "",
IFS(
C2:C >= 1000, "High Value",
C2:C >= 500, "Medium",
TRUE, "Low"
)
)
)
ARRAYFORMULA + VLOOKUP
Normally, VLOOKUP in an ARRAYFORMULA is slow on large datasets. But for moderate data (under 5,000 rows), it works:
=ARRAYFORMULA(
IF(A2:A = "", "",
VLOOKUP(A2:A, Departments!A:B, 2, FALSE)
)
)
ARRAYFORMULA + Text Functions
=ARRAYFORMULA(
IF(A2:A = "", "",
A2:A & " " & B2:B
)
)
=ARRAYFORMULA(
IF(C2:C = "", "",
REGEXEXTRACT(C2:C, "@(.+)")
)
)
=ARRAYFORMULA(
IF(A2:A = "", "",
PROPER(TRIM(LOWER(A2:A)))
)
)
ARRAYFORMULA + Date Functions
=ARRAYFORMULA(
IF(B2:B = "", "",
TODAY() - B2:B
)
)
=ARRAYFORMULA(
IF(B2:B = "", "",
B2:B + 30
)
)
Auto-Numbering Rows
=ARRAYFORMULA(
IF(B2:B = "", "",
ROW(B2:B) - ROW(B2) + 1
)
)
This generates 1, 2, 3, 4... for every row that has data in column B. New rows automatically get the next number.
Auto-Generating Timestamps
=ARRAYFORMULA(
IF(B2:B = "", "",
IF(A2:A = "", NOW(), A2:A)
)
)
ARRAYFORMULA timestamps recalculate on every sheet edit, so they won't be permanent. For permanent timestamps, use a simple Apps Script onEdit trigger instead.
Functions That Don't Need ARRAYFORMULA
Some Google Sheets functions are already array-aware:
QUERY: Always returns arraysFILTER: Returns all matching rowsSORT: Returns sorted arrayUNIQUE: Returns unique valuesIMPORTRANGE: Returns full range
You don't need to wrap these in ARRAYFORMULA. They're already array formulas by default.
Performance Tips
- Use bounded ranges when possible.
A2:A1000is faster thanA2:Abecause Sheets doesn't have to scan to the last row. - Avoid nesting VLOOKUP inside ARRAYFORMULA on large datasets. Use INDEX/MATCH or QUERY instead.
- One ARRAYFORMULA is better than 10,000 individual formulas. It's faster to calculate and easier to maintain.
- Put ARRAYFORMULA in row 1 (as header) or row 2. Make it clear this is a single formula controlling the whole column.
If you're copying a formula down more than 10 rows, you should be using ARRAYFORMULA instead.