ARRAYFORMULA in Google Sheets
Use ARRAYFORMULA to apply one formula to an entire column at once — no dragging, and new rows fill in automatically.
=ARRAYFORMULA(A2:A100 * B2:B100)
Calculates A*B for every row from 2 to 100 in a single cell — no need to fill down.
How it works
ARRAYFORMULA tells Google Sheets to run a formula across whole ranges instead of single cells. Put it once in the top cell and it spills results down the column. Wrap the entire expression in ARRAYFORMULA(...); press Ctrl+Shift+Enter and Sheets adds it for you. Guard against blank rows so it does not fill zeros to the bottom of the sheet.
Variations
Only calculate where a cell has data
=ARRAYFORMULA(IF(A2:A="", "", A2:A*B2:B))
Leaves rows blank until data is entered, so the column does not fill with 0s.
Join first and last name down a column
=ARRAYFORMULA(A2:A&" "&B2:B)
Concatenates two columns for every row at once.
Running header + auto-filling column
=ARRAYFORMULA({"Total"; IF(A2:A="",,A2:A*B2:B)})
Puts a header in row 1 and fills the rest automatically.
Examples
| Scenario | Formula |
|---|---|
| Whole-column line totals | =ARRAYFORMULA(A2:A*B2:B) |
| Whole-column percentage | =ARRAYFORMULA(A2:A/SUM(A2:A)) |
| VLOOKUP a whole column | =ARRAYFORMULA(VLOOKUP(A2:A, E:F, 2, FALSE)) |
FAQ
Why does ARRAYFORMULA fill zeros to the bottom?
Empty rows evaluate to 0. Guard with IF: =ARRAYFORMULA(IF(A2:A="", "", A2:A*B2:B)).
What is the shortcut for ARRAYFORMULA?
Type your formula with full ranges, then press Ctrl+Shift+Enter and Sheets wraps it in ARRAYFORMULA automatically.
Do I need ARRAYFORMULA if I can just fill down?
No, but it keeps one formula in one cell that updates as new rows arrive — cleaner than dragging and it can't be broken by a deleted row.