Mastering Array Formulas in Google Sheets

July 24th, 2026

Most Sheets users learn formulas one cell at a time: write a formula in row 2, drag the fill handle to row 500, and hope nobody inserts a row above it later. Array formulas break that habit. A single formula can compute an entire column, build a lookup table on the fly, or generate a list of numbers out of thin air — no dragging, no broken references when rows shift. This post walks through five of the most useful array-shaped tools — ARRAYFORMULA, OFFSET, SORTN, RANK, and SEQUENCE — with worked examples for each.

ARRAYFORMULA: one formula, a whole column

ARRAYFORMULA forces a formula that normally works on single values to operate on an entire range at once, spilling the result down every row it covers.

=ARRAYFORMULA(C2:C1000 * D2:D1000)

Put this in E2 and it fills E2:E1000 with C*D for every row that has data — no dragging, and no gaps when new rows are added within the range. Compare that to the traditional approach: type =C2*D2 in E2, then drag it down. The drag-based version silently stays wrong if someone inserts a row at the top of the sheet and forgets to re-drag; the ARRAYFORMULA version keeps working because it lives in one place and covers the whole range by design.

ARRAYFORMULA really earns its keep with functions that don't naturally operate on arrays. IF, string functions like CONCATENATE or LEN, and arithmetic all normally expect single cells — feed them a range without ARRAYFORMULA and you'll only get a result for the first row:

=ARRAYFORMULA(IF(B2:B1000="", "", B2:B1000 & " - " & C2:C1000))

This concatenates two columns row by row, but only for rows where B isn't blank — avoiding a trailing " - " on every empty row below your data. Functions that already spill on their own, like FILTER, SORT, or SEQUENCE, don't need to be wrapped — wrapping them in ARRAYFORMULA is harmless but redundant.

OFFSET: building ranges that move

OFFSET returns a range shifted and resized relative to a starting cell:

=OFFSET(A1, rows, cols, [height], [width])

That makes it useful for ranges that need to shift as data grows, without rewriting the formula each time. A common pattern is a "last N days" window that always points at the most recent rows:

=SUM(OFFSET(A1, COUNTA(A:A)-30, 0, 30, 1))

COUNTA(A:A) finds how many rows are filled in column A; subtracting 30 lands on the row 30 entries back from the bottom, and the OFFSET call carves out exactly those 30 rows, 1 column wide. As new rows are appended, the window automatically slides down with them.

OFFSET pairs well with ARRAYFORMULA when you want the same dynamic window applied across several output cells, but on its own it's a lookup-table building block: combine it with MATCH to fetch a value some number of columns to the right of a matched row, without needing a plain VLOOKUP/INDEX setup.

One caveat: OFFSET is volatile — it recalculates on every edit to the sheet, not just when its inputs change. On very large sheets with many OFFSET formulas, that can measurably slow things down; reach for INDEX instead when you don't actually need a moving reference.

SORTN: top N per group

SORTN returns the top N rows of a range, sorted, and — critically — it supports grouping so you can get the top N per category in one formula:

=SORTN(A2:C500, 2, 2, 3, FALSE)

Reading the arguments: return 2 rows (n = 2), with tie-handling mode 2 (include ties), grouped by column 3 relative to the range (so "top 2 per distinct value in column C"), sorted by that same column ascending (FALSE = ascending).

A concrete case: a table of Region | Rep | Revenue and you want the top 2 reps by revenue in each region. Sort the source by revenue descending first (or wrap in SORT), then:

=SORTN(SORT(A2:C500, 3, FALSE), 2, 0, 1, TRUE)

Here SORT(A2:C500, 3, FALSE) orders the whole table by revenue descending, and SORTN(..., 2, 0, 1, TRUE) keeps the top 2 rows per distinct value in column 1 (Region), using tie-mode 0 (no ties, exactly n per group). The result is a compact leaderboard with no helper columns and no manual copy-pasting per region — as new reps or regions are added to the source, the output updates itself.

Without SORTN, the equivalent normally takes a helper column with RANK or COUNTIFS per group plus a FILTER on top; SORTN collapses that into one call.

RANK: ranking values without sorting the sheet

RANK returns a value's position within a range, without disturbing the row order — useful when you want a rank column sitting next to unsorted data:

=RANK(value, data, [is_ascending])

=RANK(C2, $C$2:$C$500, 0)

Placed in D2 and filled down (or wrapped in ARRAYFORMULA to avoid filling), this labels every row with its rank by column C, highest value first (0 = descending). Anchoring the range with $ is essential here — every row's RANK call needs to compare against the same full range, not a range that shifts down with the row.

To rank a whole column in one shot instead of filling down cell by cell:

=ARRAYFORMULA(RANK(C2:C500, C2:C500, 0))

This is one of the clearer cases where wrapping in ARRAYFORMULA is necessary rather than optional: RANK given two ranges of equal size compares each value against the full range only when the array engine is invoked — without the wrapper, only the first row computes and the rest come back blank.

SEQUENCE: generating number series and helper columns

SEQUENCE generates a list — or grid — of numbers without typing them or dragging a fill pattern:

=SEQUENCE(rows, [columns], [start], [step])

=SEQUENCE(12)

produces a single column of the numbers 1 through 12 — instantly useful as a row-index helper column that doesn't break when rows are sorted or filtered, unlike a dragged =ROW()-1 pattern.

The more powerful use is generating dynamic date ranges. To build 12 month-start dates from a given starting date without typing each one:

=ARRAYFORMULA(EDATE(DATE(2026,1,1), SEQUENCE(12,1,0,1)))

SEQUENCE(12,1,0,1) produces 0, 1, 2 ... 11 in a column; EDATE shifts the starting date forward that many months, and ARRAYFORMULA makes EDATE apply to every entry in the sequence instead of just the first. The result is a self-updating list of 12 month-start dates — change the start date in one place and the whole list recalculates.

SEQUENCE combined with OFFSET or INDEX is also a common way to build a grid of relative positions for spilling formulas into a fixed-size block, without laying out static row/column numbers by hand.

When array formulas beat helper columns

Helper columns are easier to debug — you can see each intermediate step — but they clutter the sheet, break when rows are inserted or deleted in the middle, and multiply maintenance work whenever the underlying logic changes. Array formulas are worth it when:

• The same calculation needs to run over many rows and the row count will keep growing.
• You want a single formula to be the source of truth, rather than a formula copy-pasted (and potentially edited inconsistently) across hundreds of rows.
• You're already producing a spilling result (from FILTER, SORT, SEQUENCE) and want to transform it further without re-anchoring to specific cells.

Helper columns still win when a calculation is genuinely one-off, when non-technical collaborators need to see and tweak intermediate values, or when the array logic would require nesting several nested array-aware functions to the point of being unreadable.

Common pitfalls

• Mismatched array sizes. ARRAYFORMULA(C2:C1000 * D2:D5) will error or return unexpected results because the two ranges don't line up row for row. Keep array arguments the same shape.
• Forgetting to wrap non-spilling functions. IF, arithmetic, and most text functions silently return a single value instead of an array unless wrapped in ARRAYFORMULA — the formula won't error, it'll just look like it only "worked for the first row."
• Referencing whole columns (C:C) inside ARRAYFORMULA. This forces the formula to evaluate against hundreds of thousands of blank rows, which is slow and can produce a stray result in row 1 from the header. Bound the range explicitly (C2:C1000) instead.
• Overusing volatile functions like OFFSET inside array formulas. Each recalculation on every edit adds up fast across large ranges; use INDEX for static-shape ranges when you don't need the range itself to move.
• Forgetting anchors in comparison ranges, as in the RANK example — a range without $ shifts when array-filled, breaking the comparison.

Related functions

• ARRAYFORMULA
• OFFSET
• SORTN
• RANK
• SEQUENCE
• FILTER