Excel Lambda lets you define reusable custom functions. On Dec 3, 2020, Microsoft released a new capability that will change the way we use excel formulas. Excel was missing the ability to use the formula language to define your own reusable functions. The new update will allow users to create their own reusable functions. There is another way in excel by which you can create custom functions but, for that, you have to write the entire stuff in another language like JavaScript.
For creating custom functions through lambda, you can use Excel’s default formula language.
In the new lambda function, one function can call another function. There is no need to write custom multiple functions only one function call can do the job. You can choose any formula that you have created earlier in excel and wrap that formula with the lambda function, then give it a name. For example: “MyLambdaFunction”. And here is your new function ready for use.
Recursion
Lambda function can be used to call other functions one we have discussed above that is reusable functions other is Recursion.
Excel has been missing the capability to loop over to repeat over a set of logic at a dynamically defined interval. There are ways that you can manually configure the interval at which Excel recalculates to mimic this to an extent, but it’s not inherent to the formula language. That changes with LAMBDA.
Note: The lambda function is available to members of the Insiders: Beta program running Windows and Mac builds of Excel.
For example, the function you have created before “MYLAMBDAFUNCTION” you can call this function while defining MYLAMBDAFUNCTION. This means calling the function while defining it is called recursion. Before it is only possible through a script (such as JavaScript/VBA) in excel but now you can simply do it by using the lambda function.
For more details on Recursion click here for example.
LAMBDA & Data Types:
Over the past couple of year there have been two significant improvements with the type of data you can work within Excel:
Dynamic arrays – Rather than passing a single value into a function, you can pass an array of values, and functions can also return arrays of values.
Data Types – The value stored in a cell is no longer just a string or a number. A single cell can contain a rich data type, with a large set of properties.
Also Read: Microsoft Excel allows to connect and work on your own data type.
Functions can take data types and arrays as arguments, and they can also return results as data types and arrays. The same is true with the lambdas you build.
LAMBDA Overview
There are three key pieces of =LAMBDA to understand:
- LAMBDA function components
- Naming a lambda
- Calling a lambda function
Components of LAMBDA
For example: =LAMBDA(a, a+99)
Where ‘a’ is the argument that will be passed by the user.
And a+99 is the logic part that is to be solved.
If you pass 1 for a then after solving the function we get, a+99; 1+99=100.
But if you paste this formula into excel, you will get Calc! error. To resolve this error you need to name the Lambda function.
Naming a LAMBDA
For naming a lambda function.
Go to Formulas> Name Manager> Click New.
Now give a name and write any comment, at last, give Refers to: for example =LAMBDA(a a+99). Click ok and done.
LAMBDA Calling
You can call the Lambda function like you call any other function in Excel.
For calling a lambda function you just have to write.
=LAMBDA(argument)
For example =LAMBDA(99) which will return 100 as a value.
You can call a lambda without naming it. If we hadn’t named the previous formula, and just authored it in the grid, we could call it like this:
=LAMBDA(a, a+99)(1)
This passes 1 for a and returns 100.
As per Microsoft’s announcement, they have already planned a lot of improvements for LAMBDA, and Microsoft is looking for feedback. Microsoft is also going to add some more array manipulation functions in the coming months that will help to build even more powerful lambdas to take advantage of dynamic arrays.
Also Read: How to create Microsoft forms on mobile phone?