How it works.
ARRAYFORMULA allows a standard arithmetic expression or formula to execute across an entire range of cells simultaneously in Google Sheets without needing to drag down formulas.
Formula syntax.
Standard Excel Notation=ARRAYFORMULA(array_formula)[like_this] are optional; all other arguments are required.Formula arguments.
Every parameter accepted by the ARRAYFORMULA function is detailed below.
A range or mathematical expression using ranges.
Example value:A2:A5 * B2:B5Simple example.
Step-by-Step ScenarioAutomated Line Totals
Calculate Total = Units * Price across multiple rows at once.
| Units | Unit Price | Line Total |
|---|---|---|
| 10 | 20 | — |
| 5 | 50 | — |
| 8 | 15 | — |
=ARRAYFORMULA(A2:A4 * B2:B4)Evaluates ARRAYFORMULA over the sample table cells, placing the verified output in cell C2.
Formula generator.
Full Generator WizardCustomize the argument fields below to generate a tailored ARRAYFORMULA formula ready to paste directly into your worksheet.
=ARRAYFORMULA()Expected result behavior.
Returns number 200 based on sample evaluation criteria and active cell references.
Best Practices
- Combine with IF(A2:A="", "", ...) to automatically calculate incoming Google Form rows.
Common Mistakes
- Specific to Google Sheets. Excel 365 natively spills calculations without requiring an ARRAYFORMULA tag.
Platform & version compatibility.
Verified platform compatibility matrix based on actual spreadsheet calculation engine specifications.
In modern Microsoft Excel (365 & 2021), simply enter your formula normally — dynamic arrays spill automatically across the grid.
Not supported in Microsoft Excel. In Excel 365 and 2021, array calculations spill dynamically without requiring an ARRAYFORMULA wrapper. Legacy Excel uses Ctrl+Shift+Enter.
Native Google Sheets wrapper function that enables non-array formulas to output array results across multiple rows/columns.
Frequently asked questions.
Technical Q&ACan I use ARRAYFORMULA across different worksheets in Excel?↓
Yes. You can reference cells located on other sheets by including the sheet name followed by an exclamation mark, for example: =ARRAYFORMULA(Sheet2!A2:A10). If the sheet name contains spaces, enclose it in single quotes: ='Q1 Report'!A2:A10.
What is the most common reason for errors in ARRAYFORMULA?↓
Specific to Google Sheets. Excel 365 natively spills calculations without requiring an ARRAYFORMULA tag.
Does ARRAYFORMULA work in Microsoft Excel?↓
No. ARRAYFORMULA is a proprietary Google Sheets function and is not supported in Microsoft Excel. Recommended Excel alternative: In modern Microsoft Excel (365 & 2021), simply enter your formula normally — dynamic arrays spill automatically across the grid.