Google Sheets ARRAYFORMULA: The Complete Guide
ARRAYFORMULA is the single most important function in Google Sheets. Write one formula; it fills a whole column, forever. No dragging, no copying, no breaks when new data arrives. This guide covers everything — syntax, patterns, tricky cases, performance, and how to migrate from Excel dynamic arrays.
Download the companion workbook
Every example in this guide, pre-built. Open in Google Sheets or Excel.
What's in this guide
What ARRAYFORMULA does
ARRAYFORMULA tells Google Sheets: "apply this formula to the entire range at once, and spill the results into neighboring cells." One formula; many outputs.
Without ARRAYFORMULA, you'd write =A2*B2 in row 2, then drag down to row 1000. Every time you add a new row, you drag again. With ARRAYFORMULA:
One formula in C2. Fills C2:C down as far as A and B have data. New rows automatically get the calculation. Edit the logic once, every row updates.
Why it matters
- Zero maintenance — new rows just work
- Faster sheets — one formula calculation instead of thousands
- Single source of truth — edit once, applies everywhere
- Cleaner audit — one cell with logic, not a mystery column of pasted formulas
Syntax & the shortcut
You wrap any formula that operates on ranges in ARRAYFORMULA(). The ranges can be columns (A2:A), rectangular ranges (A2:D100), or arithmetic involving them.
The keyboard shortcut
Instead of typing ARRAYFORMULA manually, press Ctrl+Shift+Enter (Cmd+Shift+Enter on Mac) after writing a range-based formula. Sheets wraps it automatically.
Shortcut demo
Type: =A2:A * B2:B
Press Ctrl+Shift+Enter instead of Enter → becomes =ARRAYFORMULA(A2:A*B2:B)
The basic patterns
Multiplication across columns
Multiplies every row of A by every row of B. Blank rows return blank (not zero).
Addition with a constant
Adds 100 to every value in column A.
Concatenation
Joins first name (column A) and last name (column B) with a space.
Percentage calculation
Divides A by B, substitutes 0 for divide-by-zero errors.
ARRAYFORMULA with IF
IF is one of the most useful functions to combine with ARRAYFORMULA — but it has a trick.
Basic IF across a column
Returns "High" for every row where A is over 1000, "Low" otherwise.
Handling blank rows
The formula above returns "Low" even for empty rows, which is ugly. Use IF to check for blanks first:
Nested IF: if blank, return blank; otherwise apply the real logic.
Multiple conditions with IFS
With VLOOKUP & XLOOKUP
Standard VLOOKUP looks up one value. Wrap it in ARRAYFORMULA to look up an entire column.
VLOOKUP across a column
Looks up every value in A against the Lookup sheet, returns column 2. Any no-matches show "Not found" instead of #N/A.
XLOOKUP with array
Cleaner than VLOOKUP — built-in if_not_found parameter, left-of-key lookups work, and no column-index counting.
With text functions
Text functions like UPPER, LOWER, TRIM, LEFT, RIGHT, MID, SUBSTITUTE all work beautifully with ARRAYFORMULA.
Uppercase a whole column
Extract first word
The & " " prevents FIND from erroring on single-word entries with no space.
Email domain extraction
Phone number cleanup
Strips every non-digit character from a column of messy phone numbers.
With SUMIF & SUMIFS
SUMIF is a classic non-array function that ARRAYFORMULA makes much more powerful.
Running total for each row
Each row shows the cumulative sum of column A up to that row.
Category-wise sum
For every row, sums column C where column B matches. Great for "total revenue for this customer" columns.
SUMIFS with ARRAYFORMULA has edge cases where it doesn't work as expected. If you hit weird results, use SUMPRODUCT or wrap each criterion in an array context carefully. For most cases, SUMIF + array is more reliable than SUMIFS + array.
With COUNTIF & COUNTIFS
Count occurrences of each value
For every row, shows how many times that value appears in column A total. Useful for duplicate flagging.
Flag duplicates
Dynamic ranges
The real power: formulas that extend forever. Use A2:A instead of A2:A1000 — Sheets reads all the way to the bottom.
Open-ended ranges
A2:A— column A from row 2 to the bottomA:A— all of column A (including the header row)A2:C— columns A, B, C from row 2 to the bottom
Why open-ended is better
With a fixed range like A2:A1000, your formula stops working at row 1001. With A2:A, it keeps going forever. Set-and-forget.
Open-ended ranges scan the whole column, which can slow large sheets. For million-row datasets, sometimes a fixed upper bound (A2:A10000) is faster. Most sheets don't need the optimization.
Handling headers
ARRAYFORMULA fills cells starting at the formula location. If you put it in row 2, it leaves row 1 for a header.
Including the header in the formula
The curly braces stack the header string on top of the array result. Put this in row 1 (B1 or wherever), and the whole column — header included — comes from one formula.
Why this is useful
- Header can't accidentally be overwritten by someone editing the column
- Column structure is self-documenting
- Easier to move formulas around — header travels with the logic
Performance
When ARRAYFORMULA is fast
- Simple arithmetic on columns (A*B, A+100)
- Text operations (UPPER, TRIM, concatenation)
- Replacing 1000 dragged-down formulas with one wrapped version
When it can slow down
- Open-ended ranges on very large sheets (1M+ rows)
- Nested VLOOKUPs in ARRAYFORMULA — each row triggers a lookup
- Complex SUMIFS/COUNTIFS across large criteria ranges
- INDIRECT inside ARRAYFORMULA — kills optimization
Tips
- Cap ranges when you know the size (A2:A50000 instead of A2:A)
- Use QUERY for aggregation instead of SUMIFS wrapped in ARRAYFORMULA
- Avoid nested ARRAYFORMULA — one wrapper is enough
- Pre-filter with FILTER if you only need a subset
Excel dynamic arrays equivalent
Modern Excel (365, 2021+) doesn't have ARRAYFORMULA but has native dynamic arrays. Formulas spill automatically.
| Google Sheets | Excel 365/2021 |
|---|---|
=ARRAYFORMULA(A2:A*B2:B) | =A2:A100*B2:B100 (spills) |
=ARRAYFORMULA(IF(A2:A>100,"Y","N")) | =IF(A2:A100>100,"Y","N") |
=ARRAYFORMULA(UPPER(A2:A)) | =UPPER(A2:A100) |
| Open-ended A2:A | Must specify upper bound in Excel |
Excel needs an explicit range (A2:A100) — it won't accept A2:A. Dynamic arrays spill automatically with no wrapper. That's the trade: Excel is more explicit, Sheets is more automatic.
Common pitfalls
If you write =ARRAYFORMULA(A2 * B2), you get one value. You need ranges: =ARRAYFORMULA(A2:A * B2:B).
QUERY, FILTER, SORT, UNIQUE already return arrays — wrapping them in ARRAYFORMULA does nothing. GOOGLETRANSLATE and similar don't work with ARRAYFORMULA — they ignore it and return only the first value.
Empty cells become 0 in math contexts. Wrap in IF(A2:A="", "", ...) to preserve blanks visually.
Once a column is filled by ARRAYFORMULA, you can't type into those cells. You get a "#REF! Array result was not expanded because it would overwrite data" error. To change a value, edit the formula logic or temporarily break the array.
If the ARRAYFORMULA lives in row 2 and someone deletes rows 1-2 to "clean up", the formula is gone. Protect the formula row or use the header-included version with curly braces.
INDIRECT prevents Sheets from optimizing the calculation. If your ARRAYFORMULA is slow, check for INDIRECT and replace with a direct reference.
Write ARRAYFORMULA without the syntax struggle
Describe what you want ("calculate total for each row as quantity times price, skip blank rows") and Sheets Wizard generates the ARRAYFORMULA — with blank handling, error catching, and headers already wired in.
Install Sheets Wizard →Download the companion workbook
Every example in this guide, ready to open and modify.
Frequently asked questions
What does ARRAYFORMULA do in Google Sheets?
Lets one formula work on entire ranges at once. Instead of copying a formula down 1000 rows, write it once wrapped in ARRAYFORMULA() and it fills the whole column.
When should I use ARRAYFORMULA vs dragging a formula down?
Use ARRAYFORMULA when the data grows — new rows automatically get the formula. Dragging requires manual re-drag. Also use it when you want one editable formula instead of 1000 copies.
What's the keyboard shortcut for ARRAYFORMULA?
Ctrl+Shift+Enter (Cmd+Shift+Enter on Mac) automatically wraps your formula in ARRAYFORMULA() when pressed instead of plain Enter.
Why does my ARRAYFORMULA return only one value?
Usually because you used a single-cell reference instead of a range. Use A2:A instead of A2 for the formula to spill across all rows.
Does ARRAYFORMULA work in Excel?
No. Excel uses dynamic arrays instead — modern Excel formulas spill naturally without a wrapper. Migrate by removing ARRAYFORMULA and letting the formula spill.