Dynamic Array Functions: The New Era of Excel

In the "Old Excel," one cell equaled one formula result. In "Modern Excel," one formula can return an entire range of data. This behavior is called Spilling.

Part 1: How Dynamic Arrays Work

When you write a dynamic array formula, Excel creates a Spill Range. * The Blue Border: When you click any cell in the results, you will see a thin blue border around the entire range. * The Ghost Formula: Only the top-left cell contains the actual formula. If you click the other cells, you will see the formula in the bar, but it will be "grayed out." * The #SPILL! Error: If there is already data in the way of where the formula needs to go, Excel won't overwrite it. It will show a #SPILL! error until you clear the path.

Part 2: The Core Array Functions

1. FILTER(): The SQL "WHERE" Clause

• What it does: Extracts rows from a range that meet specific criteria.
• Syntax: =FILTER(array, include, [if_empty])
• Use Case: Creating a live list of "High Priority" tasks from a master project sheet.
• Official Docs: FILTER function

2. SORTBY(): Pro-Level Sorting

• What it does: Sorts a range based on the values in a different range.
• Syntax: =SORTBY(array, by_array1, [sort_order1], ...)
• Use Case: You have a list of names and a list of scores. You want to display the names sorted by the scores (highest to lowest), without showing the scores themselves.
• Official Docs: SORTBY function

3. UNIQUE(): The Duplicate Killer

• What it does: Returns a list of unique values from a range. No more "Remove Duplicates" button!
• Syntax: =UNIQUE(array)
• Use Case: Getting a clean list of every unique customer who made a purchase this month.
• Official Docs: UNIQUE function

4. VSTACK(): The Great Consolidator

• What it does: Vertically appends (stacks) arrays on top of each other. (There is also an HSTACK for side-by-side).
• Syntax: =VSTACK(array1, array2, ...)
• Use Case: You have a "Sales_Jan" table and a "Sales_Feb" table. VSTACK turns them into one long master list instantly.
• Official Docs: VSTACK function

5. CHOOSECOLS(): Precision Extraction

• What it does: Returns specific columns from an array or range.
• Syntax: =CHOOSECOLS(array, col_num1, [col_num2], ...)
• Use Case: You have a massive 50-column report, but you only need columns 1, 5, and 12.
• Official Docs: CHOOSECOLS function

Part 3: The Spilled Range Operator (#)

This is a new piece of syntax you must know for the MO-211 exam. If you want to refer to the entire result of a dynamic array, you simply point to the first cell and add a #.

• Example: If your UNIQUE list starts in cell E2, referring to E2# tells Excel: "Grab everything that spilled from E2, no matter how long it is."

Compatibility & History

• Released: Announced in late 2018; rolled out to Microsoft 365 users in 2020. VSTACK and CHOOSECOLS were added later in 2022.
• Requirement: These only work in Microsoft 365 and Excel 2021 or newer.
• Older Versions: If you open these in Excel 2019 or 2016, you will see _xlfn. added to the start of the formula, and it will return a #NAME? error.

Part 4: Generating and Sorting Arrays

SORT() vs. SORTBY()

• What it does: Sorts a range using its own values as the sort key. Distinct from SORTBY, which sorts by a separate range.
• When to use it: Producing a sorted view of a list directly — e.g., "show the product table ordered by price."
• Syntax: =SORT(array, [sort_index], [sort_order], [by_col])
  • sort_index — which column (or row) to sort by (default 1)
  • sort_order — 1 ascending (default), -1 descending
  • by_col — FALSE to sort rows (default), TRUE to sort columns

• Official Docs: SORT function

• Example (sort A2:C100 by column 3, descending): excel =SORT(A2:C100, 3, -1)

SEQUENCE(): Instant Number Series

• What it does: Generates an array of sequential numbers — 2D if needed.
• When to use it: Numbering invoice line items, building a calendar of dates, or generating row indexes inside a LET/LAMBDA formula.
• Syntax: =SEQUENCE(rows, [columns], [start], [step])

• Official Docs: SEQUENCE function

• Example (line numbers 1–25): excel =SEQUENCE(25)

• Example (the next 12 month-end dates starting from today): excel =EOMONTH(TODAY(), SEQUENCE(12, 1, 0, 1))
• Example (a 5×4 grid starting at 100, stepping by 10): excel =SEQUENCE(5, 4, 100, 10)

Part 5: Combining Arrays — HSTACK & VSTACK

Note: VSTACK was introduced briefly above. This part formalizes both the horizontal and vertical variants.

VSTACK()

• What it does: Stacks arrays vertically (one on top of another). Mismatched widths are padded with #N/A.
• When to use it: Consolidating monthly tables (Jan, Feb, Mar) into a single year-to-date list, or building an audit trail by appending new entries.
• Syntax: =VSTACK(array1, [array2], …)

• Official Docs: VSTACK function

• Example: excel =VSTACK(Sales_Jan, Sales_Feb, Sales_Mar)

HSTACK()

• What it does: Stacks arrays horizontally (side by side). Mismatched heights are padded with #N/A.
• When to use it: Joining a customer name column with a related metric column without using a helper area.
• Syntax: =HSTACK(array1, [array2], …)

• Official Docs: HSTACK function

• Example (combine names from column A and totals from column F into one 2-column array): excel =HSTACK(A2:A100, F2:F100)

Part 6: Text-as-Array — Splitting and Joining

These functions turn text manipulation into something Excel can do with formulas instead of menus.

TEXTSPLIT()

• What it does: Splits a text string into an array, breaking on a column delimiter and (optionally) a row delimiter. Replaces the Text-to-Columns wizard for many cases.
• When to use it: Parsing CSV-like text from a single cell, splitting full names into first/last, breaking address lines from a single field.
• Syntax: =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

• Official Docs: TEXTSPLIT function

• Example (split "Smith, John, Engineer" into 3 spilled cells): excel =TEXTSPLIT("Smith, John, Engineer", ", ")

• Example (split lines and CSV at once): excel =TEXTSPLIT(A2, ",", CHAR(10))

TEXTJOIN()

• What it does: Concatenates a list of strings using a delimiter — the inverse of TEXTSPLIT. Optionally ignores empty cells.
• When to use it: Building a comma-separated list of unique customers, exporting tags, or producing a single header line from a column of values.
• Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

• Official Docs: TEXTJOIN function

• Example (semicolon-separated email list, skipping blanks): excel =TEXTJOIN("; ", TRUE, Contacts[Email])

TEXTBEFORE() and TEXTAFTER()

• What they do: Extract the substring before or after a chosen delimiter — without manual LEFT/MID/FIND math.
• When to use them: Pulling a domain out of an email, the file extension off a filename, or a project code out of a hyphen-separated SKU.
• Syntax:
  • =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
  • =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

• Official Docs: TEXTBEFORE function | TEXTAFTER function

• Example (extract the domain from "alice@contoso.com"): excel =TEXTAFTER(A2, "@")

• Example (extract the project code from "PROJ-2024-NORTH-001" — text before the second hyphen): excel =TEXTBEFORE(A2, "-", 2)

Part 7: Slicing Arrays — TAKE, DROP, CHOOSEROWS

TAKE()

• What it does: Returns the first (or last) N rows or columns of an array. Negative values count from the end.
• When to use it: Top-N reports ("show me the 10 most recent orders"), or grabbing the latest row of a log.
• Syntax: =TAKE(array, rows, [columns])

• Official Docs: TAKE function

• Example (top 5 rows after sorting by amount): excel =TAKE(SORT(Orders, 3, -1), 5)

• Example (the last 2 rows — negative count): excel =TAKE(Log, -2)

DROP()

• What it does: Removes the first (or last) N rows or columns from an array. The mirror image of TAKE.
• When to use it: Stripping a header row, ignoring totals at the bottom, or peeling off a label column.
• Syntax: =DROP(array, rows, [columns])

• Official Docs: DROP function

• Example (skip the header row of a range): excel =DROP(A1:D100, 1)

CHOOSEROWS() and CHOOSECOLS()

• What they do: Return only the rows / columns you list, in the order you list them.
• When to use them: Reordering columns for a presentation view, or pulling rows 1, 4, and 9 specifically.
• Syntax:
  • =CHOOSEROWS(array, row_num1, [row_num2], …)
  • =CHOOSECOLS(array, col_num1, [col_num2], …)

• Official Docs: CHOOSEROWS function | CHOOSECOLS function

• Example (reorder a 5-column dataset to show columns 3, 1, 5): excel =CHOOSECOLS(A2:E100, 3, 1, 5)

• Example (every other row from the first 10): excel =CHOOSEROWS(A1:D10, 1, 3, 5, 7, 9)