What's the difference between ARRAYFORMULA and copying a formula down each row?

ARRAYFORMULA lets you maintain a single formula that applies to the entire range at once. Copying a formula means each row gets its own formula—changes must be applied separately to each one. ARRAYFORMULA is also more efficient and updates instantly if data changes.

Can ARRAYFORMULA use IF and other conditional functions?

Yes. You can nest IF inside ARRAYFORMULA to evaluate conditions row-by-row across the entire array. However, for complex row filtering with multiple criteria, QUERY often provides clearer syntax and better performance.

Does ARRAYFORMULA recalculate automatically when data changes?

Yes. ARRAYFORMULA is fully dynamic—any change to the referenced ranges triggers an instant recalculation. Unlike manually copied formulas, you never need to refresh or re-enter it.

Why do I get #SPILL! and how do I prevent it?

#SPILL! occurs when the array result tries to expand into cells containing existing data. Move your ARRAYFORMULA to a clear column with empty space below, or delete any data blocking the expansion. You can also use a separate sheet to isolate your results.

ARRAYFORMULA function

ARRAYFORMULA applies a single formula to an entire array or range, automatically filling results into multiple cells.

=ARRAYFORMULA(array_formula)

Generate a ARRAYFORMULA formula

Describe what you need. The generator will reach for ARRAYFORMULA where ARRAYFORMULA is the right tool, and tell you when it is not.

Arguments

How ARRAYFORMULA reads its arguments

Argument	Required	Description
array_formula	Required	A formula that operates on ranges or arrays. Can reference cell ranges, use math operators, or include functions like IF, VLOOKUP, or INDEX. If array dimensions don't match, returns #VALUE!.

Returns

An array of values with the same dimensions as the input range, or an error if expansion is blocked.

Availability

Excel: Not available · Google Sheets: Supported

Worked examples

1. Calculate cost per click for each campaign

Campaign	Channel	Spend	Clicks	Conversions
Summer	Google	5000	1250	125
Summer	Facebook	3000	450	45
Fall	Google	8000	2100	210
Fall	Facebook	4500	675	68

=ARRAYFORMULA(C2:C5/D2:D5)

Result: [4, 6.67, 3.81, 6.67]

ARRAYFORMULA divides each value in Spend (C2:C5) by the corresponding Clicks (D2:D5), returning cost per click for all four campaigns in one formula. Without ARRAYFORMULA, you'd need to copy the formula down manually to each row.

2. Calculate conversion rate as percentage across all rows

Campaign	Channel	Spend	Clicks	Conversions
Summer	Google	5000	1250	125
Summer	Facebook	3000	450	45
Fall	Google	8000	2100	210
Fall	Facebook	4500	675	68

=ARRAYFORMULA((E2:E5/D2:D5)*100)

Result: [10, 10, 10, 10.07]

This formula calculates conversion rate (conversions ÷ clicks × 100) simultaneously for all rows. ARRAYFORMULA processes the entire Conversions and Clicks ranges at once, making it simple to compare efficiency across campaigns without repeating the formula.

3. Calculate cost per acquisition only for Google Ads channel

Campaign	Channel	Spend	Clicks	Conversions
Summer	Google	5000	1250	125
Summer	Facebook	3000	450	45
Fall	Google	8000	2100	210
Fall	Facebook	4500	675	68

=ARRAYFORMULA(IF(B2:B5="Google",C2:C5/E2:E5,""))

Result: [40, "", 38.1, ""]

ARRAYFORMULA with IF evaluates each row: if Channel is "Google", calculate Spend ÷ Conversions (cost per acquisition); otherwise return blank. This produces selective results for Google rows only while skipping Facebook rows in a single formula.

Common errors

Which ARRAYFORMULA error are you seeing?

Error	Why it happens	How to fix it
#SPILL!	The array formula tries to expand its results into cells that already contain data. ARRAYFORMULA cannot overwrite existing values.	Clear the cells below the formula, or move the ARRAYFORMULA to a column with empty space to allow the results to expand.
#VALUE!	The ranges in the formula have mismatched dimensions (e.g., dividing C2:C5 by D2:D7 where ranges are different sizes). ARRAYFORMULA requires all ranges to align.	Ensure all cell ranges reference the same number of rows and columns. Adjust your range references so they match in size.
#DIV/0!	The array formula performs division and encounters a zero in the divisor range (e.g., if Clicks contained 0, Spend/Clicks would fail).	Wrap the formula in IFERROR: =ARRAYFORMULA(IFERROR(C2:C5/D2:D5,0)) to handle division by zero gracefully with a fallback value.

Tips and when to use something else

ARRAYFORMULA is best for math and transformations across entire ranges. For filtering rows by condition, use QUERY or a dedicated function—it's clearer than nested IF logic.
If your formula uses VLOOKUP or other lookups that might return #N/A for some rows, wrap it in IFERROR to handle non-matches without breaking the entire array result.
ARRAYFORMULA spills downward and rightward, so ensure empty space below and to the right, or you'll hit a #SPILL! error. Use a separate helper column if needed.
Combine ARRAYFORMULA with TRANSPOSE or MAP to reshape and manipulate multi-dimensional data elegantly in a single formula without intermediate helper columns.

Related functions

QUERYQuery a dataset using SQL-like syntax to filter, sort, and extract specific rows and columns that match your conditions.TRANSPOSETRANSPOSE flips a row-or-column array, turning rows into columns and columns into rows, returning a spilled array of the same size swapped.MAPMAP applies a custom LAMBDA to each element of a range, returning a new array of the same shape with the calculated results.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.LAMBDALAMBDA lets you create custom, reusable functions directly in a cell, returning any value or array based on supplied parameters.

Frequently asked questions

What's the difference between ARRAYFORMULA and copying a formula down each row?: ARRAYFORMULA lets you maintain a single formula that applies to the entire range at once. Copying a formula means each row gets its own formula—changes must be applied separately to each one. ARRAYFORMULA is also more efficient and updates instantly if data changes.
Can ARRAYFORMULA use IF and other conditional functions?: Yes. You can nest IF inside ARRAYFORMULA to evaluate conditions row-by-row across the entire array. However, for complex row filtering with multiple criteria, QUERY often provides clearer syntax and better performance.
Does ARRAYFORMULA recalculate automatically when data changes?: Yes. ARRAYFORMULA is fully dynamic—any change to the referenced ranges triggers an instant recalculation. Unlike manually copied formulas, you never need to refresh or re-enter it.
Why do I get #SPILL! and how do I prevent it?: #SPILL! occurs when the array result tries to expand into cells containing existing data. Move your ARRAYFORMULA to a clear column with empty space below, or delete any data blocking the expansion. You can also use a separate sheet to isolate your results.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17