Excel LAMBDA(): How to Build Custom Functions

Excel’s LAMBDA() function creates the possibility of using custom formulas. It lets you encapsulate any calculation, give it a name, and reuse it anywhere in your workbook.

This means you don't have to copy the same formula over and over and over again. Instead, you can use LAMBDA() to make your spreadsheets cleaner and more maintainable and, if you ask me, it's even a little easier than using VBA.

Why Use Excel LAMBDA()?

At its core, LAMBDA() lets you wrap any formula in a function you define. You provide input parameters (just like the arguments in SUM() or VLOOKUP()) and then call your new function with those parameters whenever you need it. Here's the structure:

=LAMBDA(parameter1, parameter2, ..., calculation)

You can experiment with LAMBDA() formulas directly in a cell, but the real power comes when you save one as a named function. Once named, your custom function appears in your workbook just like SUM(), AVERAGE(), and others. 

Here are some compelling reasons to introduce LAMBDA() into your workflow:

• Eliminate repetitive formulas. Instead of rewriting a complex formula everywhere, turn it into a function and call it by name.

• Make your spreadsheets clearer. Named functions are much easier for you and your collaborators to understand than a repeated, cryptic formula.

• Combine with other new functions. LAMBDA() plays nicely with LET(), MAP(), REDUCE(), and other modern Excel features.

• Cut down on errors. Once your logic is tested and wrapped in a LAMBDA(), you can reuse it — no more typos from copying formulas.

How to Create an Excel LAMBDA() Function

Like anything, this takes some practice. So let’s work through creating our own LAMBDA() functions.

Imagine you're calculating the area of a circle from its radius and you find yourself repeatedly typing =PI()*A1^2.

First, try building and running a LAMBDA() formula directly in a cell:

=LAMBDA(r, PI()*r^2)(3)

Here, we’ve created a LAMBDA() function that accepts r as a parameter and returns the circle's area. The (3) at the end supplies the radius, so this formula calculates the area for a circle with radius 3.

Of course, typing out the LAMBDA() each time is little improvement over the original formula. But bear with me because named functions come into play:

1. Go to Formulas > Name Manager > New.

2. Pick a name for your function, such as CircleArea.

3. In the Refers to box, enter:

=LAMBDA(r, PI()*r^2)

Now, you can use your custom function throughout your workbook:

=CircleArea(A1)

Now you’ve created a reusable, custom function.

Excel LAMBDA() Syntax and Parameters

LAMBDA() can accept any number of parameters (up to Excel’s limit of 253), each separated by a comma. (Not that you would really use this many.) 

It’s important to see that the final argument is always the calculation itself, which can use any of the parameters you define.

=LAMBDA(parameter1, parameter2, ..., calculation)

For instance, if you want to create a function to return the larger of two numbers, you might write:

=LAMBDA(a, b, IF(a > b, a, b))

After saving this as a named function - MaxOfTwo is a good name - you’d use it like any other Excel function:

=MaxOfTwo(7, 4)

This formula returns 7, the larger of the two inputs.

More Specific Excel LAMBDA() Examples

Let’s keep practicing.

Excel LAMBDA() with multiple parameters

Next let’s work with functions that take several inputs. Just as before, we separate our parameters with commas, and we supply the same number of arguments when we call the function.

To add two numbers:

=LAMBDA(a, b, a + b)(4, 9)

This will return 13.

If you decide to turn this into a named function called AddTwo, you can use it throughout your workbook like this:

=AddTwo(A1, A2)

You now have a custom addition function.

Clean up repetitive logic

Suppose you’re calculating commissions with this formula in several places:

=IF(A2 > 1000, A2*0.1, 0)

Instead of copying this formula everywhere you have to use it, you can encapsulate the logic with LAMBDA():

=LAMBDA(amount, IF(amount > 1000, amount*0.1, 0))

Name this function Commission, and you can now use:

=Commission(A2)

If you ever need to change the commission logic, you only have to update the LAMBDA() in one place, and every formula using Commission also updates.

Recursion and nested functions

LAMBDA() supports recursion, meaning where a function can call itself. The unlocks new ideas, such as custom aggregations.

For example, here’s how you could define a recursive factorial function:

=LAMBDA(n, IF(n=1, 1, n*Factorial(n-1))) 

You’d save this as a named function, and it works just like any recursive function in programming. (For the name, I’d choose something explicit, like Factorial.) 

LAMBDA() with other dynamic array functions

LAMBDA() can be combined with other dynamic Excel functions like LET(), MAP(), and REDUCE(). 

LET() allows you to assign and reuse variables within your formula, while MAP() and REDUCE() apply LAMBDA()s across arrays or lists.

For example, to double every value in a range:

=MAP(A1:A5, LAMBDA(x, IF(x>10, x*2, x)))

Here, MAP() applies your LAMBDA() function to each item in A1:A5. This is very nice for batch calculations.

(The formula above doubles the corresponding value in the range only if the value is greater than 10.)

(One thing that is nice also is that you only have to write the formula once, in cell B1, and the cells below are autofilled.)

Pro Tips with Excel LAMBDA()

Here are some tips from experience.

Test Excel LAMBDA() functions before naming them

Before you commit to naming and saving your LAMBDA(), it's smart to test it directly in a cell. You can do this by appending your test arguments in parentheses, just like calling any function:

=LAMBDA(x, x*2)(5)

This returns 10, the result we  expected.

If you forget to supply an argument when testing, like typing just =LAMBDA(x, x*2), Excel will display the function itself, not a result. Always remember to add those final parentheses with your input values.

Some caveats

Let’s pause and look at limitations:

• LAMBDA() functions are workbook-scoped. If you want to use your custom functions in another workbook, you’ll need to copy them, or use Excel’s new Named Functions feature if it’s available.

• You can’t use LAMBDA() to create volatile functions (like NOW() or RAND()).

• Supplying the wrong number of arguments when calling a LAMBDA() will result in a #VALUE! error, so always double-check your inputs.

• Not all Excel versions support LAMBDA(). You’ll need Microsoft 365 or the latest web version to make use of these features.

LAMDA() and Other Functions

Finally, let’s consider LAMBDA() alongside other ways of doing things. 

When should you use Excel LAMBDA()?

Reflecting on everything we’ve covered, you might be wondering, when is LAMBDA() the right tool for the job? Reach for LAMBDA() when:

• You have complex or repetitive formulas that you want to make simple.
• There is logic you’d like to name and explain.
• You want custom calculations or logic that Excel doesn’t provide out of the box.

In my opinion, LAMBDA() is especially useful for teams, especially when the function names are explicit. Then, team members see exactly what the logic is doing without having to work through the calculation to understand. 

Related Excel functions

It helps to be familiar with other new Excel functions that complement LAMBDA(). Here are a few. Some I mentioned earlier.

• LET(): Assign names to calculations inside a formula

• MAP(): Apply a LAMBDA() to each item in an array

• REDUCE(): Fold an array down to a single value using a LAMBDA()

• BYROW(), BYCOL(): Apply a LAMBDA() by row or column

These functions, when paired with LAMBDA(), unlock more sophisticated logic and data transformations. Take our Advanced Excel Functions course to learn about the really useful functions that Excel has to offer. 

Conclusion

As we’ve seen and practiced, Excel LAMBDA() lets you build your own worksheet functions without even touching VBA. So use LAMBDA() if you regularly write the same calculation in many places, or want to tame complicated formulas.

And remember to take our Advanced Excel Functions course to get faster and smarter with Excel. And if you are a financial analyst or want to be one, take our Financial Modeling in Excel course as well. LAMDA() functions are going to be especially useful in the world of finance.