LAMBDA in Excel: Build Your Own Reusable Function

How to write your own Excel function with LAMBDA

LAMBDA turns a formula you keep retyping into a named function of your own. You write the logic once, give the inputs names, save it in the Name Manager, and from then on you call it like any built in function. No VBA, no macros, and nothing to install.

It runs in Microsoft 365 and Excel for the web. Excel 2019 and Excel 2021 do not have it.

*

> Short version:
>
> - =LAMBDA(parameters, calculation) defines a function. On its own it does nothing.
> - Add a test call in brackets straight after it to see a result: =LAMBDA(x, x*2)(5) gives 10.
> - Save it under Formulas, Name Manager, New, and it becomes a function you can call by name.
> - #CALC! nearly always means you defined it and never called it.
> - It takes up to 253 parameters, which is more than any formula should need.

*

What LAMBDA actually does

Say every quarter you work out a price with VAT, a discount, and a rounding rule, and the formula has grown to this:

=ROUND(B2(1-C2)1.15, 2)

There is nothing wrong with it. The problem starts when it appears in forty cells across six sheets, and then the VAT rate changes. Now you are hunting for every copy.

LAMBDA lets you say what that calculation is, once:

=LAMBDA(price, discount, ROUND(price(1-discount)1.15, 2))

Read it left to right: the names before the last comma are the inputs, and the part after the last comma is the calculation that uses them.

Test it in a cell before you save it

This is the step people skip, and it is why the first thing they see is an error. A LAMBDA by itself defines a function and never runs it, so Excel has nothing to show you and answers #CALC!.

Add a second pair of brackets with real values, right after the definition:

=LAMBDA(price, discount, ROUND(price(1-discount)1.15, 2))(200, 0.1)

That returns 207. The trailing (200, 0.1) is the test call. Get the number you expect before you go any further, because a mistake is much easier to find here than after the formula has a name and is scattered across the workbook.

Save it so you can call it by name

Once the test call gives the right answer:

• Copy the definition without the test call.
• Go to Formulas, then Name Manager, then New.
• Put the name you want in Name, for example PriceWithVat. No spaces.
• Paste the definition into Refers to.
• Click OK.

Now =PriceWithVat(B2, C2) works anywhere in the workbook. Change the rule in one place, in the Name Manager, and every cell that calls it updates.

Two habits that pay for themselves later: give the parameters names a stranger could read, price rather than x, and put a sentence in the Comment box saying what the function is for. You will be that stranger in six months.

The errors, and what each one means

What you see	What it usually is
#CALC!	The formula defines a function and never calls it. Add a test call in brackets.
#VALUE!	The wrong number of arguments. A two parameter function called with one, or with three.
#NAME?	A misspelt name, or a saved function you are calling from a different workbook.
#NUM!	Usually recursion with nothing to stop it. Check the condition that ends the loop.

Where it earns its place

A rule that keeps changing. VAT rates, commission bands, shipping thresholds. One definition, one place to edit. A formula nobody else can read. A nest of IF inside IF inside IF becomes =Grade(B2), and the logic lives in one place with a name that says what it does. Cleaning text the same way every time. Trimming, stripping punctuation, fixing capitalisation. Wrap the chain once and call it TidyName. Repeating something across a range. BYROW, BYCOL and MAP take a LAMBDA and apply it to every row, column or cell, so you can run your own function across a whole table without filling anything down.

Where it does not

LAMBDA is not the place to start if you are still building the everyday toolkit. Almost all real spreadsheet work is SUM, IF, a lookup, COUNTIF and a few text functions, and being quick and accurate with those matters far more than writing custom functions.

It is also not a macro. It calculates and returns a value. It cannot format cells, move data, send mail or open files.

And if your workbook has to open in Excel 2019 or Excel 2021, or for somebody who will open it there, a saved LAMBDA will not work for them.

Practise the parts it is built from

LAMBDA is only worth writing once the formula inside it is right, and that is ordinary Excel. These run in your browser, marked as you type:

• IF statements, which is the logic most custom functions wrap.
• The IFS function, for rules with more than two outcomes.
• FLOOR and CEILING, the rounding step a price calculation usually ends on.
• LEN, LEFT and RIGHT, the text handling a tidy up function chains together.
• CONCAT, for joining the pieces back together.

Or start at the full list of Excel exercises and work through the ones you use at work.

A worked example, start to finish

A support team logs response times in minutes and wants them read as hours and minutes.

The calculation, written plainly: =INT(B2/60)&"h "&TEXT(MOD(B2,60),"00")&"m" As a LAMBDA with a test call: =LAMBDA(minutes, INT(minutes/60)&"h "&TEXT(MOD(minutes,60),"00")&"m")(135)

That returns 2h 15m. The test call proves it before it has a name.

Saved in the Name Manager as AsHoursMinutes, it becomes =AsHoursMinutes(B2), and anyone reading the sheet can tell what that column does without unpicking the string handling.

That is the whole point of LAMBDA. Not cleverness, but one name for a thing you had been explaining over and over.