If you learned Excel before 2022, your mental map of "what Excel can do" is roughly a decade out of date. This is not hyperbole. Between the introduction of dynamic arrays in 2019 and the rollout of the modern function set that finished in late 2024, Microsoft shipped more genuinely useful additions to the formula language than they had in the preceding fifteen years combined. Most of those additions are still missing from how the average analyst writes formulas, because the muscle memory is older than the toolkit.
The result is a strange situation: the workbook you opened this morning probably contains formulas that solve 2015 problems with 2015 tools, sitting in a version of Excel that quietly added the right tools four versions ago. Nothing forces you to upgrade your habits. The old patterns still work. They just work badly compared to what's available now.
This post is a tour of the functions that changed the most about how I write spreadsheets. The list is opinionated, ordered by how much each one reshaped my defaults, and honest about where each falls down.
XLOOKUP - the function that should have always existed
The first one is the obvious one, but it earns the top spot because it replaces three legacy patterns simultaneously: VLOOKUP, HLOOKUP, and the INDEX/MATCH combination that was the previous workaround for VLOOKUP's worst failure mode.
The mental shift: lookups stop being column-position arithmetic. With VLOOKUP you tell Excel "return the third column of this range" and pray nobody inserts a column to the left of your return value. With XLOOKUP you point at the lookup column and the return column directly. They can be in any order, on either side of each other. The formula is immune to inserted columns because there are no column counts to invalidate.
=XLOOKUP(A2, Customers[CustomerID], Customers[Status], "Not found")The fourth argument replaces IFERROR wrapping, which was always a hack. The fifth and sixth arguments give you exact-or-next-smaller matching and reverse-direction search, both of which used to require INDEX/MATCH with arguments most people never memorised.
Where it falls down: case sensitivity is not configurable - XLOOKUP is case-insensitive and there is no flag to make it case-sensitive. For case-sensitive lookups you still need INDEX with EXACT inside a MATCH-shaped pattern.
LET - the function that makes formulas legible
LET is the closest thing Excel has to a variable. It lets you assign a name to an intermediate value inside a formula, then reuse that name later in the same formula. The result is a formula that reads top-to-bottom in named steps, instead of a single nested expression with the same sub-calculation appearing three times.
=LET(
revenue, XLOOKUP(A2, Sales[ID], Sales[Revenue]),
discount, XLOOKUP(A2, Sales[ID], Sales[Discount]),
net, revenue * (1 - discount),
IF(net > 1000, "High", "Standard")
)The same XLOOKUP isn't repeated. The intermediate net value is named, not buried inside parentheses. The formula now reads in plain English: look up revenue, look up discount, compute net, classify.
The performance benefit is real but secondary. Excel evaluates each LET name once, so a repeated sub-expression that previously ran twice now runs once. The bigger win is readability. The auditor who opens this workbook six months from now reads names, not parenthesis trees.
Where it falls down: LET names disappear at the end of the formula. They're not workbook-wide. If you want to reuse a calculation across cells, you need LAMBDA.
LAMBDA - the function that turned Excel into a language
LAMBDA is the structural change. It lets you define your own functions, name them, and call them from anywhere in the workbook the same way you call XLOOKUP or SUM.
The before-and-after is not subtle. Before LAMBDA, "I do this calculation a lot" meant copying the formula into every cell that needed it, or writing a VBA UDF and accepting that everyone who opens the file sees a security warning bar. After LAMBDA, "I do this calculation a lot" means defining the calculation once, naming it, and calling it from anywhere.
=LAMBDA(due, status,
IF(status="Done", "Done",
IF(due < TODAY(), "Overdue (" & TODAY()-due & " days)", "On track"))
)(A2, B2)Save that into the Name Manager as TASK_STATUS, and now =TASK_STATUS(A2, B2) works from any cell in the workbook. The full deep-dive on this lives in LAMBDA: How to Write the Excel Functions Microsoft Didn't.
Where it falls down: the Name Manager is a terrible editor. Build LAMBDAs in a cell first, where Excel formats them and reports errors, then copy the definition into Name Manager once it works. Trying to debug a complex LAMBDA inside the Refers to box is its own form of punishment.
The other limitation is version compatibility. LAMBDA requires Microsoft 365 or Excel 2024. Open a workbook with named LAMBDAs in Excel 2019 and every call returns #NAME?.
BYROW and BYCOL - what "apply this to every row" was always meant to be
These two are the under-appreciated members of the modern set. BYROW hands a LAMBDA an entire row at a time. BYCOL does the same column by column. Combined with LAMBDA, they replace the drag-this-formula-down workflow with a single cell formula that produces the entire result column automatically.
=BYROW(Budget[[Budget]:[Actual]], LAMBDA(r, INDEX(r,2)/INDEX(r,1) - 1))One formula. The spilled output covers every row in the table. Add new rows; the spill extends to match. There is no formula to drag, no column to maintain, no last-row-missing problem when source data grows.
The full Magic Monday on this is coming on May 18. If you want the three-minute version, see the May 16 Click Bait.
Where it falls down: the spilled result is not a column inside the table. If you need a value that sorts and filters with table rows, you still need a real table column. BYROW produces a parallel spill that happens to sit next to the table.
SCAN, MAP, REDUCE - the array operators
These three are the closest Excel has come to functional programming primitives. MAP applies a function to each element of an array. REDUCE collapses an array to a single value by running an accumulator. SCAN does the same accumulation but keeps every intermediate result, returning the running sequence.
The most common application is the one people pick up first: SCAN replaces the entire helper-column-for-a-running-total pattern.
=SCAN(0, Transactions[Amount], LAMBDA(acc, val, acc + val))One cell. The running total of the entire column. Add a transaction; the spilled result extends.
The deeper application is in composability. Once these three are in your toolkit, "transform this array, then aggregate it" becomes a single formula written in the way you'd describe it in plain English. The intermediate steps that used to demand helper columns disappear.
The Magic Monday on SCAN is the May 11 piece. MAP and REDUCE deserve their own deep-dives and don't have them yet - the brief version is that MAP is the most under-used of the three, and REDUCE is what you reach for when SCAN keeps too much state.
Where they fall down: error handling inside the LAMBDA can be tricky. If one row triggers a #VALUE!, the entire spilled result can come back as an error array. Wrap the LAMBDA body in IFERROR if your data is unreliable. Performance also degrades on very large arrays, especially when the LAMBDA does expensive lookups inside - the function is being called once per row, and that adds up.
TEXTSPLIT, TEXTBEFORE, TEXTAFTER - the text functions that finally landed
A smaller but persistent improvement. The historical Excel text functions were almost-but-not-quite the right shape. LEFT, RIGHT, MID, and FIND were the entire toolkit, and any non-trivial text manipulation became a nested expression that calculated positions from positions.
TEXTSPLIT splits a string by a delimiter into an array, the way every other language has done for thirty years. TEXTBEFORE and TEXTAFTER return the substring before or after a delimiter, without you having to compute its position with FIND first.
=TEXTBEFORE("Smith, Jane", ", ") -- returns "Smith"
=TEXTAFTER("Smith, Jane", ", ") -- returns "Jane"
=TEXTSPLIT("a,b,c,d", ",") -- returns {"a","b","c","d"}If you've ever written =LEFT(A1, FIND(",", A1) - 1), you have written the long version of TEXTBEFORE. The long version still works. The short version is one function call with no off-by-one risks.
The bigger pattern
The thread connecting all of these is the same thread: Excel has moved from a language of references-and-positions to a language of arrays-and-functions. The old patterns were built around individual cells: this cell looks up that cell, that cell adds two others, drag the formula down. The new patterns are built around whole arrays: this column maps to that column, this array reduces to a value, this LAMBDA defines what happens for every row.
If your workbook still looks like the first version - dragged formulas, helper columns, nested IFs three levels deep - it's not because you're doing anything wrong. It's because the language has shifted underneath you and nobody made you upgrade. The cost of the old patterns is invisible until you stand them next to the new ones, and then the difference is hard to ignore.
The payoff for taking the time to learn the modern set isn't "writing faster formulas." It's that the workbook becomes legible. Whoever opens it next - including you, six months from now - can read what it does instead of decoding what it does. That is the upgrade. The tools were here all along. They are still waiting for the rest of us to catch up.
Frequently Asked Questions
Do I need to learn all of these at once?
No. The fastest path to feeling the difference is XLOOKUP and LET first - they replace patterns you almost certainly already write daily. LAMBDA and BYROW are the next tier and they reward more time to internalise. SCAN, MAP, and REDUCE are powerful but situational; pick them up when you hit a problem they fit.
What if I'm on a version of Excel that doesn't have these?
Most of the functions in this list require Microsoft 365 or Excel 2024. Excel 2021 has XLOOKUP, LET, and the dynamic array functions but not LAMBDA, BYROW, BYCOL, SCAN, MAP, or REDUCE. Excel 2019 has none of them. If you're stuck on 2019, INDEX/MATCH and helper columns are still your toolkit; if you're on 2021, the gap is mostly the LAMBDA-based family.
Will my workbook break if I share it with someone on an older Excel?
Any cell that calls a function their version doesn't support returns #NAME?. The named LAMBDAs in Name Manager travel with the file but won't evaluate. For cross-version workbooks, either stay on the lowest-common-denominator function set, or maintain two versions of the file.
Does Microsoft Copilot in Excel use these functions?
When asked to write formulas, Copilot will use the modern functions when they fit - XLOOKUP, LET, and LAMBDA show up regularly in its suggestions. Whether the suggestions are correct is a separate question, and the only way to verify is to know what these functions do yourself.
Is VBA dead?
For everything covered in this post, yes. The modern function set replaces the entire category of "I need a custom Excel calculation" that VBA UDFs used to fill. VBA still has a role for things formulas can't do - file system access, manipulating other workbooks, triggering external processes - but for custom calculations, formulas have caught up.