SheetsdrillARRAYFORMULA
ARRAYFORMULA(array_formula) makes a normal formula work on whole ranges instead of single cells, so one formula in the top cell returns a result for every row. =ARRAYFORMULA(B2:B*C2:C) fills the column with each row's product, and the output updates automatically as rows are added.
Syntax
=ARRAYFORMULA(array_formula)| Part | What it is |
|---|---|
array_formula | Any formula written with ranges where single cells would normally go, for example B2:B*C2:C or IF(A2:A="", "", A2:A*2). |
Examples
=ARRAYFORMULA(B2:B10*C2:C10)Result: Nine values, each row's quantity times price, starting in the formula cell
The cells below must be empty or you get #REF! (result was not expanded).
=ARRAYFORMULA(IF(A2:A="", "", A2:A*1.21))Result: Price with tax for every filled row and blank for empty rows
The IF guard stops the formula from filling the whole column with zeros.
=ARRAYFORMULA(SUM(B2:B10*C2:C10))Result: One number: the total of every row's quantity times price
Equivalent to SUMPRODUCT(B2:B10, C2:C10).
Common mistakes
- The formula shows only one result because you forgot ARRAYFORMULA; typing Ctrl+Shift+Enter after a formula wraps it in ARRAYFORMULA for you.
- #REF! saying the array result was not expanded means a cell in the output area is not empty; clear the cells below or to the right.
- A whole-column reference like A2:A keeps producing zeros or dates at the bottom; wrap the calculation in IF(A2:A="", "", ...).
Practice ARRAYFORMULA
These drills check your answer on live data, free, no account needed.
- Revenue with ARRAYFORMULATotal revenue is quantity times unit price on every row, added up. Do it in one formula in F1, without a helper column, using ARRAYFORMULA.
- One rate for every rowConvert each price from dollars to euros using the exchange rate in F1. Fill C2 and C3. Lock the rate with dollar signs so the same formula could be filled down the column.
Questions
Does ARRAYFORMULA work with VLOOKUP?
Yes: =ARRAYFORMULA(VLOOKUP(A2:A, Prices!A:B, 2, FALSE)) returns a lookup for every row. Wrap it in IFERROR or IFNA to hide the #N/A for empty rows.
Why is ARRAYFORMULA slow?
Open-ended ranges such as A2:A evaluate every row in the sheet. Limit the range to the data, or use a QUERY or FILTER that only returns real rows.
Related
- FILTERHow the FILTER function in Google Sheets returns only rows that match one or more conditions, with examples for AND, OR, dates and text, plus common errors.
- SUMPRODUCTHow SUMPRODUCT in Google Sheets multiplies two ranges row by row and adds the results, and how it works as a conditional sum with OR logic, with examples.
- VLOOKUPHow VLOOKUP works in Google Sheets: the four arguments, exact and approximate match, lookups from another sheet, and fixes for #N/A and wrong results.
- IFHow the IF function in Google Sheets returns one value when a condition is true and another when it is false, with examples for text, blanks and nested IFs.