Established onExcel 365: LET and LAMBDAExcel 2021: LET only, no LAMBDAGoogle Sheets, web: both, plus documented named functions
Two additions turn a formula from an expression into something closer to a program, and the second one moves the code out of the cell entirely.
Naming values inside a formula
The first lets a formula assign names to intermediate results and then use them. A long expression that repeats the same subexpression four times becomes one that computes it once, names it, and refers to the name.
Two benefits, and the readability one is the smaller. The computational benefit is that a repeated subexpression is genuinely evaluated once rather than four times, which on a lookup repeated across thousands of rows is a large difference.
Defining a function
The second lets a formula take parameters, and be stored under a name, and be called from any cell like a built-in. Alongside it came a set of functions that apply such a definition across a range: mapping it over every value, accumulating across a range, producing a running result.
Recursion is possible, because a definition stored under a name can refer to that name.
Where the code goes
This is the part to be careful about. The definition is not in a cell. It is stored among the workbook's defined names, in a dialogue that most people who open a spreadsheet have never opened.
So a sheet can contain a call to something that looks like a built-in function, with no indication anywhere on the surface of the sheet that it is not one, and no way to read what it does without knowing where to look. The formula got shorter and the logic moved somewhere invisible.
Availability, which differs sharply
Naming inside a formula is in Excel 365 and Excel 2021. Defining a function is in Excel 365 only, and arrived during 2022; it is not in Excel 2021 and not in anything earlier. Google Sheets has both, with the function definition arriving in 2022, and its own separate feature for named functions with documentation and parameter descriptions.
That last detail is worth noting: one product provides a place to write down what a defined function does and the other does not.
What this does to everything in the first part of this site
The argument there was that a spreadsheet has no tests, no review and no history, and that this is bearable because a sheet is not a program.
A workbook containing defined functions, recursion and mapping over ranges is a program, written in a language with no version control, no test framework, no comments in most implementations, and code stored in a dialogue box. Every complaint in the first part applies with more force.
That is not an argument against using them. It is an argument for treating a workbook that uses them as software: documenting each definition in a cell on a visible sheet, keeping the file somewhere versioned, and building the row of check cells described in the entry on missing tests.
When to use them and when not
Use naming inside a formula freely; it is a straightforward improvement with no hiding place, because the names live in the formula where they are read.
Use defined functions where the same non-trivial logic appears in many places and would otherwise be copied, and where the file will stay inside an organisation whose versions you know. Avoid them in anything sent outside, in anything that must open in an older version, and in anything a non-technical colleague will maintain.
The discoverability problem, stated once
If a workbook uses defined functions, put a sheet in it listing them, with the definition and a sentence about what it is for. Nobody will find them otherwise, including you in a year.
What we cannot verify
Availability by version is documented by both vendors and was confirmed on current builds. The performance claim about single evaluation of named subexpressions is stated by the vendor and was consistent with our timing on a large sheet, which is not a controlled measurement. Rollout dates within 2022 differ by release channel and we have not established them precisely.
The older way of doing the same thing
Custom functions written in the products' scripting languages have existed for decades and do the same job with more power and worse consequences: they require the file to be trusted, they are blocked by default in many organisations, and in one product they change the file extension.
The newer definitions have none of those problems, which is the real argument for them. They are also the first time in the history of these products that a reusable function can be written without leaving the formula language.
In short
- Naming inside a formula computes a repeated subexpression once instead of many times.
- Defining a function stores a parameterised formula under a name, callable anywhere.
- The definition lives among the workbook names, not in any cell a reader will see.
- One product offers a place to document a defined function and the other does not.
- Function definition is in Excel 365 only, not in 2021 and not in anything earlier.
- A workbook using these is software, and every complaint in the first part applies harder.