Excel Array Formulas

Array formulas are advanced formulas that can perform multiple calculations on a range of cells simultaneously, returning either a single result or multiple results. They allow complex operations that would otherwise require many helper columns or intermediate calculations — all in a single formula.

What is an Array?

An array is simply a collection of values — like a list of numbers or a grid of data. In Excel, arrays can be represented as cell ranges (like A1:A10) or as values typed directly into a formula (like {1,2,3,4,5}).

Simple Analogy

Without arrays: A person lifts one box at a time and records the weight of each box individually. Then they add up all the weights at the end.

With arrays: A person picks up all boxes at once, weighs them together, and gets the total in a single step. Array formulas work the same way — processing all values in one calculation.

Types of Array Formulas

1. Legacy Array Formulas (Ctrl + Shift + Enter)

In older versions of Excel (before 2019), array formulas had to be entered by pressing Ctrl + Shift + Enter instead of just Enter. Excel wrapped the formula in curly braces { } to indicate it is an array formula. These are sometimes called CSE formulas.

2. Dynamic Array Formulas (Excel 365 and Excel 2019+)

Modern Excel supports dynamic arrays. Formulas that return multiple values automatically "spill" into neighboring cells. No special key combination is required — just press Enter normally.

Legacy Array Formula Example

Goal: Multiply Two Ranges and Sum the Results (Without a Helper Column)

  | A (Units) | B (Price) |
1 | 10        | 50        |
2 | 5         | 80        |
3 | 8         | 30        |
4 | 3         | 120       |

  Without array: Calculate A1*B1, A2*B2, A3*B3, A4*B4 in helper column C,
  then sum column C.

  With array formula (single cell):
  {=SUM(A1:A4*B1:B4)}

  How to enter: Type =SUM(A1:A4*B1:B4) and press Ctrl + Shift + Enter
  Excel adds the { } automatically. Never type the braces manually.

  Calculation:
  → A1*B1 = 500
  → A2*B2 = 400
  → A3*B3 = 240
  → A4*B4 = 360
  → SUM = 1500

  Result: 1500
  (Note: =SUMPRODUCT(A1:A4, B1:B4) achieves the same result without needing array entry)

Dynamic Arrays (Excel 365 / Excel 2019+)

Dynamic array functions return results that automatically spill into multiple cells. The first cell in the output range contains the formula; the remaining cells are filled automatically.

Spill Range Indicator

When a dynamic array formula spills results across multiple cells, a blue border appears around all cells in the spill range. Other formulas can reference the spill range using the # symbol (e.g., =A1#).

Key Dynamic Array Functions

SORT Function

Returns a sorted version of a range.

Syntax

  =SORT(array, [sort_index], [sort_order], [by_col])

Example

  Names in A2:A6: Charlie, Alice, Eve, Bob, David

  =SORT(A2:A6)
  → Spills sorted list into the next cells:
  Alice, Bob, Charlie, David, Eve

  =SORT(A2:B6, 2, -1)
  → Sorts by column 2 (B) in descending order (-1)

FILTER Function

Returns only the rows that meet a specified condition — without changing the original data.

Syntax

  =FILTER(array, include, [if_empty])

Example

  | A     | B      | C      |
1 | Name  | Region | Sales  |
2 | Alice | North  | 5000   |
3 | Bob   | South  | 8000   |
4 | Carol | North  | 6500   |
5 | David | East   | 4000   |

  Show only North region rows:
  =FILTER(A2:C5, B2:B5="North")

  Spill result:
  Alice | North | 5000
  Carol | North | 6500

FILTER with Multiple Conditions

  Show North region AND sales > 5500:
  =FILTER(A2:C5, (B2:B5="North")*(C2:C5>5500))

  → Multiplication (*) acts like AND
  → Result: Carol | North | 6500

UNIQUE Function

Returns a list of unique (non-duplicate) values from a range.

Syntax

  =UNIQUE(array, [by_col], [exactly_once])

Example

  Regions in B2:B5: North, South, North, East

  =UNIQUE(B2:B5)
  → Spills: North, South, East  (duplicates removed)

SORTBY Function

Sorts a range based on values in another range.

Syntax

  =SORTBY(array, by_array, [sort_order])

Example

  Names in A2:A5, Scores in B2:B5
  Sort names by score, highest first:

  =SORTBY(A2:A5, B2:B5, -1)

  Result: Names ordered from highest to lowest score.

SEQUENCE Function

Generates a sequence of numbers automatically in a spill range.

Syntax

  =SEQUENCE(rows, [columns], [start], [step])

Example

  =SEQUENCE(5)
  → Spills: 1, 2, 3, 4, 5 (in 5 rows)

  =SEQUENCE(3, 4, 10, 5)
  → 3 rows, 4 columns, starting at 10, incrementing by 5
  → 10, 15, 20, 25
  → 30, 35, 40, 45
  → 50, 55, 60, 65

Referencing a Spill Range

If a FILTER result starts in cell E2, the entire spill range can be referenced as E2# in other formulas.

  =SORT(E2#)
  → Sorts the entire spill range from E2

Combining Dynamic Array Functions

Example: Filter, Then Sort

  Show all North region records, sorted by Sales descending:

  =SORT(FILTER(A2:C5, B2:B5="North"), 3, -1)

  → FILTER returns all North rows
  → SORT sorts those rows by column 3 (Sales) in descending order

Example: Unique Sorted List

  =SORT(UNIQUE(B2:B100))
  → Returns a sorted list of unique region names

SUMPRODUCT: An Array-Like Function Without CSE

SUMPRODUCT multiplies corresponding elements of arrays and sums the results. It has always worked like an array formula without requiring Ctrl + Shift + Enter.

Example

  =SUMPRODUCT(A2:A5, B2:B5)
  → Multiplies each Unit by its Price and sums the products
  (Same as the legacy array SUM example shown earlier)

Summary

• Array formulas perform calculations on multiple values simultaneously.
• Legacy array formulas (Excel 2016 and older) require Ctrl + Shift + Enter and display curly braces { }.
• Dynamic array functions (Excel 365/2019+) spill results automatically into neighboring cells.
• SORT: Sorts a range. FILTER: Returns rows meeting a condition. UNIQUE: Returns distinct values.
• SEQUENCE: Generates number sequences. SORTBY: Sorts by another array.
• Spill ranges are referenced using the # symbol (e.g., A1#).
• SUMPRODUCT performs array-style multiplication and summation without a special key combination.