LAMBDA function in Excel: how to create your own custom functions

The LAMBDA syntax

=LAMBDA([parameter1, parameter2, …], calculation)

• Parameters are placeholder names for the values you pass in, up to 253 of them. Use clear names such as price or rate, not cell addresses.
• The calculation is the last argument and uses the parameter names like cells.
• Typed into a cell on its own, a LAMBDA only describes a function, so Excel shows #CALC!. It calculates when you call it with arguments.

LAMBDA is available in Microsoft 365, Excel 2024 and Excel for the web. It is not in Excel 2021 or older.

Step 1: write and test it inline

Call a LAMBDA directly in a cell by putting the arguments in brackets right after it. In D2 of the example, =LAMBDA(price,cost,(price-cost)/price)(C2,B2) passes the price 39.00 as price and the cost 18.40 as cost and returns a margin of 52.8%. The arguments are matched by position, so the order matters: (B2,C2) would calculate a nonsense margin of −112%. Fill the formula down and check a few rows by hand before you go further. That is the whole point of testing inline: a mistake is still easy to see.

Step 2: save it as a named function

When the inline version is right, turn it into a workbook function:

1. Go to Formulas → Name Manager → New.
2. Name: MarginPct. Add a comment that describes the parameters, it appears as a tooltip.
3. Refers to: =LAMBDA(price,cost,(price-cost)/price), without the arguments.

From then on =MarginPct(C2,B2) works in every sheet of the workbook, and Excel suggests it as you type. The tax column works the same way: =LAMBDA(price,rate,ROUND(price*(1+rate),2)) saved as TaxPrice turns the desk lamp's 39.00 into 42.12 at an illustrative 8% rate. Change the rule once in the Name Manager and every formula that uses it updates.

Worked example: margin and tax functions for six products

Columns D and E call two LAMBDAs inline, exactly the way you test them before saving them as MarginPct and TaxPrice. Rows 13 to 19 use LAMBDA inside LET, MAP, REDUCE and SCAN.

Worked example: margin and tax functions for six products

	A	B	C	D	E
1	Product	Cost	Price	Margin %	Price incl. tax
2	Desk lamp	18.40	39.00	52.8%	42.12
3	Oak shelf	52.00	89.00	41.6%	96.12
4	Wool throw	27.50	64.00	57.0%	69.12
5	Side table	61.00	98.00	37.8%	105.84
6	Ceramic vase	9.80	24.00	59.2%	25.92
7	Floor rug	74.00	119.00	37.8%	128.52
8
9	Sales tax rate (illustrative)				8%
10	Margin threshold				40%
11
12	LAMBDA with LET and the helper functions				Result
13	LET: a local Margin function, used for the floor rug				37.8%
14	Nested: 15% off the wool throw, then tax (two LET functions)				58.75
15	MAP: average margin of all six products				47.7%
16	MAP: products below the margin threshold				2
17	REDUCE: total of all prices incl. tax				467.64
18	Check: REDUCE total minus SUM of column E				0.00
19	SCAN: running total of prices after the 3rd product				192.00

Fictional products, illustrative tax rate. Once saved in the Name Manager, D2 becomes =MarginPct(C2,B2) and E2 becomes =TaxPrice(C2,$E$9), with the same results.

LAMBDA inside LET: functions for one formula

If you need a function only inside one long formula, store it in a LET variable instead of the Name Manager: =LET(Margin,LAMBDA(price,cost,(price-cost)/price),Margin(C7,B7)) returns 37.8% for the floor rug. Several LET functions can be nested: 15% off the wool throw's 64.00 with SalePrice, then tax with WithTax, gives 58.75. Avoid variable names that are also Excel function names (DISC, for example, is a built-in function).

MAP, REDUCE, SCAN and BYROW

LAMBDA is also the engine of the helper functions that loop over a range for you:

• MAP(range, LAMBDA(x, …)) applies the function to every element: the average of the six margins is 47.7%, and counting those below the threshold in E10 gives 2 (the side table and the floor rug).
• REDUCE(start, range, LAMBDA(total, x, …)) carries one value through the range and returns the final result: the sum of all prices including tax, 467.64.
• SCAN works like REDUCE but returns every step, a running total. BYROW and BYCOL apply a function to each row or column.

If margin and markup are easy to confuse, see the guide on markup vs margin: a markup function would divide by the cost instead.

LAMBDA or a VBA function?

A named LAMBDA lives in the workbook like any other name: no macros, no security warning, the file stays an .xlsx and it works in Excel for the web. A VBA user-defined function can do things a formula cannot, such as reading files or calling other programs, but needs a macro-enabled file. For calculations, LAMBDA is usually the better choice. One limit: a named LAMBDA is only available in the workbook where it was defined, so copy a sheet that uses it into the new workbook or add the name there.

Common mistakes

• Forgetting to call the LAMBDA. =LAMBDA(x,x*2) alone returns #CALC!. Add the arguments, =LAMBDA(x,x*2)(A2), or save it as a name.
• The wrong number of arguments. A LAMBDA with two parameters called with one or three arguments returns #VALUE!.
• Arguments in the wrong order. They are matched by position, not by name. MarginPct(B2,C2) swaps price and cost without any error message.
• Parameter names that Excel cannot use. Names that look like cell addresses (TAX1) and the single letters r and c are not allowed. Use descriptive names.
• Including the arguments in the Name Manager. "Refers to" must contain only the LAMBDA, not the (C2,B2) you used for the test.
• Skipping the inline test. A named function hides its formula, so a mistake spreads to every cell that uses it. Test on a few rows first.