What is so great about the Excel LAMBDA function?
Microsoft announced the launch of the LAMBDA on December 3, 2020 and it is probably safe to say we’ve never seen this much excitement from the Excel MVP community. And that’s saying a lot because just a few months earlier, Microsoft’s Excel team announced the XLOOKUP, which also blew people’s minds.
Excel’s LAMBDA function makes it possible for regular Excel users to create their own functions, give those functions a name, and use them just like any other Excel function.
This can be useful in a variety of ways, but here’s a simple example: We constantly deal with dates, and need to figure out which quarter they occur in. We’ve always wanted a version of EOMONTH that works for QUARTERS. We normally have to create a long complicated formula to figure it out.
With LAMBDA weI can just create our very own EOMONTH function:
As you can imagine, this ratchets up Excel’s power dramatically.
Excel LAMBDA applications for finance
So we got to thinking… What are some immediate, quick and easy and truly useful applications of Excel’s LAMBDA functions for people who use Excel particularly in the corporate finance, investment banking and private equity?
That’s what this mini course is for. Over the course of the 8 short videos below, we will cover all the basics of using LAMBDAs in Excel and teach you how to build a variety of custom functions you can put to use right away (be sure to download the free excel file containing the LAMBDAs below the videos). Enjoy!
Before we begin: Get the LAMBDA Worksheet
Use the form below to download the Excel worksheet used in this mini-course:
Video 1: Create simple custom functions with LAMBDA
Video 2: Create a =CAGR() function to calculate Compound Annual Growth Rate
Video 3: Calculates a company’s Days Sales Outstanding with a =DSO() function
Video 4: Calculate implied growth rate of an annuity with a =IMPLIEDG() function
Video 5: Create a =EOQUARTER() function to solve the problem we mentioned earlier
Video 6: A =TSM() function to calculate dilutive options using the Treasury Stock Method
Video 7: Bonus function! Use =SHEETNAME() to output the name of the active sheet
This one is courtesy of Mr. Excel
Video 8: Use your LAMBDAs across multiple workbooks and share them with others
Recursion with LAMBDA
One feature of LAMBDA that we did not cover is something called recursion – which is a superpower Microsoft gave LAMBDA giving it the ability to loop and self reference.
That will be a topic for a subsequent video. In the meantime, check out Ms. Excel’s excellent starter video on LAMBDA’s recursion with LAMBDA.
This brings us to the end of our lesson – we hope you enjoyed this course!
Have ideas for useful LAMBDAs?
Share them with the world in the comments below!
Is it possible to use lambda in a cell formula to compare the previous value of a cell with the new value? I use the Stock Connector Addin and would like to store the max value of a cell that updates every five minutes.
We’re not sure if that is quite possible or not but our guess is that would probably not work.