ARRAYFORMULA in Google Sheets: One Formula for a Whole Column
Use ARRAYFORMULA in Google Sheets to apply one formula to a whole column, skip blank rows, combine it with IF and VLOOKUP, and avoid errors.
By Ayoub Farid · · 1 min read
Normally you write a formula in row 2 and fill it down. ARRAYFORMULA lets one formula in row 2 calculate every row, including rows added later, for example by a Google Form.
Syntax
=ARRAYFORMULA(array_formula)Shortcut: type a formula and press Ctrl+Shift+Enter, and Sheets wraps it for you.
Example: revenue for every row
Instead of =B2*C2 filled down:
=ARRAYFORMULA(B2:B*C2:C)Open-ended ranges (B2:B) cover all rows.
Skip empty rows
The formula above fills every empty row with 0. Add IF:
=ARRAYFORMULA(IF(B2:B="","",B2:B*C2:C))Header in the same formula
Put the formula in row 1 to keep the header and results together:
={"Revenue";ARRAYFORMULA(IF(B2:B="","",B2:B*C2:C))}With VLOOKUP
=ARRAYFORMULA(IF(A2:A="","",IFERROR(VLOOKUP(A2:A,Prices!$A$2:$C$500,3,FALSE),"Not found")))See VLOOKUP in Sheets.
What doesn't work inside it
Functions that already return a single summary (SUM, AVERAGE, AND, OR) don't calculate per row. For per-row AND, multiply conditions: (B2:B>0)*(C2:C="Paid").
Errors
- #REF! "Array result was not expanded": something is typed in the cells below. Clear them.
- Slow sheets: open-ended ranges on very large sheets can be slow; limit them (
B2:B5000).
Excel equivalent
Modern Excel spills array results automatically: =B2:B500*C2:C500 works without a wrapper.
Frequently asked questions
What does ARRAYFORMULA do?
It applies a formula to whole ranges at once, returning a result for every row.
How do I stop ARRAYFORMULA showing zeros in empty rows?
Wrap the calculation in IF(range="","",...).
Why does ARRAYFORMULA give #REF!?
Cells in the output area aren't empty.
Does Excel have ARRAYFORMULA?
No, but Excel 365 dynamic arrays do the same job automatically.