Dynamic Arrays — The AI Way to Use Modern Excel

Chapter 14: Dynamic Array Functions — Excel 365's Formula Revolution

In 2018, Microsoft quietly introduced dynamic arrays into Excel 365, fundamentally changing how formulas work. A single formula can now "spill" results across an entire range automatically. FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE — these functions make what once required complex array formulas or Advanced Filter straightforward enough to be almost unbelievable.

The Dynamic Array Revolution

In traditional Excel (2019 and earlier), one formula produced one result in one cell. To output multiple values, you had to select the entire target range, type the formula, then press Ctrl+Shift+Enter to commit it as an array formula, which wrapped it in curly braces {}. This was unintuitive and difficult to modify.

Excel 365 introduced the dynamic array engine. Now, when a formula calculates multiple values, Excel automatically "spills" those values into adjacent empty cells — called spill behavior. Just type the formula in one cell, press Enter, and results fill the range automatically.

/* Old array formula (requires Ctrl+Shift+Enter) */
{=IF(B2:B10="Sales",C2:C10,"")}

/* Excel 365 dynamic array (just press Enter) */
=FILTER(C2:C10, B2:B10="Sales")

The Spill Range and the # Operator

When a formula spills, Excel marks the entire spill range with a blue border. The anchor cell holds the formula; other cells show "ghost" values (grayed, non-editable). Reference the entire spill range by appending # to the anchor cell address: A1# always refers to the full current spill from A1, regardless of how many rows it contains.

⚠️ Warning

Version Compatibility: All functions in this chapter (FILTER, SORT, SORTBY, UNIQUE, XLOOKUP, SEQUENCE, RANDARRAY) require Excel 365 or Excel 2021. They are not available in Excel 2016/2019 or WPS Office.

FILTER — Extract Data by Condition

Syntax

=FILTER(array, include, [if_empty])

• array    — The data range to filter (can be multiple columns)
• include  — Logical expression returning TRUE/FALSE (same row count as array)
• if_empty — Optional: what to show when no results match (default: #CALC! error)

Case 1: Extract All Employees in a Department

=FILTER(A2:C100, B2:B100="Sales", "No results")

Case 2: Records Where Sales Exceed $100,000

=FILTER(A2:C100, C2:C100>100000)

Case 3: Multi-Condition FILTER (AND / OR)

/* AND: department = "Marketing" AND salary > 8000 */
=FILTER(A2:D100, (B2:B100="Marketing") * (C2:C100>8000))

/* OR: department = "Sales" OR department = "Marketing" */
=FILTER(A2:D100, (B2:B100="Sales") + (B2:B100="Marketing"))

Multiply conditions (*) for AND logic; add them (+) for OR logic.

Case 4: FILTER + SORT Combined

/* Filter Sales dept, then sort by salary descending */
=SORT(FILTER(A2:C100, B2:B100="Sales"), 3, -1)

Case 5: Ask AI to Convert Your Filter Requirements into a Formula

I have an Excel order table with data in A1:G500. Column layout:
A=Order ID, B=Date, C=Customer, D=Product Type, E=Qty, F=Unit Price, G=Salesperson

Filter conditions (all must match):
1. Date between Jan 1 2024 and Mar 31 2024
2. Product Type is "Type A" or "Type B"
3. Unit Price greater than 500
4. Salesperson is "John"

Write an Excel 365 FILTER formula for cell I2 that returns all matching rows in full. Show "No records" if nothing matches.

SORT and SORTBY — Dynamic Sorting

Traditional Excel sort modifies source data in place and requires manual re-sorting. SORT and SORTBY output sorted results in a separate location, leaving source data untouched and updating automatically.

Case 1: Sort by Sales Descending

=SORT(A2:C100, 3, -1)
/* Column 3 (sales), descending order */

/* Top 10 only */
=TAKE(SORT(A2:C100, 3, -1), 10)

Case 2: SORTBY for Multi-Column Sort

/* Sort by department ascending, then by salary descending within each dept */
=SORTBY(A2:D100, B2:B100, 1, C2:C100, -1)

Case 3: Random Shuffle (SORTBY + RANDARRAY)

=SORTBY(A2:C50, RANDARRAY(49))
/* Generates 49 random numbers as sort keys — effectively shuffles the data */
/* Note: recalculates on every sheet change. Paste as values to lock the order. */

Case 4: Auto-Updating Leaderboard

/* Live top-5 sales leaderboard with rank numbers */
=LET(
  sorted, SORT(A2:C20, 3, -1),
  rank,   SEQUENCE(ROWS(sorted)),
  HSTACK(rank, sorted)
)

UNIQUE — Dynamic Deduplication

Syntax

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

Case 1: Extract a Unique Product List

=SORT(UNIQUE(A2:A500))

Case 2: Count Unique Values

=COUNTA(UNIQUE(B2:B500))
/* Count how many distinct customers exist */

Case 3: Multi-Column Deduplication

/* Unique combinations of customer + product */
=UNIQUE(B2:C500)

XLOOKUP — Next-Generation Lookup

XLOOKUP vs VLOOKUP

Case 1: Two-Way Lookup (Row and Column)

/* Matrix: A1 empty, B1:E1 = quarters, A2:A10 = products, B2:E10 = sales data */
=XLOOKUP("Product B", A2:A10, XLOOKUP("Q3", B1:E1, B2:E10))

Case 2: Reverse Lookup (Value is to the Left)

/* Look up by Employee ID in column D, return Name from column B (to the left) */
=XLOOKUP(G2, D2:D100, B2:B100)

Case 3: Custom "Not Found" Message

=XLOOKUP(A2, CustomerDB!A:A, CustomerDB!B:B, "New Customer")

Case 4: Return Multiple Columns at Once

/* Returns Name, Dept, and Salary (3 columns) in one formula */
=XLOOKUP(G2, A2:A100, B2:D100)

SEQUENCE — Generate Number Series

Case 1: Generate a Monthly Calendar Grid

=SEQUENCE(6, 7, DATE(2024,1,1), 1)
/* Format cells as "d" to show day numbers only */

Case 2: Auto-Numbering

/* Format: ORD-001, ORD-002 ... */
="ORD-" & TEXT(SEQUENCE(100), "000")

Case 3: Generate Test Data

/* 100 random integers between 1 and 1000 */
=RANDARRAY(100, 1, 1, 1000, TRUE)

/* Next 30 days as a date list */
=SEQUENCE(30, 1, TODAY(), 1)

Dynamic Array Functions Cheat Sheet

Previous ← Chapter 13: Charts & Data Visualization Next Chapter 15: VBA Basics →