Man at table with computer.

Try LAMBDA: Custom functions without code

Feature deep dive
  • Share on Facebook
  • Share on Twitter
  • Share on LinkedIn

Hello, Office Insiders! My name is Chris Gross, and I am a Program Manager on the Excel team. I am happy to announce a new function in Excel: LAMBDA. It allows you to take a formula and turn it into your own named custom function.

LAMBDA function in Excel.

LAMBDA 

Do you make use of repeated calculations you wish you could define once and reuse? Have you ever looked for a function, but couldn’t find one that fits your specific needs? Or, maybe, you’ve run into this type of issue before and investigated creating VBA User Defined Functions (UDF) or JavaScript custom functions, only to discover that you need to learn a new language. 

All that changes today with the release of the LAMBDA function. 

For example, let’s say you want to calculate the result of the Pythagorean theorem or get a count of words in a text string. These are custom functions you can make yourself and re-use as LAMBDAs. 

LAMBDA gif in Excel.

How it works

To see how this function works, lets first go over the function syntax, and then we will walk through the steps needed to author your own.

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

[parameter1]-[parameter253] (optional):

A value that you want to pass to the function, such as a cell reference, string, or number. You can enter up to 253 parameters.

calculation:

The formula you want to execute and return as the result of the function. It must be the last argument and it must return a result. This argument is required.

Inspecting the individual arguments, there are two things to make note of:

  1. The first set of arguments, parameter1 – parameter253, like LET, are names for the inputs into your function. Since these are optional, you don’t have to include any inputs if you don’t want to. You can, however, make use of these names within your calculation.
  2. The final argument, calculation, must return a value. In most cases, this will be the formula you want to turn into a reusable function.

With the definition out of the way, we’re going to go over the steps necessary to build a LAMBDA like the ones above.

  1. LAMBDA function components.
  2. Calling a LAMBDA function.
  3. Naming a LAMBDA function.

LAMBDA function components

Let’s look at an example which puts the two earlier concepts together and creates a basic LAMBDA function.

Suppose we have the following formula:

=LAMBDA(x, x+122)

Deconstructing this example and mapping it to the syntax definition, let’s look at x:

=LAMBDA(xx+122)  

The first occurrence of x is defining the first and only parameter input to LAMBDA.

Now let’s look at x+122:

=LAMBDA(x, x+122)

The second parameter to LAMBDA, x+122, is the calculation, which makes use of the x name and will be substituted for the input value when the lambda function is called.

For example, suppose you called the lambda function and input the value 1 for x, Excel would substitute x for 1 and do the following calculation:

1 + 122

Which, as we all know:

1 + 122 = 123=

It’s almost as easy as… 1… 2… 3…

If you’ve been pasting the example above into Excel, you may have noticed some #CALC! errors. To resolve those, you’ll need to learn and perform the next step.

Calling a LAMBDA function

To put it simply, you call a LAMBDA function the same way you call native functions in Excel.

To illustrate this, let’s revisit the previous example and show how to call it with a value.

Uncalled

=LAMBDA(x, x+122) 

Called with the value 1

=LAMBDA(x, x+122)(1)

Returns

123

When should you call the LAMBDA function?

  • You will want to return an uncalled LAMBDA function when you initially store and name it, or for passing into other LAMBDA functions which may subsequently call it.
  • And you should call the LAMBDA function when you are making use of it during authoring and later re-use.

Naming a LAMBDA function

Once you have authored a LAMBDA function and are happy with the results, you should give it a name and store it for re-use.

To do this, you will want to make use of the Name Manager, so let’s dive in!

Name Manager Usage

To open Name Manager, click Formulas > Name Manager.

Naming a lambda function.

In the Name Manager dialog box, click the New button.

Naming a lambda function

In the New Name dialog box, enter the requested information, and then click OK.

Naming a lambda function.

  1. Name: The name of your function.
  2. Comment: A description and associated tooltip which will be shown when calling your function.
  3. Refers to: Your LAMBDA function definition. 

Naming a lambda function.

And that’s it! Now you can make use of your newly crafted custom function in the workbook by calling it by its name.

For example, we could call this LAMBDA function by authoring a formula which calls MYLAMBDA

=MYLAMBDA(122)

Which would return the value

123

Tips and tricks   

Availability

LAMBDA function is rolling out to users running the following Insider Beta Channel builds:

  • Windows: Version 2012 (Build 13519.20000) or higher
  • Mac: Version 16.45 (Build 1201.0) or higher

We typically release features over some time to ensure that things are working smoothly. This is true for Insiders as well. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as Insiders, have had the opportunity to try them.

Feedback 

To give feedback and suggestions, click Help > Feedback, and add #LAMBDA in your feedback so that we can easily find input about the feature. 

Learn more about what other information you should include in your feedback to ensure it’s actionable and reaches the right people. We’re excited to hear from you! 

With the Office Insider newsletter, you can get the latest information about Insider features in your inbox once a month.