The Complete ARRAYFORMULA Function Guide for Google Sheets (2026) — One Formula, Entire Column
Home› Google Sheets› Guides› ARRAYFORMULA Complete Guide
Google Sheets — Native Function

The complete ARRAYFORMULA guide for Google Sheets (2026)

ARRAYFORMULA is the function that transforms how you build Google Sheets. Instead of dragging =A2*B2 down 500 rows, one ARRAYFORMULA in a single cell handles the whole column — automatically expanding as new data arrives. This is the complete guide: what ARRAYFORMULA does, the six patterns you'll use constantly (arithmetic, concatenation, IF logic, VLOOKUP, aggregation, dates), common errors, and how it compares to Excel's dynamic arrays. Free companion workbook included.

📊 Free companion workbook

All examples use the same sample datasets. Open in Google Sheets and follow along with the ArrayFormula_Reference sheet.

Open Workbook on Drive

What is ARRAYFORMULA?

ARRAYFORMULA is a Google Sheets function that lets a single formula operate on entire ranges instead of one cell at a time. It's the mechanism that transforms most single-cell functions into "array-aware" versions that spill their results across multiple cells.

The concept is simple but transformative. In a traditional spreadsheet, you write a formula like =A2*B2, then drag it down 500 rows to apply the same calculation to every row. Every one of those 500 cells contains a separate formula. Adding a 501st row of data means dragging again — or worse, forgetting and shipping a broken sheet.

With ARRAYFORMULA, you write the formula once. In a single cell. It applies to every row automatically:

Cell H2 =ARRAYFORMULA(
D2:D26 * E2:E26)
→
Spills into
H2:H26
$897
$998
$3,000
$395
$598
... 20 more rows

One cell holds the formula. The other 24 cells display calculated values, but they contain nothing themselves — they're outputs of the array formula in H2. Edit them and Sheets will refuse. Delete H2 and all 25 values disappear together. This is fundamentally different from copy-paste and is the mental model you need to internalize.

💡 Why ARRAYFORMULA is a superpower

Traditional spreadsheets have one formula per cell. That model breaks down when you have 10,000 rows and 20 calculated columns — 200,000 individual formulas that all need maintaining. ARRAYFORMULA reduces that to 20 formulas, one per calculated column. Change the logic in one place and every row updates. Add new data and the formulas expand automatically. This is why serious Sheets workbooks use ARRAYFORMULA everywhere.

The problem it solves

To appreciate ARRAYFORMULA, look at what it replaces. Here's the same "calculate total = quantity × price" for 500 sales rows, two ways:

❌ The old way (drag-down)

Type =D2*E2 in cell H2. Drag down to H501. Now 500 individual formulas exist. Add row 502? Drag again. Change the formula? Retype in every cell, or edit one and drag again to overwrite.

500 formulas. Manual maintenance. Breaks with new data.

✓ The ARRAYFORMULA way

Type =ARRAYFORMULA(D2:D501*E2:E501) in cell H2. Done. Add row 502? Extend the range to D502 and E502 — one edit, everywhere. Change the calculation? Edit one formula.

1 formula. Self-maintaining. Trivial to update.

The maintainability advantage compounds across a real workbook. A sales dashboard with 15 calculated columns × 5,000 rows contains 75,000 formulas in the drag-down model versus 15 formulas with ARRAYFORMULA. When your CFO wants the tax rate updated from 22% to 24%, the drag-down model requires editing 5,000 cells (or dragging over 5,000 cells). The ARRAYFORMULA model requires editing one.

Anatomy of ARRAYFORMULA

ARRAYFORMULA has the simplest possible syntax — one argument:

=ARRAYFORMULA(formula_that_operates_on_ranges)

You take any formula that would normally operate on single cells and pass it ranges instead. ARRAYFORMULA handles the expansion. The three key changes to remember:

1. Use ranges, not single cells. Change A2 to A2:A500. Change B2 to B2:B500. The ranges must be the same length for element-wise operations like multiplication or comparison.

2. Wrap in ARRAYFORMULA(...). This tells Google Sheets to process the formula as an array operation. Without the wrapper, most formulas silently return only the first result.

3. Place in the top-left of the output area. The formula lives in one anchor cell. Results spill downward (and sometimes rightward) into blank cells below and to the right. If any cell in the spill area is occupied, you get #REF! until you clear it.

Not all functions need ARRAYFORMULA

Some Sheets functions natively handle arrays — SUMIF, COUNTIF, FILTER, SORT, UNIQUE, QUERY, and most aggregations. Wrapping these in ARRAYFORMULA usually has no effect. The wrapper is needed for arithmetic operators (+, -, *, /), comparison operators, IF, IFS, VLOOKUP, HLOOKUP, and most functions originally designed for single cells. See Section 13 for the full list.

The Ctrl+Shift+Enter shortcut

You rarely type =ARRAYFORMULA( manually. Instead:

Type your formula normally with ranges — for example, =D2:D26*E2:E26. Instead of pressing Enter, press Ctrl + Shift + Enter (or Cmd + Shift + Enter on Mac). Google Sheets automatically wraps your formula in ARRAYFORMULA(...) — much faster than typing the wrapper by hand.

Press the same combination again on an already-wrapped formula and Sheets removes the ARRAYFORMULA wrapper. It's a toggle. This shortcut alone probably saves seasoned Sheets users an hour a week.

Our sample datasets

Every example in this guide uses one of two sample datasets from the companion workbook. Here's the Employees sheet:

Employees — first 5 of 30 rows
A: Emp_ID B: First_Name C: Last_Name D: Department E: Salary F: Bonus_Pct G: Tax_Rate H: Hire_Date
E001AliKhanEngineering$95,00010.0%28.0%2020-05-14
E002SaraAhmedSales$120,00015.0%32.0%2019-11-03
E003JamesMillerMarketing$78,0008.0%24.0%2021-08-22
E004LisaWongProduct$145,00018.0%35.0%2018-03-11
E005DavidChenFinance$88,00010.0%28.0%2022-01-30

All formulas reference these ranges. The Departments sheet contains a lookup table matching department names to budgets, managers, and locations. The Sales_Rows sheet is a simple Quantity × Price dataset for the arithmetic pattern.

Pattern 1

Column-wide arithmetic

The simplest ARRAYFORMULA pattern: apply an arithmetic operation to every row of one or more columns. This is the "Quantity × Price = Total" pattern that appears in every sales workbook.

Total = Quantity × Price for every row (Sales_Rows sheet)
=ARRAYFORMULA(D2:D26 * E2:E26)
Result25 total values spill into cells F2:F26 (or wherever you place the formula)
Quantity (D)Unit Price (E)Total (calculated)
3$299$897
2$499$998
4$750$3,000
5$79$395

ARRAYFORMULA performs the multiplication row-by-row: D2×E2, D3×E3, D4×E4, and so on, spilling all 25 results into the destination cells. If you extend the source data to row 100, just update the ranges to D2:D100 and E2:E100 — one edit, entire column recalculates.

Any arithmetic operator works: multiplication, addition, subtraction, division, exponentiation. You can combine them with parentheses for complex row-wise calculations. Here's a discount calculation:

Apply a 10% discount to every price
=ARRAYFORMULA(E2:E26 * 0.9)
ResultEvery price reduced to 90% of its original value

Notice you can mix ranges with single values (constants). ARRAYFORMULA broadcasts the constant across every row — no need to have a matching range of 25 copies of 0.9.

Pattern 2

Concatenation across columns

Combining text from multiple columns into a single output — full names from first + last, addresses from separate street/city/state, email addresses generated from names. The ampersand operator (&) is your primary tool.

Full name = First + Space + Last (Employees sheet)
=ARRAYFORMULA(B2:B31 & " " & C2:C31)
Result30 full names spilled into a single column
First (B)Last (C)Full Name (calculated)
AliKhanAli Khan
SaraAhmedSara Ahmed
JamesMillerJames Miller

The & operator concatenates strings. ARRAYFORMULA broadcasts the constant " " across every row. Extending this pattern to build professional email addresses:

Generate email addresses from names
=ARRAYFORMULA(LOWER(B2:B31) & "." & LOWER(C2:C31) & "@company.com")
ResultEmails like ali.khan@company.com, sara.ahmed@company.com — for every employee at once

Notice how LOWER() works inside ARRAYFORMULA — it applies to the entire range and returns a lowercased array. Most single-cell text functions (LOWER, UPPER, PROPER, LEFT, RIGHT, MID, LEN, TRIM) become array-aware when wrapped in ARRAYFORMULA.

Pattern 3

Conditional operations with IF

Applying IF logic across an entire column is where ARRAYFORMULA becomes indispensable. Tier assignments, status flags, conditional calculations — anything that would traditionally require dragging =IF(...) down 500 rows.

Assign a seniority tier based on salary
=ARRAYFORMULA(IF(E2:E31>=120000, "Senior", IF(E2:E31>=80000, "Mid", "Junior")))
ResultEach employee gets a tier label based on their salary — nested IF handles three tiers
SalaryTier (calculated)
$95,000Mid
$120,000Senior
$78,000Junior
$145,000Senior

The nested IF pattern works exactly like it does in a single cell — but now applied to every row of the range simultaneously. The comparison E2:E31>=120000 produces an array of TRUE/FALSE values, which IF then uses to pick between the true and false branches for each row.

Calculate net salary (after tax) plus bonus
=ARRAYFORMULA((E2:E31 * (1 - G2:G31)) + (E2:E31 * F2:F31))
ResultCombined "after-tax salary + bonus amount" calculated for every employee in one formula

This single formula does what would take three helper columns (bonus amount, after-tax salary, net total) in the drag-down approach. All the arithmetic happens element-wise across all 30 rows.

IF requires ARRAYFORMULA

Bare IF does not naturally handle arrays — writing =IF(A2:A100>5, "High", "Low") without ARRAYFORMULA returns only the first result. Always wrap IF (and its cousins IFS, IFERROR, IFNA) in ARRAYFORMULA when the goal is column-wide conditional logic.

Pattern 4

VLOOKUP inside ARRAYFORMULA

Arguably the most-requested ARRAYFORMULA pattern. Instead of dragging =VLOOKUP(A2, LookupTable, 2, FALSE) down hundreds of rows, one ARRAYFORMULA-wrapped VLOOKUP handles the whole column:

Look up department budget for every employee
=ARRAYFORMULA(VLOOKUP(D2:D31, Departments!A:B, 2, FALSE))
ResultEvery employee gets their department's budget joined in
Employee Dept (D)Dept Budget (looked up)
Engineering$2,500,000
Sales$1,800,000
Marketing$1,200,000
Product$2,200,000

The magic: VLOOKUP's first argument becomes a range instead of a single value. ARRAYFORMULA tells VLOOKUP to perform 30 individual lookups (one per row) and return an array of 30 matched values. What would take 30 formulas (or a dragged formula) collapses into one.

You can chain multiple lookups by adding more columns of ARRAYFORMULA-wrapped VLOOKUPs, each pulling a different column from the lookup table (budget in column 2, manager in column 3, location in column 4). Together they build a fully joined table without any actual joining.

ARRAYFORMULA with INDEX/MATCH and XLOOKUP

The same pattern works with INDEX/MATCH and XLOOKUP. =ARRAYFORMULA(INDEX(Departments!B:B, MATCH(D2:D31, Departments!A:A, 0))) is the INDEX/MATCH equivalent. Sheets' XLOOKUP also plays well with ARRAYFORMULA: =ARRAYFORMULA(XLOOKUP(D2:D31, Departments!A:A, Departments!B:B)). Pick whichever lookup function your team standardizes on.

Pattern 5

SUMIF / COUNTIF wrapped

SUMIF and COUNTIF technically don't need ARRAYFORMULA to work — they already accept ranges. But wrapping them in ARRAYFORMULA unlocks a powerful pattern: a per-row aggregate calculated once for every row. Perfect for "what's my department's total salary?" or "how many people are in my department?" as a column beside each employee.

Total salary of each employee's department (per row)
=ARRAYFORMULA(SUMIF(D:D, D2:D31, E:E))
ResultEvery row shows the sum of salaries for that row's department. All 30 rows compute in one formula.

SUMIF normally returns a single number when given a single criterion. When ARRAYFORMULA passes it an array of criteria (D2:D31), SUMIF runs 30 times — once per employee — returning an array of 30 department totals. Employees in the same department show identical values.

The COUNTIFS variant is equally useful for building rank columns — showing where each row sits within its group:

Rank each employee within their department by salary
=ARRAYFORMULA(COUNTIFS(D:D, D2:D31, E:E, ">"&E2:E31) + 1)
ResultEach employee's rank within their department. Highest salary in each dept = rank 1.
Pattern 6

Date operations at scale

Date functions like DATEDIF, MONTH, YEAR, TEXT, and WEEKDAY all become array-aware inside ARRAYFORMULA. Useful for computing tenure, extracting date parts, or building formatted date labels for entire columns.

Years of service for every employee (from Hire_Date)
=ARRAYFORMULA(DATEDIF(H2:H31, TODAY(), "Y"))
Result30 tenure values calculated in years, updating automatically as time passes
Extract the hire month name for grouping
=ARRAYFORMULA(TEXT(H2:H31, "mmmm"))
Result"May", "November", "August" — full month names for every hire date

TEXT is particularly powerful inside ARRAYFORMULA because it accepts any date format code. "mmmm yyyy" gives "May 2020". "ddd mmm dd" gives "Thu May 14". "Q" & ROUNDUP(MONTH(...)/3) gives quarter labels. Once you master TEXT + ARRAYFORMULA, formatting dates for dashboards becomes trivial.

A common frustration: your ARRAYFORMULA works perfectly for data rows but has an ugly technical name at the top instead of a clean header. The solution is a nested IF that checks the row number and returns a header text for row 2 (assuming your data starts at row 2):

=ARRAYFORMULA( IF(ROW(A2:A31)=2, "Full Name", IF(A2:A31="", "", B2:B31 & " " & C2:C31)))

Three things happen here. First, ROW(A2:A31) returns an array of row numbers (2, 3, 4, ..., 31). The outer IF checks each: if row = 2, return "Full Name" (the header). Otherwise, the inner IF checks if the row is empty (A is blank), and if so returns empty — preventing hundreds of "Zain " strings from populating below the real data. Only if both checks pass does it compute the concatenation.

This pattern shows up throughout professional Sheets workbooks. Master it and your ARRAYFORMULA outputs look like they belong in a polished dashboard rather than a debugging session.

Empty-safe ARRAYFORMULA is a habit worth building

Wrapping your calculations in IF(A2:A31="", "", ...) prevents the formula from populating empty rows with zeros, "N/A", or garbage. When you extend your data range far beyond current rows (e.g., A2:A1000 to accommodate future growth), this pattern keeps unused rows blank until real data arrives. Non-negotiable for professional Sheets workbooks.

When you DON'T need ARRAYFORMULA

Not every formula benefits from ARRAYFORMULA. Some functions are already array-aware. Wrapping them in ARRAYFORMULA usually does nothing (or causes errors). Here's the reference:

Function typeNeeds ARRAYFORMULA?Examples
Arithmetic operators✓ YesA2:A10*B2:B10
Comparison operators✓ YesA2:A10>100
IF, IFS, IFERROR, IFNA✓ YesIF(A2:A10>0, "Yes", "No")
Text functions (LOWER, UPPER, LEFT, etc.)✓ YesLOWER(A2:A10)
VLOOKUP, HLOOKUP, INDEX, XLOOKUP✓ YesVLOOKUP(A2:A10, ...)
Date functions (DATEDIF, TEXT, etc.)✓ YesDATEDIF(A2:A10, TODAY(), "Y")
Aggregations (SUM, COUNT, MAX, MIN, AVG)✗ NoNative array support
Conditional aggregates (SUMIF, COUNTIF, SUMIFS)△ SometimesOnly when you want per-row aggregates
FILTER✗ NoNative array output
SORT✗ NoNative array output
UNIQUE✗ NoNative array output
QUERY✗ NoReturns full array natively
ARRAYFORMULA itselfN/ANever wrap ARRAYFORMULA in another ARRAYFORMULA

The pattern: functions that were designed for spreadsheet ranges (aggregations, filtering, sorting, querying) already know how to work with arrays. Functions that were designed for single values (arithmetic, IF, lookups, text/date processing) need the ARRAYFORMULA wrapper to be applied across ranges.

Common ARRAYFORMULA errors and fixes

ARRAYFORMULA errors are usually one of four types. Here's what they mean and how to fix them:

ErrorWhat it meansCommon cause and fix
#REF! Result would overwrite existing data A cell in the spill area contains data. Clear it. Or move the formula. ARRAYFORMULA needs blank space to spill into.
"Result was not automatically expanded, please insert more rows" Not enough blank rows below Add empty rows at the bottom of the sheet, or shorten the range you're passing to ARRAYFORMULA.
#VALUE! Ranges are different sizes Element-wise operations need matching range sizes. A2:A10*B2:B20 won't work because one is 9 rows and the other 19. Match the ranges.
Only one value returned Formula returned a single result instead of an array You probably passed single cells (A2) instead of ranges (A2:A100). Or the inner function doesn't support arrays natively — try IF wrapping or check the function's documentation.
#N/A in some rows VLOOKUP couldn't find matches for some values Wrap with IFERROR or IFNA: =ARRAYFORMULA(IFERROR(VLOOKUP(...), "Not found"))
The "empty rows populate with zeros" trap

Arithmetic ARRAYFORMULAs treat empty cells as 0. If your source range extends to row 1000 but only rows 2-31 have data, rows 32-1000 will show zeros in the output column. Fix with an empty-check wrapper: =ARRAYFORMULA(IF(A2:A1000="", "", D2:D1000*E2:E1000)). This one habit dramatically improves how your Sheets workbooks look and behave.

ARRAYFORMULA vs Excel dynamic arrays

Both Google Sheets ARRAYFORMULA and Excel 365 dynamic arrays solve the same fundamental problem — "how do I apply a formula to many rows at once?" — but with different syntax and different scopes. For dual-platform teams, understanding the difference matters.

AspectGoogle Sheets ARRAYFORMULAExcel 365 Dynamic Arrays
Syntax Wrap formula in =ARRAYFORMULA(...) No wrapper needed — formulas spill automatically
Shortcut Ctrl+Shift+Enter to auto-wrap Just Enter — spilling is default
Available in All Google Sheets versions Excel 365, Excel 2021+, Excel for Web
Function coverage Works with most single-cell functions when wrapped Only functions Microsoft has explicitly updated (FILTER, SORT, UNIQUE, XLOOKUP, etc.)
Spill indication No visual indicator — output cells look normal Blue "spill" border around output range
Backwards compatibility Older Sheets always supported ARRAYFORMULA Breaks in Excel 2019 and earlier
The @ operator Not applicable Use @ to force single-cell result: =@A2:A10

Practical implication: Sheets workbooks using ARRAYFORMULA don't translate cleanly to Excel and vice versa. Moving between platforms usually requires manually rewriting formulas. This is one of the most significant friction points in dual-platform teams and one of the biggest reasons ARRAYFORMULA is worth learning deeply — it's a genuinely Sheets-only skill.

Performance considerations

ARRAYFORMULA is generally faster than dragging formulas down, but there are edge cases. Understanding when it slows down helps you build workbooks that stay responsive as they grow.

Small ranges (under 1,000 rows): No meaningful difference. Use whichever is more maintainable — usually ARRAYFORMULA.

Medium ranges (1,000–10,000 rows): ARRAYFORMULA is often faster because Sheets processes the whole range in a single pass rather than 10,000 individual recalculations.

Large ranges (10,000–50,000 rows): Performance depends on the inner function. Arithmetic ARRAYFORMULAs remain fast. VLOOKUP-based ARRAYFORMULAs slow down noticeably — every row triggers a lookup against the entire lookup table. Consider structuring your data with QUERY or joining upstream.

Very large ranges (100,000+ rows): Sheets starts to lag regardless of technique. Consider BigQuery connectors, Sheets' data connectors, or splitting the data across multiple sheets.

The maintainability benefit almost always outweighs performance considerations. A workbook that's 20% slower but has 15 formulas instead of 150,000 is much easier to trust, debug, and hand off to colleagues.

Frequently asked questions

What does ARRAYFORMULA do in Google Sheets?

ARRAYFORMULA lets a single formula operate on entire ranges instead of one cell at a time. Instead of dragging =A2*B2 down 500 rows, you write =ARRAYFORMULA(A2:A500*B2:B500) in one cell and the results spill across 499 rows automatically. It transforms most non-array functions into array-aware ones, which is essential for maintainable Sheets workbooks.

Does ARRAYFORMULA work in Microsoft Excel?

No. ARRAYFORMULA is native to Google Sheets. Excel 365 has dynamic arrays which achieve similar results but with completely different syntax — Excel formulas automatically spill without needing a wrapper function. Older Excel versions used Ctrl+Shift+Enter array formulas which are closer in concept but limited. When moving workbooks between Sheets and Excel, ARRAYFORMULA is one of the biggest compatibility gaps.

What is the keyboard shortcut for ARRAYFORMULA?

Type your formula normally, then press Ctrl+Shift+Enter (Cmd+Shift+Enter on Mac). Google Sheets automatically wraps the formula in ARRAYFORMULA. This is significantly faster than typing =ARRAYFORMULA( manually. The same shortcut also toggles ARRAYFORMULA off if you press it again on an already-wrapped formula.

Why is my ARRAYFORMULA only returning one value?

The most common cause is passing a single cell reference instead of a range. =ARRAYFORMULA(A2*B2) returns one value because A2 and B2 are single cells. Change to =ARRAYFORMULA(A2:A100*B2:B100) to operate across ranges. Another cause: the inner function may not support arrays — try wrapping in an IF or checking the function's Google documentation for array-compatibility.

Can I use VLOOKUP with ARRAYFORMULA?

Yes, this is one of the most valuable ARRAYFORMULA patterns. Instead of =VLOOKUP(A2, LookupTable, 2, FALSE) dragged down 500 rows, use =ARRAYFORMULA(VLOOKUP(A2:A500, LookupTable, 2, FALSE)) in a single cell. The search key becomes a range and VLOOKUP returns a spilled array of matching results. Works with any lookup function including HLOOKUP, INDEX/MATCH, and XLOOKUP.

Which functions do NOT need ARRAYFORMULA?

Functions that already handle arrays natively don't need ARRAYFORMULA. This includes SUMIF, SUMIFS, COUNTIF, COUNTIFS, FILTER, SORT, UNIQUE, QUERY, MAX, MIN, AVERAGE, and most aggregation functions. If a function already accepts and processes ranges, wrapping it in ARRAYFORMULA usually has no effect. Wrapping is needed for arithmetic operators, IF, VLOOKUP, and functions that were originally designed for single cells.

How do I add a header row to an ARRAYFORMULA output?

Use an IF that checks the row number: =ARRAYFORMULA(IF(ROW(A2:A100)=2, "Header Text", IF(A2:A100="", "", B2:B100*C2:C100))). The formula returns 'Header Text' for the first row (row 2) and the calculated value for subsequent rows. Wrap in another IF to handle empty rows gracefully so the ARRAYFORMULA doesn't populate hundreds of blank rows.

Is ARRAYFORMULA slower than dragging a formula down?

For small ranges (under 1,000 rows) the performance difference is negligible. For very large ranges (10,000+ rows), ARRAYFORMULA is often faster because Sheets processes the entire range in one calculation pass instead of 10,000 individual recalculations. The bigger benefit is maintainability — one formula updates instead of 10,000, and adding new rows doesn't require dragging.

Can ARRAYFORMULA output spill into multiple columns?

Yes. If your inner formula returns multiple columns (like VLOOKUP with a column range or a function that returns arrays), ARRAYFORMULA spills across both rows and columns. For example, =ARRAYFORMULA(VLOOKUP(A2:A10, LookupTable, {2,3,4}, FALSE)) returns 3 columns of matched values spilling into a 3-column × 9-row area from the single anchor cell.

What is the difference between ARRAYFORMULA and SPILL in Excel?

Excel 365 introduces dynamic arrays where formulas automatically spill without needing a wrapper — =A2:A100*B2:B100 works directly. Google Sheets requires the ARRAYFORMULA wrapper for many operations. The end result is similar (formula in one cell, output spills across many) but the syntax and range of supported functions differs. Google's ARRAYFORMULA is more explicit; Excel's dynamic arrays are more automatic but only work with functions Microsoft has updated.

Related resources

· · ·

Bottom line: ARRAYFORMULA turns 500 formulas into 1. Master the six patterns — arithmetic, concatenation, IF, VLOOKUP, aggregation, dates — and every Google Sheets workbook becomes dramatically more maintainable, faster to build, and easier to hand off.