Introduction
Excel has been used the same way for more than 30 years. Memorizing VLOOKUP, creating helper columns, dragging formulas down, refreshing pivot tables — that has been the daily routine. Yet the Microsoft 365 version of Excel has quietly, but fundamentally, changed.
The core of it is the **dynamic array**. A single formula automatically "spills" into multiple cells, a function returns an entire array, and complex transformations finish in one line without any helper columns. Add `LAMBDA`, `LET`, `GROUPBY`, and regular expression functions on top of that, and Excel now resembles a lightweight functional data-processing tool.
In this article, we organize the latest functions available in Microsoft 365 as of 2026, centered on practical examples. For each function we present the actual formula together with an explanation of the result, and we also cover compatibility concerns with older versions as well as dashboard and report scenarios.
> Most of the functions in this article work in the Microsoft 365 subscription version and the web version of Excel. Perpetual licenses (Excel 2019/2021/2024) support only some of them, so be sure to check the compatibility table at the end.
1. Dynamic Arrays and Spill — A Paradigm Shift
What is spill?
In traditional Excel, a function returned a single value to a single cell. With the introduction of the dynamic array engine, a single formula **automatically expands** its multiple results into adjacent cells. This is called spill.
For example, when A2:A6 contains numbers, try entering the following into a single cell.
=A2:A6*2
In the past you would have gotten only a single value, or you would have needed to build an array formula with `Ctrl+Shift+Enter`. Now, five results automatically spill down below the cell you entered. The entire result range is called the **spill range**, and if the starting cell is C2, you can reference the whole range with `C2#` (the hash operator).
=SUM(C2#)
The formula above computes the sum of the entire spill range starting at C2. Even if the size of the spill range changes, the reference follows automatically.
The #SPILL! error
If another value occupies the space where the spill should unfold, a `#SPILL!` error occurs. The fix is simple: just clear the cells where the result will unfold. When using dynamic arrays, it is important to develop a sense that "a single formula occupies the space below and to the right."
A B C(=A2:A6*2)
10 20 <- starts here
20 40
30 60
40 80
50 100
Helper columns disappear
The biggest practical benefit of dynamic arrays is **eliminating helper columns**. In the past you would create a "unit price × quantity" column and then sum it, but now you handle the array multiplication inside a single formula.
=SUM(unitPriceRange * quantityRange)
The formula becomes cleaner, and even when rows are added or deleted you no longer need to maintain helper columns.
2. XLOOKUP — The Complete Successor to VLOOKUP
`VLOOKUP` had three chronic problems: it could not look to the left, it required hardcoding column numbers, and its default was approximate match. `XLOOKUP` solves all of these.
=XLOOKUP(lookupValue, lookupRange, returnRange, "Not found", 0)
- First argument: the value to find
- Second argument: the range to search (in any direction)
- Third argument: the range to return
- Fourth argument: the value to return when not found (a custom value instead of the default error)
- Fifth argument: the match mode (0 = exact match, the default)
For example, you can look up a name by employee ID, and there is no problem even if the employee ID column is to the right of the name column.
=XLOOKUP("E1042", employeeIdColumn, nameColumn, "Unregistered")
If you specify a return range with multiple columns, you can even pull several values at once as a spill.
=XLOOKUP("E1042", employeeIdColumn, nameDeptSalaryRange, "Not found")
This way, the name, department, and salary spill out into a single row. Work that used to require three VLOOKUPs becomes a single line.
3. FILTER / SORT / UNIQUE / SEQUENCE — The Data-Wrangling Four
FILTER
Picks only the rows that match a condition and returns them as a spill.
=FILTER(dataRange, deptColumn="Dev", "No results")
This unfolds every row whose department is "Dev" exactly as is. You can also combine conditions with multiplication (AND) and addition (OR).
=FILTER(dataRange, (deptColumn="Dev")*(salaryColumn>5000), "None")
`*` acts as AND, and `+` acts as OR.
SORT / SORTBY
=SORT(FILTER(dataRange, deptColumn="Dev"), 3, -1)
This sorts the FILTER result in descending order (-1) by the third column. By nesting functions, "filter then sort" finishes in a single line. To sort by a different column, `SORTBY` is convenient.
=SORTBY(dataRange, salaryColumn, -1)
UNIQUE
Removes duplicates.
=UNIQUE(deptColumn)
This distills the department column down to unique values and unfolds them. It is extremely useful when creating drop-down lists or the axis of an aggregation.
SEQUENCE
Creates an array of consecutive numbers.
=SEQUENCE(12, 1, 1, 1)
This generates a 12-row, 1-column array starting at 1 and increasing by 1 (months 1 through 12). It is frequently used for creating date axes or indexes.
4. LET — Declaring Variables Inside a Formula
Long formulas tend to repeat the same calculation. `LET` creates **named variables** inside a formula, improving both readability and performance at once.
=LET(
preTax, unitPrice * quantity,
tax, preTax * 0.1,
preTax + tax
)
Here `preTax` and `tax` are each computed once and reused. The last argument is the final return value. The more variables you have, the greater the benefit, and it turns complex report formulas into a form that humans can read.
5. LAMBDA and Its Friends — Functional Programming Inside Excel
LAMBDA: Build your own function
`LAMBDA` lets you create reusable user-defined functions with formulas alone. Once registered in the Name Manager, you can call it anywhere in the workbook.
=LAMBDA(celsius, celsius * 9 / 5 + 32)(25)
The above is an immediate-call example that converts 25 to Fahrenheit and returns 77. If you register it in the Name Manager as `celsiusToFahrenheit`, you use it like a function as shown below.
=celsiusToFahrenheit(25)
MAP: Apply a function to each element of an array
=MAP(scoreRange, LAMBDA(x, IF(x>=60, "Pass", "Fail")))
This applies the lambda to each value of the score array and returns a pass/fail array.
REDUCE: Cumulative aggregation
=REDUCE(0, amountRange, LAMBDA(acc, val, acc + val))
Starting from an initial value of 0, it accumulates all the values. You can express arbitrary aggregation logic such as a running sum or a running product.
SCAN: Return the entire accumulation process
While `REDUCE` gives only the final value, `SCAN` unfolds the accumulated result at each step as an array.
=SCAN(0, revenueRange, LAMBDA(acc, val, acc + val))
This creates the cumulative running total (YTD) of monthly revenue in a single line.
BYROW / BYCOL: Row- and column-level aggregation
=BYROW(scoreTableRange, LAMBDA(row, AVERAGE(row)))
This returns the average of each row as a vertical array. It is suitable for per-student averages, per-item totals, and so on. For column-level work you use `BYCOL`.
6. GROUPBY / PIVOTBY — Pivots Built with Formulas
The biggest inconvenience of a pivot table is that you have to **refresh** it every time the data changes. `GROUPBY` and `PIVOTBY` are formula-based aggregations, so when the source changes the result is reflected automatically. No refresh is needed.
GROUPBY: Aggregate by group
=GROUPBY(deptColumn, revenueColumn, SUM, , 1)
- First argument: the grouping criterion (the row field)
- Second argument: the value to aggregate
- Third argument: the aggregation function (SUM, AVERAGE, COUNT, etc.)
- Fifth argument: the header display option
This automatically unfolds the revenue total by department. By placing a lambda in the aggregation function slot, **custom aggregation** is also possible.
=GROUPBY(deptColumn, revenueColumn, LAMBDA(v, MAX(v) - MIN(v)))
This directly defines and computes the max-minus-min spread (range) of revenue by department.
PIVOTBY: Row-by-column cross aggregation
=PIVOTBY(deptColumn, quarterColumn, revenueColumn, SUM)
This builds a cross-tabulation with department as rows and quarter as columns. Unlike a traditional pivot table, because it is a cell formula it is reflected immediately when the source data is updated. It is powerful when building dynamic summary tables for a dashboard.
7. Regular Expression Functions — REGEXTEST / REGEXEXTRACT / REGEXREPLACE
Regular expressions have entered Excel as official functions. Text validation, extraction, and substitution become much cleaner.
REGEXTEST: Whether a pattern matches
=REGEXTEST(A2, "^[0-9]{3}-[0-9]{4}$")
If A2 matches the "three digits-four digits" pattern, it returns TRUE. It is suitable for validating phone number or postal code formats.
REGEXEXTRACT: Extract the matching part
=REGEXEXTRACT(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+")
This pulls an email address out of a string.
REGEXREPLACE: Substitute a pattern
=REGEXREPLACE(A2, "[^0-9]", "")
This removes every non-digit character, leaving only pure digits. It is frequently used for normalizing phone numbers.
8. TRIMRANGE and the New Trim References
`TRIMRANGE` trims away empty rows and columns at the edges of a range. When a table grows and shrinks, you can build a dynamic range that "captures to the end but excludes the empty parts."
=TRIMRANGE(A1:A100000)
Even if you reference 100,000 rows, it returns only up to the part that actually has values, reducing the performance hit caused by overly large ranges.
9. Text Decomposition — TEXTSPLIT / TEXTBEFORE / TEXTAFTER
TEXTSPLIT
Splits text by a delimiter and spills it. It replaces the "Text to Columns" wizard with a formula.
=TEXTSPLIT("Seoul,Busan,Daegu", ",")
The three cities spill out horizontally. If you also specify a row delimiter, you can split into two dimensions as well.
TEXTBEFORE / TEXTAFTER
=TEXTBEFORE("user@example.com", "@")
This returns "user", the part before `@`. `TEXTAFTER` returns the trailing "example.com". It is convenient for separating an extension from a file name, extracting a file name from a path, and so on.
10. Importing Data — IMPORTTEXT / IMPORTCSV
`IMPORTTEXT` and `IMPORTCSV` have been added to web-based Excel and the latest desktop, allowing you to load external text/CSV directly with a formula.
=IMPORTCSV("https://example.com/data/sales.csv")
This pulls the specified CSV into cells as a spill. You can use it as a simple, auto-updating data source, but for large-scale or complex transformations, Power Query — which we will cover in a later article — is a better fit.
11. A Practical Scenario — Building a Dynamic Dashboard
Let's combine these functions to build a department-level revenue dashboard that "needs no refresh."
1. Extract the unique list of departments
=UNIQUE(deptColumn)
2. Get the revenue total and average by department in one shot
=GROUPBY(deptColumn, revenueColumn, HSTACK(SUM, AVERAGE))
3. Filter and sort to keep only the top 3 departments
=TAKE(SORT(GROUPBY(deptColumn, revenueColumn, SUM), 2, -1), 3)
After sorting by revenue in descending order with `SORT`, `TAKE` brings only the top 3 rows.
4. A cross-tabulation for a specific period
=PIVOTBY(deptColumn, monthColumn, revenueColumn, SUM)
All of these cells update automatically when the source data changes. Just add rows to the input sheet, and the dashboard follows along as if it were alive.
12. Function Summary Table
| Function | Role | Key point |
| --- | --- | --- |
| XLOOKUP | Lookup | Bidirectional, custom value when not found |
| FILTER | Conditional extraction | Combine multiple conditions with AND/OR |
| SORT / SORTBY | Sorting | Can sort by a different column |
| UNIQUE | Deduplication | Create lists and axes |
| SEQUENCE | Consecutive arrays | Create dates and indexes |
| LET | Variable declaration | Improves readability and performance |
| LAMBDA | User functions | Reuse after registering in Name Manager |
| MAP / REDUCE / SCAN | Functional aggregation | Per-element and cumulative processing |
| BYROW / BYCOL | Row/column aggregation | Direction-specific summaries |
| GROUPBY / PIVOTBY | Formula pivots | No refresh needed, custom lambda |
| REGEXTEST / EXTRACT / REPLACE | Regular expressions | Validation, extraction, substitution |
| TRIMRANGE | Range trimming | Removes empty edges |
| TEXTSPLIT / BEFORE / AFTER | Text decomposition | Delimiter-based splitting |
| IMPORTTEXT / IMPORTCSV | Importing | Loads external data directly |
13. Compatibility Concerns with Older Versions
| Feature | M365 / Web | Excel 2024 | Excel 2021 | Excel 2019 or earlier |
| --- | --- | --- | --- | --- |
| Dynamic arrays / spill | Supported | Supported | Supported | Not supported |
| XLOOKUP | Supported | Supported | Supported | Not supported |
| LET / LAMBDA | Supported | Partial | Not supported | Not supported |
| GROUPBY / PIVOTBY | Supported | Not supported | Not supported | Not supported |
| Regular expression functions | Supported | Not supported | Not supported | Not supported |
When you share a file with users on older versions, dynamic array results can break with the implicit intersection operator. It is safer to leave static results by pasting as values (copy values), or to replace them with compatibility functions.
Closing Thoughts
Excel's modern functions are not merely "convenient shortcuts" but **tools that change the way you work itself**. When you reduce helper columns and dragging, and process data with a single declarative formula, mistakes decrease and maintenance becomes easier.
At first, learn `XLOOKUP`, `FILTER`, and `UNIQUE`; once you are comfortable, move on to `LET` and `LAMBDA`. Finally, once `GROUPBY`/`PIVOTBY` become second nature, a new workflow opens up: "reports that need no refresh."
In the next article, we will cover the keyboard-shortcut system for working in Excel without leaving the keyboard for the mouse, and after that, how to automate data cleansing with Power Query.
References
- [XLOOKUP function (Microsoft Support)](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)
- [FILTER function (Microsoft Support)](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)
- [LAMBDA function (Microsoft Support)](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)
- [LET function (Microsoft Support)](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)
- [GROUPBY function (Microsoft Support)](https://support.microsoft.com/en-us/office/groupby-function-d58dbc52-9c12-4ec9-bcb3-a37c2c038a9b)
- [PIVOTBY function (Microsoft Support)](https://support.microsoft.com/en-us/office/pivotby-function-3e90a3d5-2cb6-4b8c-bb8a-2f7286ca665b)
- [Dynamic array and spilled array behavior (Microsoft Support)](https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531)
- [Regular expressions in Excel (Microsoft Tech Community)](https://techcommunity.microsoft.com/t5/excel-blog/regular-expressions-regex-in-excel/ba-p/4149144)
- [Excel functions (alphabetical) (Microsoft Support)](https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188)
현재 단락 (1/141)
Excel has been used the same way for more than 30 years. Memorizing VLOOKUP, creating helper columns...