January 25, 2021

Lambda Functions in Microsoft Excel

How to use Lambda functions in Excel.

What is LAMBDA? It allows us to take a formula and convert it into our own named custom function. Here’s an example of creating a custom function using Lambda:

Lambda Function to multiply two numbers
Lambda Function to multiply two numbers

Normally we create such functions using VBA and collectively they are called User Defined Functions (UDF). An interesting example of a user-defined-function is to calculate compound interest. If we make use of repeated calculations and wish to define such a function once and reuse it, we can create a LAMBDA function as shown in the image above. We show below the same MyLambda function in an Excel worksheet:

Custom function to multiply two numbers using Lambda
Custom Function Using Lambda

How to name a Lambda Function.

Once we have thought of a function and confirmed the validity of its results , we can give it a name and store it for reuse. Here we make use of the name manager.

Name Manager
Name Manager

We click on Formulas and the Name Manager and enter the details:

Name: The name of our function. We could also name the function ProductOfTwoNumbers.
Comment: We have added no comment. But we could add: multiplies two numbers.
Refers to: Our lambda function definition.

MyLambda - Custom Function multiplies two numbers
MyLambda – Custom Function multiplies two numbers

It’s done! We can make use of your new custom function in the workbook by calling it by its name: =MyLambda(A2,B2)

This would return the value 35 as shown above since the range A2 has the value 5 and the range B2 has the value 7.

Watch the video below:

Lambda Functions in MS Excel

One thought on “Lambda Functions in Microsoft Excel

Comments are closed.