ARRAYFORMULA in Google Sheets: One Formula for Every Row
ARRAYFORMULA Basics is a Google Sheets function that arrayformula applies the multiplication to every row simultaneously. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Write the formula once in the header row. It automatically applies to every row below. No dragging, no copying.
The Formula
"Calculate total price (quantity * unit price) for every row with one formula"
=ARRAYFORMULA(IF(A2:A<>"",B2:B*C2:C,""))ARRAYFORMULA applies the multiplication to every row simultaneously. The IF wrapper ensures empty rows don't show 0 — they stay blank.
Step-by-Step Breakdown
- ARRAYFORMULA wraps the entire calculation to apply it to all rows
- B2:B*C2:C multiplies quantity by unit price for every row
- IF(A2:A<>"", ..., "") only calculates where column A has data
- Open-ended ranges (B2:B instead of B2:B100) auto-include new rows
- Place this formula in the first data row — it fills downward automatically
Edge Cases & Warnings
- Only works in Google Sheets (Excel 365 has implicit arrays instead)
- If any cell in the output range has data, you get an error
- Open-ended ranges can slow down large sheets (1M+ cells)
- Nested ARRAYFORMULA calls can cause unexpected behavior
Examples
"Qty=[5,3,2], Price=[10,20,30]"
[50, 60, 60] in the total column"Empty row (A is blank)"
Blank (not 0)Frequently Asked Questions
Do I need ARRAYFORMULA in Excel?
No. Excel 365 handles arrays implicitly — just write the formula normally and it spills. In older Excel, use Ctrl+Shift+Enter for array formulas.
Why does my ARRAYFORMULA show 0 for empty rows?
Wrap your formula with IF(A2:A<>"", your_formula, "") to return blank for rows without data.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.