Google Sheets Array Formulas: Powerful Techniques Guide

Still dragging the same formula down a thousand rows every time the data grows? An array formula does the whole column from a single cell — you write it once, and it keeps applying itself as new rows arrive. This covers what ARRAYFORMULA actually does, how to stretch ordinary functions like IF and VLOOKUP across a whole range, the handful of patterns worth memorizing, and — just as useful — when you don’t need it at all.

What an array formula actually does

A normal formula takes single values and returns a single value: =A2*B2 reads two cells and writes one. An array formula takes whole ranges and returns a whole range, written once in the top cell and spilling down from there. The classic example is a calculated column — instead of =A2*B2 copied down five hundred times, you write one formula that handles every row at once.

=ARRAYFORMULA(A2:A * B2:B)

That single formula in C2 multiplies every matching pair down columns A and B. The open-ended range A2:A means “from row 2 to the bottom of the sheet,” which is what makes the formula self-extending: add a row tomorrow and it’s already covered. No copying, no fill handle, and no “I forgot to extend the formula to the new rows” bug that surfaces three weeks later when a total comes out wrong.

Why one formula beats five hundred copies

On a sheet several people touch, copied-down formulas rot. Someone inserts a row in the middle and the copied formula doesn’t follow; someone deletes a cell and leaves a gap; someone pastes values over half the column. Each of these is invisible until a downstream total is off and you’re scrolling row by row to find where the pattern broke. An array formula has exactly one formula to inspect — it either works for the whole column or it doesn’t, and there’s no “works for rows 2–340 but not 341” middle ground to hunt down.

Stretch ordinary functions across a column

ARRAYFORMULA’s real power isn’t arithmetic — it’s wrapping functions that normally work one cell at a time and making them operate on an entire range. IF is the most common case, and it’s where most people first reach for it.

=ARRAYFORMULA(IF(B2:B="", "", B2:B * 1.1))

This adds 10% to every value in column B, but leaves blank rows blank instead of showing a stray 0 or a propagated error. The B2:B="" test runs on every row simultaneously, and the IF decides per row whether to calculate or stay empty. Without the wrapper, =IF(B2:B="", "", B2:B*1.1) would only evaluate the first row and fill the rest with copies of that single result — the wrapper is what tells Sheets to treat the whole range as a set of independent rows.

Turning a single VLOOKUP into a whole column

The same idea scales a lookup across every row without copying it down:

=ARRAYFORMULA(VLOOKUP(A2:A, Products!A:C, 3, FALSE))

One formula looks up every value in column A against the Products sheet and returns the third column for each. As new entries land in A, they’re looked up automatically.

The patterns worth memorizing

A few combinations come up often enough to keep in your head. Set up a small data range to follow along — region in column A, sales in column B:

Auto-numbering that survives inserted rows

=ARRAYFORMULA(IF(A2:A="", "", SEQUENCE(COUNTA(A2:A))))

This numbers each filled row 1, 2, 3… and stops where the data stops. Because it’s a single formula rather than a typed-in sequence, inserting a row in the middle renumbers everything below automatically — no manual fill, no gaps, no duplicate 7 because two people added rows at once. It’s the difference between a numbering column you maintain and one that maintains itself.

Find duplicates without a helper column

A common real task: which values appear more than once? Combining FILTER, UNIQUE, and COUNTIF answers it in one cell.

=FILTER(UNIQUE(A2:A), ARRAYFORMULA(COUNTIF(A2:A, UNIQUE(A2:A)) > 1))

UNIQUE lists each distinct region once, COUNTIF counts how often each appears across the full column, and FILTER keeps only the ones with a count above one. In the sample above, that returns “North.” Note the ARRAYFORMULA wraps only the COUNTIF — FILTER and UNIQUE are already array-native, which leads directly to the next section.

Category totals and joined labels in one cell

Two more worth keeping. To total by category without a pivot table, spread SUMIF down the column so every row carries its own group’s total:

=ARRAYFORMULA(IF(A2:A="", "", SUMIF(A2:A, A2:A, B2:B)))

North rows all show the North total, South rows the South total, recalculated whenever the data changes — the foundation for percentage-of-group math without a helper column. It’s the array cousin of conditional aggregation with SUMIFS and COUNTIFS, applied to a whole column instead of a single summary cell. String operators spread the same way:

=ARRAYFORMULA(A2:A & " — " & B2:B)

That builds a combined label like “North — 1200” down the entire column, handy for chart labels or dropdown sources that need a single joined field. The same trick formats a whole column at once: =ARRAYFORMULA(TEXT(B2:B, "$#,##0")) turns raw numbers into currency without touching each cell.

When you don’t need ARRAYFORMULA at all

Here’s the part most tutorials skip, and it saves more confusion than any single pattern. Several functions already return arrays on their own, so wrapping them in ARRAYFORMULA adds nothing but visual clutter and a false sense that the wrapper is doing something.

If your goal is “show every row where region is North,” that’s =FILTER(A2:B, A2:A="North"), full stop. The mental rule is clean: reach for ARRAYFORMULA when you need to apply a single-cell function — IF, VLOOKUP, a math operation, TEXT, LEFT — across a range that wouldn’t otherwise spill. When a function is built to return a range, like the QUERY function or IMPORTRANGE, it already handles the array and the wrapper is dead weight. Google’s own ARRAYFORMULA reference spells out which functions need it and which don’t.

The quick test before you type ARRAYFORMULA

Ask one question: does this function, on its own, already return more than one cell? If yes — FILTER, QUERY, SORT — skip the wrapper. If no — IF, VLOOKUP, a multiplication — and you want it applied down a column, wrap it. That single check resolves most “do I need ARRAYFORMULA here?” hesitation.

Mistakes that quietly break array formulas

Most array-formula failures come from a few predictable causes. Walk these before assuming the formula itself is wrong.

• ✓ All ranges are the same length — A2:A paired with B2:B, never A2:A with B2:B10
• ✓ The spill area is empty — an array formula throws #REF if anything blocks the cells it needs to fill
• ✓ Blank rows are guarded with an IF so trailing empty rows don’t show 0 or an error
• ✓ Open-ended ranges (A2:A) are used only when you want the entire column

The mismatched-range mistake is the most common: if one range runs to the bottom of the sheet and the other stops at row 10, Sheets either errors or pads the short one with blanks, and your results shift out of alignment. The performance trap is subtler. An open-ended A2:A evaluates every row in the column — all 1,000 by default, empty or not. On a small sheet that’s free; on a sheet with tens of thousands of rows it’s a real drag, and bounding the range to something like A2:A5000 can turn a sluggish recalculation back into an instant one. Use open-ended ranges for convenience on modest sheets, bounded ranges for speed on large ones.

So when should you reach for one?

Use an array formula when you’d otherwise copy the same single-cell logic down a column — calculated fields, conditional transforms, lookups that should cover every row. Skip it when a range-native function already does the job, and bound your ranges once the sheet gets big. The model is simple: one formula, one cell, a whole column of results that maintains itself as the data grows. Learn the three or four patterns above — calculated columns, conditional transforms, auto-numbering, duplicate-finding — and the fill handle becomes something you reach for far less often. The payoff compounds on shared and growing sheets: one self-maintaining formula quietly prevents the gap-and-drift bugs that hundreds of copied formulas accumulate over months, and it never needs anyone to remember to extend the range to the new rows.