fx
FunctionPedia
Quick Answer

ARRAYFORMULA Function

ARRAYFORMULA wraps a formula and applies it to every cell in a range at once, so you don't need to copy formulas down.

✓ Excel✓ Google SheetsExcel Google Sheets only

Syntax

ARRAYFORMULA
(array_formula)

Parameters

ParameterDescriptionRequired
array_formulaAny formula or expression that returns an array or range result. The formula is evaluated once for each row/column in the input ranges.Required

Basic Example

Multiply two columns row by row using a single formula

=ARRAYFORMULA(A2:A10 * B2:B10)
ResultColumn of products for each row

Instead of writing =A2*B2 in C2 and copying down, ARRAYFORMULA calculates the product for every row in A2:A10 and B2:B10 in one cell.

Advanced Examples

Example 1: Conditional array with IF

Sales commission

Calculate 10% commission only for sales greater than $1,000

=ARRAYFORMULA(IF(A2:A100 > 1000, A2:A100 * 0.1, 0))
Result: Commission per row
The IF statement is applied to each row. Only rows above $1,000 get a 10% commission; others return 0.

Example 2: Combine with FILTER

Data extraction

Filter and transform a dataset in one formula

=ARRAYFORMULA(FILTER(A2:C, B2:B > 50) * 1.1)
Result: Filtered rows with values increased by 10%
FILTER returns qualifying rows, and ARRAYFORMULA applies the 10% increase to the numeric columns.

How ARRAYFORMULA Works

ARRAYFORMULA evaluates the inner expression for each element of the input arrays and returns a spilled array. Any range references inside the formula are iterated element-by-element. It works especially well with IF, mathematical operators, and functions that normally return scalar values.

1
Pick a scalar formula
Write a formula you would normally copy down, such as =A2*B2.
2
Replace ranges with arrays
Change the references to full ranges, e.g. =A2:A10*B2:B10.
3
Wrap with ARRAYFORMULA
Type =ARRAYFORMULA(...) around the formula.
4
Place in one cell
Enter the formula in a single cell and it will expand automatically.

Important Notes & Limitations

  • ARRAYFORMULA is Google Sheets only; Excel uses dynamic arrays (e.g. =A2:A10*B2:B10 spills automatically).

  • Some functions like SUM, COUNT, or AVERAGE return a single value and do not spill inside ARRAYFORMULA.

  • If the output range overlaps existing data, the formula returns a #REF! error.

Common Errors & Fixes

#REF! errorThe spilled array is blocked by existing data in adjacent cells.

Fix: Clear the cells below or to the right where the formula is trying to expand.

Single value instead of arrayA function inside the formula is aggregating the result (e.g., SUM, COUNT).

Fix: Use row-by-row equivalents or remove the aggregator; SUM cannot be arrayed directly.

Mismatched range sizesInput ranges have different numbers of rows or columns.

Fix: Ensure all referenced ranges have the same dimensions.

Download Practice File

Practice ARRAYFORMULA with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the ARRAYFORMULA function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

Can ARRAYFORMULA work with SUM?
No, SUM returns a single number. For row-by-row sums, use =ARRAYFORMULA(A2:A10+B2:B10) or =SUMPRODUCT(A2:A10, B2:B10).
Why does ARRAYFORMULA return #REF?
The array is trying to expand into cells that already contain data. Clear those cells.
Can I use ARRAYFORMULA with VLOOKUP?
Yes, but only if the lookup value is an array. For example: =ARRAYFORMULA(VLOOKUP(A2:A10, Sheet2!A:B, 2, FALSE)).
Does ARRAYFORMULA slow down a sheet?
It can on very large ranges because the formula is calculated for every cell at once. For huge datasets, consider using QUERY or pivot tables.