FormulaFix · Plain-English guides to Excel and Google Sheets formulas

How to Use ARRAYFORMULA in Google Sheets: A Complete Guide

1. Understanding ARRAYFORMULA and When to Use It

ARRAYFORMULA is a Google‑Sheets‑only function that tells a formula to work on whole ranges instead of a single cell. When you wrap a regular expression such as A2:A10*B2:B10 in ARRAYFORMULA, Sheets automatically expands the calculation down the column and returns an array of results.

Situation Typical non‑array approach With ARRAYFORMULA
Add two columns row‑by‑row =A2+B2 copied down =ARRAYFORMULA(A2:A+B2:B)
Pull the first word from a list of sentences =LEFT(A2, FIND(" ",A2)-1) copied down =ARRAYFORMULA(LEFT(A2:A, FIND(" ",A2:A)-1))
Flag rows that meet a condition =IF(C2="Yes",1,0) copied down =ARRAYFORMULA(IF(C2:C="Yes",1,0))

Why use ARRAYFORMULA?

  1. Speed & Simplicity – One formula replaces dozens of duplicated cells, reducing file size and making the sheet easier to audit.
  2. Dynamic ranges – Open‑ended ranges (e.g., A2:A) automatically include rows added later.
  3. No helper columns – Complex logic can be expressed in a single cell, keeping the worksheet tidy.

When NOT to use it

Pitfall Explanation
Mixing a single cell with a range A2:A*B1 multiplies every row by the one cell B1, which is fine if that is what you want. Use $B$1 so the reference is clear.
Heavy functions on very long ranges Open-ended ranges such as A2:A cover the whole column and can slow big sheets. Use a bounded range like A2:A5000 when you can.
SPLIT with a regex delimiter SPLIT only accepts plain‑text delimiters. Use REGEXREPLACE or REGEXEXTRACT first, then wrap the whole thing in ARRAYFORMULA.
Very large datasets (10 k+ rows) Even though ARRAYFORMULA is efficient, huge arrays can cause lag. Consider a separate “data‑processing” sheet or a filter view.

2. Basic Syntax and Simple Examples

The core syntax is straightforward:

=ARRAYFORMULA( expression )

2.1 Simple arithmetic across a column

A (Quantity) B (Unit Price)
2 5.00
4 3.75
1 12.00 
=ARRAYFORMULA(A2:A4 * B2:B4)

Result

C (Total)
10.00
15.00
12.00

2.2 Concatenating text

A (First Name) B (Last Name)
Alice Smith
Bob Jones
Carol Lee 
=ARRAYFORMULA(A2:A4 & " " & B2:B4)

Result

C (Full Name)
Alice Smith
Bob Jones
Carol Lee

2.3 Using a single‑value function on a range

A (Date)
2023‑01‑02
2023‑01‑03
2023‑01‑04 
=ARRAYFORMULA(TEXT(A2:A4, "dddd"))

Result

B (Weekday)
Monday
Tuesday
Wednesday

2.4 Nesting multiple functions

=ARRAYFORMULA(LEN(A2:A4 & " " & B2:B4))

Result

C (Length)
11
9
9

3. Combining ARRAYFORMULA with Other Functions

3.1 Arithmetic & logical operations

A (Qty) B (Unit Price)
2 15.00
5 9.80
1 22.50 
=ARRAYFORMULA(A2:A4 * B2:B4)

Result
30.00
49.00
22.50

3.2 Conditional calculations with IF

A (Score)
78
92
61
85 
=ARRAYFORMULA(IF(A2:A5 >= 80, "Pass", "Fail"))

Result
Fail
Pass
Fail
Pass

3.3 Summarising with SUMIF (or SUMIFS)

A (Region) B (Sales)
East 1200
West 950
East 430
North 710 
=ARRAYFORMULA(SUMIF(A2:A5, {"East","West","North"}, B2:B5))

Result
1630
950
710

3.4 Text manipulation with REGEXREPLACE

A (Raw)
John_Doe@example.com
jane.doe@sample.org
bob-smith@domain.net 
=ARRAYFORMULA(REGEXREPLACE(A2:A4, "([._-])", " "))

Result
John Doe@example.com
jane doe@sample org
bob smith@domain net

4. Common Pitfalls and How to Avoid Them

Pitfall Why it happens Fix
SPLIT with a regex delimiter SPLIT only accepts plain-text delimiters, so "\s+" is treated literally. Replace the pattern first, then split: =ARRAYFORMULA(SPLIT(REGEXREPLACE(A2:A, "\s+", "|"), "|"))
Forgetting ARRAYFORMULA around IF, VLOOKUP or REGEXMATCH These return one result for one cell unless the formula is in array mode. Wrap the whole formula: =ARRAYFORMULA(IF(B2:B>100,"High","Low"))
Cells already filled below the formula The array cannot expand and shows #REF! ("Array result was not expanded because it would overwrite data"). Clear the cells in the way, or move the formula to an empty column.
Header row inside the range Text in row 1 breaks math such as A1:A*B1:B. Start at row 2, or put the header inside the formula: ={"Total"; ARRAYFORMULA(A2:A*B2:B)}
Blank rows fill with 0 or errors Open-ended ranges like A2:A include empty rows. Test for blanks: =ARRAYFORMULA(IF(A2:A="",,A2:A*B2:B))
SUM or COUNTUNIQUE inside ARRAYFORMULA They collapse the range to one value, so they do not work row by row. Use them outside for a single total, e.g. =SUM(A2:A) or =COUNTUNIQUE(A2:A).

5. Advanced Techniques and Use Cases

5.1 Row totals without helper formulas

A (Qty) B (Price) C (Discount)
10 5.00 0.10
7 8.50 0.00
12 4.75 0.05 
=ARRAYFORMULA(IF(A2:A="",,A2:A*B2:B*(1-C2:C)))

Result: 45.00, 59.50, 54.15 (and nothing for empty rows).

5.2 Combine several columns into one label

A (First) B (Last) C (Dept)
John Doe Sales
Jane Smith HR
Bob Lee IT 
=ARRAYFORMULA(IF(A2:A="",,A2:A&" "&B2:B&" ("&C2:C&")"))

Result: John Doe (Sales), Jane Smith (HR), Bob Lee (IT).

5.3 Running total

A (Sales)
120
80
150
95 
=ARRAYFORMULA(MMULT(--(ROW(A2:A5)>=TRANSPOSE(ROW(A2:A5))), A2:A5))

Result: 120, 200, 350, 445. The comparison builds a grid of 1s and 0s that keeps only the current and earlier rows, and MMULT adds them up for each row.

5.4 Header and results from one formula

={"Full name"; ARRAYFORMULA(IF(A2:A="",,A2:A&" "&B2:B))}

Put this in C1. The header and the open-ended results come from a single cell, so new rows are picked up automatically.

5.5 FILTER does not need ARRAYFORMULA

=FILTER(A2:A, B2:B>=80)

FILTER, UNIQUE, SORT and QUERY already return arrays, so wrapping them in ARRAYFORMULA adds nothing.

6. FAQ

What is ARRAYFORMULA in Google Sheets?

ARRAYFORMULA is a function that forces a formula to operate on an entire range, returning a spill‑over array of results instead of a single value. It lets you replace many copy‑down formulas with one dynamic expression.

How do I use ARRAYFORMULA with a VLOOKUP?

Wrap the VLOOKUP call in ARRAYFORMULA and give it a range for the lookup value, e.g., =ARRAYFORMULA(VLOOKUP(A2:A, Sheet2!$A$2:$C$100, 3, FALSE)). The formula returns the matching column for each row in A2:A. Without ARRAYFORMULA, VLOOKUP would return only one result.

Can ARRAYFORMULA be used with conditional logic?

Yes. Place any conditional function—IF, IFS, SWITCH, or even COUNTIFS—inside ARRAYFORMULA. For example, =ARRAYFORMULA(IF(B2:B>100, "High", "Low")) evaluates the condition for every cell in B2:B and returns a parallel array of “High” or “Low”.

Tip: Whenever you use ARRAYFORMULA, keep the target column free of other formulas and prefer open‑ended ranges (e.g., A2:A) so new rows are automatically included. This practice maximises the benefits of dynamic, single‑cell calculations.

Related guides