The Excel Lambda Function is a new powerful addition to the Excel suite of functions, that Microsoft announced not long ago.
The Lambda function allows you to define a new Excel function with one or multiple parameters. I see it being especially useful in case of string manipulation or definition of new mathematics/statistical functions. What Lambda functions will not make any easier – are nested If statements or very complex calculations requiring loops etc. If you want to learn how to create such complex custom Excel function see the end of the article.
Excel Lambda definition
The Lambda function consists of only 2 types of arguments.
- Parameters – one or multiple parameters that you need to provide. Each one needs to have a different label as they will be uniquely used in the function
- Calculation – the actual results of the usage of the parameter. This will be returned as the result of the new created function
Below you can find the most simple example of defining and using a Lambda function in Excel.
Simple right? Notice that to run the function we had to append the brackets ( ) and enter the parameters after the lambda function. At first glace it is hard to appreciate the value of lambda as it does not seem like it can save us any time. Especially as we need to define it each time. This is where Named Ranges can help us…
Lambda as Named Range
To be able to reuse a Lambda function as you might any other function in Excel we need to name it as a Named Range. For this we will need to use the Name Manager in Excel.
Open the Name Manager
Go to Formulas ribbon and click on Name Manager in the Defined Names group.
Create a new Named Range and Paste the Lambda function
Click the New... button to create a new Named Range. Now enter the definition of your Lambda function.
Remeber to omit the ( ) – calling of the function. We will use the Named Range to call the function later.
Use the Named Range as your Lambda
You can now easily use the Lambda function without having to define it each time:
'Excel formula =ADDFUNC(1,2) 'The result 3
Did you know that when you copy a worksheet to another workbook your Named Ranges will copy as well? This is great way to copy a lot of custom formulas when using Lambda function in Excel. My suggestion is to keep you custom Lambda functions handy in a separate workbook and pulling them in when needed.
Excel Lambda Use Cases
I am sure as Lambda will become more widely available we will see more interesting usage examples. I had some of my own ideas on how I will start using the Excel Lambda function. See examples below:
Find the Hypotenuse of a right angle
'Definition =LAMBDA(a, b, SQRT((a^2+b^2))) 'Usage =MYLAMBDA(3,4) 'Result 5
Get Email domain from String
I think the Lambda function is going to be a great time saver for working with strings. Below an example of a function that helps get the domain from an email.
'Definition
=LAMBDA(mail,RIGHT(mail,LEN(mail)-FIND("@",mail)))
'Usage
=MYLAMBDA("something@gmail.com")
'Result
gmail.com
Recursion using Excel Lambda
One of the magics of using the Lambda function is that it allows for recursion. Recursion is (in simple terms) the ability of a function to call itself. The function would usually keep calling itself recursively until a certain conditional is fulfilled.
Let us use the example of the factorial function represented by the Excel FACT function. Given a number it returns the multiplication of the number and all following substracted numbers until 1. See example below:
'Factorial of 5 5! = 5 * 4 * 3 * 2 * 1 'Result 120
Sample example in Excel
=FACT(5) 'Result 120
Now let us recreate this function using Lambda and recursion. I am assuming I have defined my Lambda as a Named Range MYFACT.
Lambda vs VBA UDF
Lambda is a new addition to the Excel function family. But many of us don’t yet have access to it. Similarly in some cases we might still stumble on very complex functions with which Lambda might not be the easiest option. Fortunately since all of us can also use something called as UDF i.e. User Defined Function.
What are UDF’s? A User Defined Function is basically a Excel VBA Macro that can be made public to be used similarly as a custom Excel function. To define a UDF follow these simple steps:
- Make the Developer ribbon visible
- Create a new VBA Module by right-clicking in the Project window
- Create a VBA Function and make sure it is Public
- Save your file as XLSM file to save your macros
See the example below:
Now you can use the function just like you would with the Lambda function:
If you want below you can copy the MYFACT VBA function:
Public Function MYFACT(num As Long)
If num = 1 Then
MYFACT = 1
Else
MYFACT = num * MYFACT(num - 1)
End If
End Function
How to enable Lambda in Excel?
Most of you are probably scratching your heads trying to figure out how to enable this cool new Excel function. Well Microsoft is not eager to roll-out it out and sometimes your own organization may be at fault. Below message from Microsoft official page:
Note: This feature is only available if you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.
If you have a desktop version of Excel 2010/2016 basically no Lambda for you buddy. However if you have Office 365 and can’t find the function – reach out to your IT Admin to check when they will be releasing the newest updates within your organization.
Otherwise I suggest you can still you Excel UDF functions to create custom functions.
Conclusions
The Lambda function is a great addition and was definitely missing from Excel. As with all new functions I feel some time will need to pass for it to be widely used as not all Excel versions will have it enabled (Excel 2016 and older will not).
For now I recommend for all complex Excel function examples to resort to VBA UDF functions. At least these can be ported to almost any version of Excel and debugging is much easier.
What is your opinion?
Really baffled by the need or desire for Lambda functions. I’ve written dozens of UDFs and see zero added value with Lambda’s. In fact, their terse and uncommented nature would seem to make them worse than a UDF. Lambda seems like a solution in search of a problem. Silly.
I would tend to agree to some point – if you are fluent with VBA then Lambda is not really a game changer, but a more limited way of doing things for people only familiar with Excel functions. If you only know Excel functions you might not want to write custom VBA code. The benefit of using Lambda is the lack of security warnings when sharing the file.
One thing I dislike is that Microsoft made us do an additional unnecessary step when creating Lambda. Why force users to create named ranges? Why not create a Named Range type of user form where you can see all your all Lambda functions and edit/manage them there?
One strong point of LAMBDA is that it runs on any end-point where Excel is available (in the very near future) whereas VBA UDFs don’t work on most of them.
Another important benefit is that in my timing tests, Lambda’s outperform VBA UDFs by as much as a factor 10 (ten!).
I don’t think this stance will pass the test of time! As legacy Excel passes into the realm of being something only used by the most basic (no pun intended) users who would never touch VBA or Lambda, it will make less and less sense to gratuitously combine two very different programming environments to form a solution when one will do the job (faster and on a wider range of platforms).
Now the advanced formula environment will allow the functions to be documented and shared many things are possible. For example, I have written a Fast Fourier Transform function as a Lambda function and, since the solution uses array shaping functions, I suspect the task would have been worse, not better, in VBA. I am prepared to consider the argument that calling a Python library might have been a better use of my time, but that is another matter!
Strictly speaking, what you are calling a “named range” is referred to by Excel as a “name”, and a more accurate label would be “named formula”. The Refers-To property of every name is a formula, and it makes much more sense to call a name that defines a lambda function as a named formula.
Correct. I called it named ranges as I see most people referring to names or named formulas more as named ranges. Most of the times that is what we use names for – just to name a particular cell or range of cell. If you haven’t see this I highly recommend reading my article on dynamic named ranges:
https://analystcave.com/excel-dynamic-named-range/