One formula for the whole column
Normally you write a formula in the first row and drag it down. ARRAYFORMULA lets one formula work on whole ranges at once: =ARRAYFORMULA(B2:B8*C2:C8) multiplies each row's B by its C and spills every answer down the column.
=ARRAYFORMULA(array_formula)
Without it, Sheets expects single cells in places like B2:B8*C2:C8, so the formula won't calculate across the whole range. While editing a formula, press Ctrl+Shift+Enter (⌘+Shift+Enter on a Mac) and Sheets wraps it in ARRAYFORMULA for you.
Why people use it
- Rows added by a form: one formula in the header row area covers new rows automatically, where dragged formulas would stop.
- Fewer formulas to maintain: one formula to fix instead of hundreds of copies.
- No helper columns:
=SUM(ARRAYFORMULA(B2:B8*C2:C8))totals price × quantity in a single cell.
Most functions work element by element inside ARRAYFORMULA: IF, LEFT, UPPER, ROUND, VLOOKUP's search key and so on. Functions that already combine a range into one answer (SUM, AND, COUNTIF) still return one value.