Google Sheets ARRAYFORMULA: complete walkthrough

every Sheets user has dragged a formula down 5,000 rows. the data adds rows every week and you forget to extend, the dashboard breaks, you find out two days later when a number looks wrong.

ARRAYFORMULA fixes that. it lets one formula in one cell calculate results for an entire column. add a row to the source data, the result column auto-extends. delete a row, the result auto-shrinks. you write the formula once and never touch it again.

most Sheets users avoid ARRAYFORMULA because the syntax feels weird at first. ranges replace single cells, IF statements behave differently, and some functions need a special wrapper. once you understand the four rules of array thinking, the syntax becomes natural and your spreadsheets get dramatically more durable.

this guide walks through ARRAYFORMULA from zero. we use a 1,000-row sales tracker (Order ID, Date, Salesperson, Region, Product, Units, Revenue) so every example mirrors real analysis work. by the end you will know when to use ARRAYFORMULA, when to skip it, and the eight patterns that cover most reporting needs.

Google Sheets ARRAYFORMULA is a wrapper that makes a formula return a range of results instead of a single value. Syntax: =ARRAYFORMULA(formula_using_ranges). One formula in one cell calculates an entire column, auto-expanding as the source data grows. Replaces fill-down for arithmetic, IF statements, lookups, and most aggregations. Particularly powerful for dashboards where the source data changes weekly and manual extension is unreliable. Shortcut: Ctrl+Shift+Enter inserts ARRAYFORMULA() around the current cell formula.

what ARRAYFORMULA does and why it matters

a normal formula returns a single value. =B2*C2 returns one number. drag it down 1,000 rows, you get 1,000 calculations, but each is a separate formula stored in its own cell.

ARRAYFORMULA changes the semantics. =ARRAYFORMULA(B2:B1000*C2:C1000) returns a vector of 999 values, written from one cell. only one formula exists in the sheet, but the result spills across an entire column.

three concrete benefits:

auto-extending: change B2:B1000 to B2:B and ARRAYFORMULA covers every row that exists.
fewer formula cells: 1,000 cells of arithmetic become 1 cell. recalculation is faster, file size is smaller.
safer for shared dashboards: collaborators cannot accidentally overwrite cell N500 because that cell does not contain a formula — only N1 does.

ARRAYFORMULA pairs naturally with QUERY, which is also array-aware. together they handle most Sheets-based analytics work.

the four rules of ARRAYFORMULA thinking

ARRAYFORMULA breaks how some functions work. these four rules cover most edge cases.

rule 1: ranges replace single cells

a normal formula uses a single cell as input. ARRAYFORMULA uses a range. B2 becomes B2:B1000. every cell reference in the formula must shift to a range of the same length.

rule 2: arithmetic and comparisons vectorize automatically

multiply, divide, add, subtract, compare — all of these work cell-by-cell across the range when wrapped in ARRAYFORMULA. you do not need to change anything except the cell references.

rule 3: IF and IFERROR vectorize too

=IF(B2>100, "high", "low") becomes =ARRAYFORMULA(IF(B2:B1000>100, "high", "low")). each row gets its own true/false branch.

rule 4: some functions need special wrappers

a few functions do not vectorize cleanly:
– VLOOKUP needs to be wrapped: ARRAYFORMULA(VLOOKUP(A2:A, lookup_range, 2, FALSE)).
– aggregations like SUM, AVERAGE return a single number — they cannot be vectorized.
– some text functions (REGEXEXTRACT, CONCATENATE, JOIN) work but need careful range alignment.

remember rule 4 when ARRAYFORMULA returns weird results: the function inside might not be array-friendly.

the eight ARRAYFORMULA patterns

pattern 1: simple arithmetic across a column

problem: calculate revenue per unit for every order. Revenue is in column G, Units in column F.

=ARRAYFORMULA(G2:G1000/F2:F1000)

put this formula in cell H2. it fills H2:H1000 with results, one row per order.

[SCREENSHOT: Sheets cell H2 with the ARRAYFORMULA above and column H showing per-unit revenue values]

to make it self-extending, drop the bottom-row reference:

=ARRAYFORMULA(G2:G/F2:F)

now H auto-fills as new orders are added to the data. the only catch: empty cells return errors. fix with rule 3.

pattern 2: IF for conditional values

problem: tag each order as “high value” if revenue is above $1,000, else “low value”.

=ARRAYFORMULA(IF(G2:G="", "", IF(G2:G>1000, "high value", "low value")))

the outer IF handles the empty-row case. the inner IF does the value tagging. without the empty-row guard, the column would show “low value” for every blank row, polluting the data.

pattern 3: IFERROR for graceful failures

problem: revenue per unit when some orders have zero units (which would error out).

=ARRAYFORMULA(IFERROR(G2:G/F2:F, 0))

IFERROR replaces #DIV/0! errors with 0. clean for dashboards. add the empty-row guard from pattern 2 if needed.

pattern 4: VLOOKUP wrapped in ARRAYFORMULA

problem: a separate sheet maps Salesperson to Region. look up the region for every order.

=ARRAYFORMULA(VLOOKUP(C2:C, Lookup!A:B, 2, FALSE))

without the ARRAYFORMULA wrapper, VLOOKUP only handles a single value. wrapping it tells Sheets to apply VLOOKUP row by row.

note: ARRAYFORMULA + VLOOKUP recalculates slowly on big sheets. for 100k+ rows, consider INDEX/MATCH or QUERY instead.

pattern 5: text concatenation

problem: build a customer ID by combining Salesperson initials with region.

=ARRAYFORMULA(LEFT(C2:C,1) & "-" & D2:D)

& concatenation works directly inside ARRAYFORMULA. result: “S-APAC”, “M-EMEA”, etc.

pattern 6: nested IF for tiered logic

problem: tag each order with a customer tier based on revenue.

=ARRAYFORMULA(IF(G2:G="", "", IF(G2:G>5000, "platinum", IF(G2:G>1000, "gold", IF(G2:G>500, "silver", "bronze")))))

nested IFs work fine inside ARRAYFORMULA. for cleaner code on more than three tiers, use IFS:

=ARRAYFORMULA(IFS(G2:G="", "", G2:G>5000, "platinum", G2:G>1000, "gold", G2:G>500, "silver", TRUE, "bronze"))

pattern 7: dynamic running total

problem: a cumulative revenue column that builds up across rows.

=ARRAYFORMULA(IF(ROW(G2:G)=2, G2:G, MMULT(N(ROW(G2:G)>=TRANSPOSE(ROW(G2:G))), G2:G)))

this is the hairiest pattern in ARRAYFORMULA. MMULT multiplies a triangular matrix of 1s and 0s by the revenue column to compute running sums. memorize the shape, swap in your range.

simpler alternative: use the SCAN function (modern Sheets):

=SCAN(0, G2:G, LAMBDA(acc, x, acc+x))

SCAN is cleaner but requires Sheets’ newer LAMBDA functions. either approach yields a live cumulative column.

pattern 8: ARRAYFORMULA inside QUERY

problem: feed QUERY a calculated column without storing it permanently.

=QUERY({A2:G, ARRAYFORMULA(G2:G/F2:F)}, "SELECT Col1, Col4, Col8 WHERE Col8 > 100", 0)

the curly-brace array literal {} glues the original data with a calculated revenue-per-unit column. QUERY then references it as Col8. powerful for one-shot analysis without writing back to the sheet.

ARRAYFORMULA vs fill-down vs Apps Script

three ways to apply a formula across a column. when do you use each?

approach	best for	maintenance
ARRAYFORMULA	live calculation that auto-extends	one cell to fix any bug
fill-down	one-time calculation, static data	every new row needs manual extension
Apps Script onEdit trigger	complex logic that needs procedural code	requires script knowledge, harder to debug

for any column where the source data grows, prefer ARRAYFORMULA. for one-time analysis on a frozen dataset, fill-down is fine. for Google Apps Script, use only when ARRAYFORMULA cannot express the logic.

numbered walkthrough: build your first ARRAYFORMULA

we will build pattern 2 from scratch on the sales tracker.

open your data. confirm Revenue is in column G, rows 2-1001.
click into cell H1. type “Tier” as the header.
click cell H2. type =ARRAYFORMULA(.
type the IF logic: IF(G2:G="", "", IF(G2:G>1000, "high value", "low value")).
close the parenthesis. press Enter.
column H now fills with “high value” or “low value” for every row that has data.
add a new row at row 1002 with sample data. column H auto-extends to include it.
delete the formula in H2. all of H2:H1001 clears. only H2 holds the formula.

this is the durability ARRAYFORMULA buys you. one cell controls the whole column.

[SCREENSHOT: Sheets sheet showing column G with revenue values, column H with “high value” or “low value” labels filling all data rows, and the formula bar showing the ARRAYFORMULA in H2]

handling empty rows and bounded ranges

the most common ARRAYFORMULA bug: blank rows pollute the result with “0”, “FALSE”, or empty strings.

three ways to handle empty rows:

approach 1: explicit empty check

=ARRAYFORMULA(IF(G2:G="", "", G2:G * 0.1))

cleanest. always use this when the formula has any potential blank-row issue.

approach 2: bounded range

=ARRAYFORMULA(G2:G1001 * 0.1)

works if you know the data extent. fails when data grows beyond row 1001.

approach 3: dynamic LAST ROW

=ARRAYFORMULA(G2:INDEX(G:G, COUNTA(G:G)) * 0.1)

bounds the calculation to the last filled row of column G. cleaner than bounded but harder to read.

approach 1 is the daily driver. master that one and most ARRAYFORMULA pain disappears.

common ARRAYFORMULA mistakes and fixes

mistake 1: forgetting ARRAYFORMULA on a function that needs it

=VLOOKUP(C2:C, Lookup!A:B, 2, FALSE) returns just the first row’s lookup. wrap with ARRAYFORMULA to make it apply per row.

mistake 2: mixing ranges of different lengths

=ARRAYFORMULA(B2:B1000 + C2:C500) errors because the ranges are different sizes. always use ranges of identical length.

mistake 3: aggregating instead of vectorizing

=ARRAYFORMULA(SUM(G2:G)) returns a single number. ARRAYFORMULA does not vectorize SUM because SUM is by nature a reducer. use SUMIF or SUMIFS for per-row conditional sums.

mistake 4: double-wrapping nested ARRAYFORMULAs

=ARRAYFORMULA(ARRAYFORMULA(G2:G * 1.1))

works but is redundant. the outer ARRAYFORMULA is enough.

mistake 5: typing ARRAYFORMULA from scratch every time

shortcut: enter the formula without ARRAYFORMULA, then press Ctrl+Shift+Enter. Sheets wraps it for you. saves keystrokes and reduces typos.

conclusion: durable spreadsheets are array-formula spreadsheets

every spreadsheet you build is a small piece of infrastructure. fragility compounds: a formula that needs to be filled down is a formula that breaks every time someone forgets. ARRAYFORMULA removes that risk by making the formula self-extending.

start with pattern 1 — simple arithmetic — on one column you currently fill-down. swap it for an ARRAYFORMULA and watch the rest of the column populate automatically. once that is comfortable, move into pattern 2 (IF logic) and pattern 4 (VLOOKUP wrapper). by the third week of consistent use, you will stop using fill-down for anything that lives in a recurring report.

ARRAYFORMULA is also a gateway to writing more “functional” spreadsheets — formulas that describe relationships rather than procedures. that mindset transfers directly to SQL, dbt, Power Query, and any other declarative analytics tool.

next action: open a dashboard you maintain. count how many filled-down formulas live in it. pick one column and convert it to ARRAYFORMULA. add a new row to the source data. confirm the new row auto-populates. that one move is the start of a more durable spreadsheet practice.

Google Sheets ARRAYFORMULA: Complete Walkthrough (2026)