Array formulas perform calculations on multiple values simultaneously, returning either a single result or multiple results. Excel 365's dynamic arrays make them easier and more powerful than ever.

Basic Array Formula

// Sum of products (multiply then sum)
=SUMPRODUCT(A2:A10, B2:B10)

// Equivalent array formula (legacy)
{=SUM(A2:A10 * B2:B10)}  // Press Ctrl+Shift+Enter

Dynamic Array Functions (Excel 365)

UNIQUE - Get Unique Values

// Extract unique values from a list
=UNIQUE(A2:A100)

// Automatically spills to adjacent cells

SORT - Sort Data

// Sort by column A ascending
=SORT(A2:C100, 1, 1)

// Sort by column 2 descending
=SORT(A2:C100, 2, -1)

FILTER - Filter Data

// Filter sales greater than 1000
=FILTER(A2:C100, B2:B100>1000)

// Multiple conditions
=FILTER(A2:C100, (B2:B100>1000) * (C2:C100="North"))

SEQUENCE - Generate Number Series

// Generate numbers 1 to 10
=SEQUENCE(10)

// Generate 5 rows x 3 columns starting at 1
=SEQUENCE(5, 3)

// Dates for next 7 days
=TODAY() + SEQUENCE(7) - 1

Practical Examples

Calculate Total Cost

// Column A: Quantity
// Column B: Price
// Result: Sum of all items (Qty * Price)

=SUMPRODUCT(A2:A10, B2:B10)

Find Top 5 Values

// Sort sales and take first 5 rows
=TAKE(SORT(A2:B100, 2, -1), 5)

Count Unique Values

// Count distinct customers
=COUNTA(UNIQUE(A2:A100))

Multi-Condition Lookup

// Find sales for specific product AND region
=FILTER(A2:D100, (B2:B100="Widget") * (C2:C100="East"))

Legacy Array Formulas

Array Entry (Ctrl+Shift+Enter)

// Count values greater than 100
{=SUM(IF(A2:A100>100, 1, 0))}

// Max value in each row
{=MAX(IF(A2:A10=B2:B10, C2:C10))}

XLOOKUP with Arrays

// Lookup and return multiple columns
=XLOOKUP(E2, A2:A100, B2:D100)

// Spills results across 3 columns automatically

Spill Range Reference

// Reference entire spilled range
=A2#  // All cells that spilled from A2

// Use in other formulas
=SUM(A2#)  // Sum all spilled values

When to Use Array Formulas

• Processing multiple cells with one formula
• Complex conditional calculations
• Dynamic filtered or sorted lists
• Avoiding helper columns

Common Array Functions

• UNIQUE: Extract unique values
• SORT/SORTBY: Sort data dynamically
• FILTER: Filter based on conditions
• SEQUENCE: Generate number sequences
• SUMPRODUCT: Sum of products