Excel LAMBDA Function
In this post we are going to discuss the new LAMBDA function that currently is in BETA release for Microsoft Excel and will be released to the general public at a later time. In my humble opinion (IMHO) This function will change the way people work in Excel and is considered a revolutionary change to Excel.
Excel has often been considered the most used programming language in the world. However it has always been considered incomplete as a programming language as it has only:
Supported Scalar values
Has not allowed you to define new functions without learning VBA
With the recent changes to the “new” Excel this has changed, these new changes include:
Data Types - Excel Custom Data Types — The Best Excel Reviews on the Web
Dynamic Arrays - Dynamic array formulas in Excel | Exceljet
LAMBDA - LAMBDA: The ultimate Excel worksheet function - Microsoft Research
I recommend you take the time to read the above links to better understand each of the above new features in Excel. These changes make the Excel programming language Turing-complete. “A Turing complete system can be proven mathematically to be capable of performing any possible calculation or computer program.” 1 Think about the possibilities if Excel is possible of performing any possible calcualtion. For the rest of the Blog we are going to focus on some of the best resources for learning about Microsoft’s new LAMBDA Function. In order for you to be able to use the LAMBDA function you will need to subscribe to the insider channel of Excel and have a subscription to Microsoft 365. If you do not subscribe and you are a big Excel user I recommend it.
LAMBDA Excel Videos
Today I want to start by listing some of my favorite Excel videos for learning what LAMBDA is and how it works in Excel. My first video is the best video I have found on LAMBDA. The video explains what LAMBDA is and how it works in an easy way for all of us to understand. The video is from Leila Gharani, Leila has a gift for explaining Excel and is a must follow for those wanting to learn Excel.
The second video I recommend is also by Leila Gharani and it explains what are Recursive LAMBDA’s and how they work. A Recursive LAMBDA is a LAMBDA that loops through data similar to Loops in VBA and is one of the things that makes LAMBDA so powerful.
This video by Mynda Treacy at MyOnlineTrainingHub does a great job of running through a few examples of how LAMBDA can be used in real life and how it can be used with the new LET function to create powerful complex formulas in an easy to understand format.
Below are links to a few other Excel videos that either explain how LAMBDA works or do a good job of showing how powerful the new LAMBDA function can be in Excel.
Mike Girvin Uses LAMDA and LET together in this fun video LAMBDA Excel Function. LET & LAMBDA together to make Single Cell Reports. Excel Magic Trick 1706 - YouTube
Mr. Excel shows a cool example of how LAMDA can be used to return pictures from data types Tom Hanks From A Lambda And Let Function In Excel - 2397 - YouTube
John MacDougal explains why LAMBDA is the most powerful function in Excel and how it can be used New LAMBDA Function [the Most POWERFUL Function in Excel] - YouTube
LAMBDA Articles
Below are a few articles I recommend reading to help you gain a better understanding of LAMBDA and how it opens up a whole new set of possibilities in Excel.
The first is by Dave Bruns who runs the website Exceljet.net. This site is one of my go to sites for seeing how a formula works in easy to digest examples. He does a great job of explaining LAMBDA with several easy to follow examples.
How to use the Excel LAMBDA function | Exceljet
Next on my list is a link to an article by WallStreetPrep as it includes seven videos and shows how LAMBDA can be used in the finance space
How to use the Excel LAMBDA function | Exceljet
This article does a good job of explaining LAMBDA and then recommending why you should continue to use VBA UDF functions for the time being.
Excel LAMBA Function - Create your own Excel Functions! - Analyst Cave
Below is a list of a few other useful articles I recommend reading regarding the new LAMBDA function.
Introducing the New LAMBDA Function in Excel | by Andrew Moss | CodeX | Medium
New Excel function redefines formula building - FM (fm-magazine.com)
Excel LAMBDA Function • My Online Training Hub
What Makes Excel's Lambda Functions so Awesome (and what doesn't)? (xlwings.org)
Conclusion
In my view LAMBDA will help change the way many of us work in Excel. I do not expect the average user to learn LAMBDA or how to use LAMBDA with the new LET function. However, I do expect many companies will create a library of custom functions using LAMBDA that will be available to all users and that will open up new possibilities for the average Excel user.
The LAMBDA function is still in BETA and has some work that needs to be done before it reaches its potential but that being said I believe LAMBDA is a game changer for Excel and is a giant leap forward for the new modern Excel.
Leave your thoughts in the comments regarding LAMBDA and how you could see your organization using it in the future.