Excel

Array Formula

A formula that performs multiple calculations on one or more sets of values and returns either a single result or multiple results, enabling complex operations in a single cell.

Examples

=SUM(A1:A10*B1:B10) for weighted totals
=FILTER(A:C, B:B>1000) for filtered results
=UNIQUE(A2:A100) for unique values

Array Formula

An Array Formula is a formula that can process multiple values simultaneously and return one or multiple results. They're essential for complex calculations that would otherwise require helper columns.

Understanding Arrays

An array is a collection of values treated as a single unit. In spreadsheets:

  • 1D Array (row): {1, 2, 3, 4, 5}
  • 1D Array (column): {1; 2; 3; 4; 5}
  • 2D Array: {1, 2, 3; 4, 5, 6}

Types of Array Formulas

Single-Result Array Formulas

Return one value from multiple calculations:

=SUM(A1:A10*B1:B10)

Multiplies each A value by corresponding B value, then sums all products.

Multi-Result Array Formulas (Dynamic Arrays)

Return multiple values that "spill" into adjacent cells:

=UNIQUE(A2:A100)
=FILTER(A:C, B:B="North")
=SORT(A2:C100, 2, 1)

Excel: Legacy vs Dynamic Arrays

Legacy CSE Arrays (Excel 2019 and earlier)

  • Required Ctrl+Shift+Enter to confirm
  • Displayed with curly braces { }
  • Couldn't spill to multiple cells naturally

Dynamic Arrays (Excel 365/2021)

  • Automatic spilling
  • No special entry required
  • New functions: FILTER, SORT, UNIQUE, SEQUENCE, RANDARRAY

Google Sheets: ARRAYFORMULA

In Sheets, wrap formulas with ARRAYFORMULA to process ranges:

=ARRAYFORMULA(A2:A*B2:B)
=ARRAYFORMULA(IF(A2:A="", "", A2:A*B2:B))

Key Array Functions

Excel Dynamic Arrays

Function Purpose
FILTER Return rows matching criteria
SORT Sort a range
SORTBY Sort by another range
UNIQUE Remove duplicates
SEQUENCE Generate number sequences
RANDARRAY Generate random arrays
XLOOKUP Modern lookup (array-aware)

Google Sheets

Function Purpose
ARRAYFORMULA Apply formula to range
FILTER Return matching rows
SORT Sort results
UNIQUE Remove duplicates
QUERY SQL-like data operations

Examples

Sum of Products

=SUM(Quantity*Price)
// Or explicitly
=SUMPRODUCT(Quantity, Price)

Count with Multiple Criteria

=SUM((A2:A100="North")*(B2:B100>1000))

Filter and Sort

=SORT(FILTER(A2:D100, B2:B100="Active"), 3, -1)

Create Multiplication Table

=SEQUENCE(10)*SEQUENCE(1,10)

The Spill Range Operator (#)

Reference all values in a spill range:

=UNIQUE(A2:A100)     // In cell C2, spills down
=SUM(C2#)            // Sum all spilled values

Common Errors

#SPILL!

Spill range is blocked by existing data. Clear the cells.

#CALC!

Calculation error in the array formula. Check your logic.

Wrong Size Result

Formula returns different size than expected. Verify your ranges.

Performance Tips

  1. Limit range sizes: Use A2:A1000 instead of A:A when possible
  2. Avoid volatile functions: NOW(), TODAY() in arrays recalculate constantly
  3. One array formula: Better than 1000 individual formulas
  4. Use dynamic arrays: More efficient than legacy CSE

Best Practices

  1. Understand spilling: Know where results will appear
  2. Leave room: Don't put data where arrays might spill
  3. Name your ranges: Makes array formulas more readable
  4. Test with small data: Verify logic before applying to large datasets
  5. Document complex formulas: Comments or a documentation sheet