How It Works

ARRAYFORMULA applies a formula to an entire range at once, automatically expanding results. Instead of copying a formula down 1000 rows, one array formula handles everything - and auto-extends when you add new data.

Syntax

=ARRAYFORMULA(expression)

The expression operates on ranges instead of single cells.

Basic Example

Without Array Formula: Cell C2: =A2*B2 (Copy down to C3, C4, C5, etc.)

With Array Formula: Cell C2: =ARRAYFORMULA(A2:A*B2:B) (Automatically fills entire column)

Financial Applications

Running Balance

Traditional (copy down each row):

=E2+C3-D3

Array formula (one formula, infinite rows):

=ARRAYFORMULA(IF(A2:A="", "",
  starting_balance +
  SUMIF(ROW(A2:A), "<="&ROW(A2:A), C2:C) -
  SUMIF(ROW(A2:A), "<="&ROW(A2:A), D2:D)))

Auto-Calculate Category Totals

Category	Jan	Feb	Mar	Total
Food	450	420	480	=ARRAYFORMULA(SUM(B2:D2))

One formula populates all Total cells:

=ARRAYFORMULA(IF(A2:A="", "", MMULT(B2:D, {1;1;1})))

Percentage of Budget

Instead of =C2/B2 copied down:

=ARRAYFORMULA(IF(B2:B="", "", C2:C/B2:B))

Conditional Calculations

IF with Arrays

=ARRAYFORMULA(IF(A2:A="", "", IF(C2:C>B2:B, "Over", "Under")))

Multiple Conditions

=ARRAYFORMULA(IF(A2:A="", "",
  IF(C2:C/B2:B>1, "Over Budget",
  IF(C2:C/B2:B>0.8, "Warning", "OK"))))

IFS with Arrays

=ARRAYFORMULA(IFS(
  A2:A="", "",
  C2:C/B2:B>1, "Over",
  C2:C/B2:B>0.8, "Near",
  TRUE, "Good"))

FILTER: Dynamic Filtered Lists

Expenses Over $100

=FILTER(A:E, E:E<-100)

This Month’s Transactions

=FILTER(A:E, MONTH(A:A)=MONTH(TODAY()), YEAR(A:A)=YEAR(TODAY()))

Specific Category

=FILTER(A:E, C:C="Groceries")

Multiple Criteria

=FILTER(A:E, C:C="Food", E:E<-50)

UNIQUE: Deduplicated Lists

Get All Categories Used

=UNIQUE(C2:C)

Categories with Amounts

=UNIQUE(FILTER(C:C, C:C<>""))

SORT: Ordered Results

Sort by Amount (Largest First)

=SORT(A2:E, 5, FALSE)

Sort by Date (Newest First)

=SORT(A2:E, 1, FALSE)

Combined: Filter + Sort

=SORT(FILTER(A:E, E:E<0), 5, TRUE)

(All expenses, sorted smallest to largest)

SUMPRODUCT: Weighted Calculations

Weighted Average Return

=SUMPRODUCT(portfolio_weights, returns)

Conditional Sum with Multiple Criteria

=SUMPRODUCT((category=A2:A)*(month=B2:B)*(amounts))

Building Auto-Expanding Tables

Transaction Log with Calculated Fields

Date	Description	Amount	Category	Running Total
				(array formula)

=ARRAYFORMULA(IF(A2:A="", "",
  SUMIF(ROW(A2:A), "<="&ROW(A2:A), C2:C)))

Monthly Summary Auto-Generate

=ARRAYFORMULA(
  QUERY(
    {EOMONTH(A2:A, 0), C2:C, D2:D},
    "SELECT Col1, SUM(Col2), SUM(Col3)
     WHERE Col1 IS NOT NULL
     GROUP BY Col1
     ORDER BY Col1"
  )
)

Common Patterns

Prevent Errors on Empty Rows

=ARRAYFORMULA(IF(A2:A="", "", your_formula))

Limit to Data Rows

=ARRAYFORMULA(IF(ROW(A2:A)>MAX(IF(A2:A<>"", ROW(A2:A))), "", your_formula))

Add Row Numbers

=ARRAYFORMULA(IF(A2:A="", "", ROW(A2:A)-1))

Combining Array Functions

Complete Spending Dashboard

All categories with totals (auto-updating):

=ARRAYFORMULA(
  QUERY(
    {Transactions!C:C, Transactions!E:E},
    "SELECT Col1, SUM(Col2)
     WHERE Col2 < 0
     GROUP BY Col1
     ORDER BY SUM(Col2)
     LABEL Col1 'Category', SUM(Col2) 'Total'"
  )
)

Dynamic Date Ranges

=ARRAYFORMULA(
  FILTER(A:E,
    A:A >= EOMONTH(TODAY(), -1) + 1,
    A:A <= EOMONTH(TODAY(), 0)
  )
)

(Current month only)

Performance Tips

Limit range when possible: Use A2:A1000 instead of A2:A for large sheets
Avoid volatile functions: INDIRECT, OFFSET in arrays recalculate constantly
Use QUERY over complex arrays: Often faster and cleaner
One array per column: Multiple overlapping array formulas can conflict

Pro Tips

Test on small range first - get formula working, then expand
Use IF for blank rows - prevents errors and cleans output
Named ranges help - make complex formulas more readable
LAMBDA for reusable logic - create custom functions for repeated patterns
SCAN/MAP for iteration - newer functions for complex row-by-row calculations

Common Errors

#REF!: Array result would overwrite existing data
#VALUE!: Array sizes don’t match
Blank results: Missing IF wrapper for empty rows
Single result: Formula isn’t set up to operate on ranges
Circular reference: Array formula references its own output range