ARRAYFORMULA function in Google Sheets
ARRAYFORMULA lets one formula work on whole ranges instead of single cells. Write it once in the top row and it fills the column with a result for every row, including rows added later when you use an open range like B2:B.
- Works in
- Google Sheets only
- Returns
- An array of results that fills the cells below (and to the right of) the formula.
Syntax
=ARRAYFORMULA(array_formula)
| Argument | Required? | What it is |
|---|---|---|
array_formula | Required | A formula that uses ranges where you would normally use single cells, such as B2:B*C2:C or IF(B2:B>=10,"Bulk","Standard"). |
Examples
Each result is calculated from the sample sheet by our formula engine in Sheets mode, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =ARRAYFORMULA(B2:B6*C2:C6) | $30.00spills 5 × 1 | Line totals for all five rows from one formula in E2 |
| F2 | =ARRAYFORMULA(IF(B2:B6>=10,"Bulk","Standard")) | Bulkspills 5 × 1 | IF runs once per row, so every order gets a label |
| G2 | =ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)) | $30.00spills 45 × 1 | Open ranges cover rows added later; the IF keeps empty rows blank instead of 0 |
Try it live
This is the sample sheet. Change any value or edit a formula, and the results update.
Practice ARRAYFORMULA
Graded instantly in Sheets mode, then re-checked on changed data.
Every order total from one formula
D2, write one formula that multiplies the quantity by the unit price for every order in rows 2 to 7. Wrap it in ARRAYFORMULA so it fills D2:D7 on its own.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| One value or an error instead of a column of results | Outside ARRAYFORMULA, Sheets only does row-by-row math on ranges inside functions built for it, such as FILTER and SUMPRODUCT. Wrap the formula: =ARRAYFORMULA(B2:B6*C2:C6). |
| #REF! with "Array result was not expanded" | Something is already in the cells the results need, often an old fill-down formula or a stray space. Clear everything below the formula. |
| Zeros all the way down the column | An open range like B2:B includes every empty row, and an empty cell times an empty cell is 0. Test for blanks first: =ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)). |
Tips
- Press Ctrl+Shift+Enter (⌘+Shift+Enter on a Mac) while editing a formula and Sheets wraps it in ARRAYFORMULA for you.
- Keep the header and the formula together in row 1 with an array literal: ={"Total";ARRAYFORMULA(B2:B*C2:C)}.
- Functions like IF, VLOOKUP and SUMIF run once per row inside ARRAYFORMULA, so one VLOOKUP can look up a whole column of IDs.
ARRAYFORMULA in Excel
Excel has no ARRAYFORMULA. In Excel 2021, 2024 and Microsoft 365, a formula like =B2:B6*C2:C6 spills its results on its own, and older versions use Ctrl+Shift+Enter array formulas.
Questions
Do I need ARRAYFORMULA with FILTER, SORT or QUERY?
No. Those functions already return several values and fill the cells they need. ARRAYFORMULA is for formulas that would otherwise return one value, like B2:B*C2:C.
How do I stop ARRAYFORMULA filling blank rows?
Check the first column for blanks: =ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C)). Empty rows get an empty string instead of 0.
What is the Excel version of ARRAYFORMULA?
Current Excel doesn't need one: =B2:B6*C2:C6 spills on its own in Excel 2021, 2024 and Microsoft 365. In Excel 2019 and earlier you confirm the formula with Ctrl+Shift+Enter instead.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine in Sheets mode.