Google Sheets ARRAYFORMULA
Google Sheets ARRAYFORMULA applies one formula to an entire range at once, so you never copy down again. Learn how to combine it with IF, FILTER, and QUERY.
Introduction
Copying a formula down hundreds of rows is tedious and brittle — insert a row and the pattern breaks. Google Sheets' ARRAYFORMULA solves this by applying a single formula to a whole range and spilling the results automatically. This guide covers the core pattern and how to combine it with IF, FILTER, and QUERY.
Prerequisites
- Basic spreadsheet formulas (multiplication, IF)
- A Google Sheets spreadsheet to test in
- Awareness that ARRAYFORMULA is Google Sheets only
1What ARRAYFORMULA Does
ARRAYFORMULA wraps any formula and evaluates it for every cell in the input range at once, returning a spilled array of results. Instead of writing =A2*B2 in C2 and dragging down, you reference the full columns and let Sheets calculate each row in a single cell.
Example
=ARRAYFORMULA(A2:A10 * B2:B10)The multiplication is applied element-by-element. Place the formula in one cell (e.g. C2) and it fills C2:C10 automatically.
Leave the cells below the formula empty so the array has room to spill.
2Replacing Fill-Down with One Formula
The most common use is to replace a column of copied formulas with a single spilling formula. Write the formula you would copy down, then swap the single-cell references for full column ranges and wrap everything in ARRAYFORMULA.
Write the scalar version
Start with the row-by-row formula.
=A2*B2Use full ranges
Change references to the whole column range.
=A2:A10*B2:B10Wrap with ARRAYFORMULA
Enclose the expression so Sheets spills it.
=ARRAYFORMULA(A2:A10*B2:B10)This pattern survives inserted rows because the range reference expands with the data.
3Combining ARRAYFORMULA with IF
IF normally returns one value, but inside ARRAYFORMULA it is applied to every row. This is perfect for row-by-row conditional calculations such as tiered commissions or status flags.
Example
=ARRAYFORMULA(IF(A2:A100 > 1000, A2:A100 * 0.1, 0))The condition A2:A100 > 1000 is evaluated per row, and the true/false branches are computed element-by-element.
Avoid aggregators like SUM inside ARRAYFORMULA — they collapse to one value; use row-by-row math or SUMPRODUCT instead.
4ARRAYFORMULA with FILTER and QUERY
ARRAYFORMULA pairs naturally with other array functions. You can transform a FILTER result in place, or pre-compute a column that QUERY then references. These combinations let you build entire report tables from one cell.
Example
=ARRAYFORMULA(FILTER(A2:C, B2:B > 50) * 1.1)FILTER returns the qualifying rows, and ARRAYFORMULA applies the 10% increase to the numeric values before spilling.
For very large datasets, QUERY or a pivot table can be faster than one giant ARRAYFORMULA.
5Excel Equivalent: Dynamic Arrays
ARRAYFORMULA is a Google Sheets exclusive. In Excel 365, the same effect comes from implicit dynamic arrays — =A2:A10*B2:B10 spills on its own with no wrapper. In older Excel you would need Ctrl+Shift+Enter to force array evaluation, which is exactly what ARRAYFORMULA does in Sheets.
Example
=A2:A10 * B2:B10Excel 365 recognizes an array expression and spills it, so ARRAYFORMULA is unnecessary there.
If a spilled array lands on occupied cells in Sheets, you get #REF! — clear the target range first.
Functions Used
ARRAYFORMULA
ARRAYFORMULA lets you apply a single formula to an entire range of cells automatically, expanding results into neighboring rows and columns without manually filling down or across.
QUERY
QUERY runs a Google Visualization API Query Language statement on a range of data. It's Google Sheets' most powerful data function — essentially SQL for your spreadsheet.
FILTER
FILTER extracts rows from a range that meet a condition, returning a dynamic array of matching results. It's the array-era replacement for complex IF/INDEX combinations.
SEQUENCE
SEQUENCE returns a dynamic array of sequential numbers, shaped by the number of rows and columns, starting at a given value and stepping by a given increment.
Related Guides
Master the QUERY Function in Google Sheets
Learn how to use Google Sheets' powerful QUERY function to filter, sort, and analyze data like a database.
Master Excel Dynamic Arrays (Excel 365)
Learn how to use modern dynamic array functions like FILTER, SORT, UNIQUE, and SEQUENCE to transform your spreadsheets.
Extract Text From String
Extract text from string in Excel and Google Sheets using LEFT, RIGHT, MID, and FIND. Learn to pull the first or last word, a middle substring, or text between two delimiters.
Summary
Google Sheets ARRAYFORMULA lets one formula replace an entire column of copied-down calculations by evaluating a range element-by-element and spilling the results. Combine it with IF for row-wise conditions, with FILTER or QUERY for in-formula data shaping, and remember there is no Excel equivalent — Excel 365 uses implicit dynamic arrays instead. Keep the spill range clear to avoid #REF! errors.
Next Steps
- Rewrite one of your fill-down columns as a single ARRAYFORMULA
- Nest ARRAYFORMULA with IF to compute row-wise conditional values
- Explore QUERY for SQL-like filtering when the dataset grows large