Brand new in Excel for 2021 - LAMBDA is the most powerful function ever. This is awesome!
LAMBDA is the most powerful function in Excel for one reason. It lets you create your own functions
and use them throughout your spreadsheet. Let's take a look and see how this works. Let's say we
want to create a formula that adds 1 to the value over here in this column x. So let's type in the
LAMBDA function and the first thing it asks for is a parameter or calculation. I'm going to put in x
as an input variable and this can be anything you want to use. It just so happens I'm matching the x
title that I have over here. Then it wants to know what the calculation is. Well let's take x plus
one. That's our formula. One of the things that's tricky about the LAMBDA function when you enter it
directly into your spreadsheet is it needs to know what the input value is for x in this formula.
It doesn't know that it's pulling data from over here so we're going to tell it that the
input value is going to be this value 8 over here in A2. Hit Enter and it's taken that value 8 and
added 1 to it. Let's copy this down and you can see that it uses A3 as the input for this one
and A4 as the input for this one and adds one to each of those values.
The real power of LAMBDA is when you name a function of your own. So let's take this x plus
one idea and we'll create our own function called PLUSONE. The way you do that is come up here to
Formulas and go to Name Manager. We're gonna create a new name and we're gonna call this one
PLUSONE and in the formula for that we're gonna have it be a LAMBDA function with an input of x
and a value of x plus 1 for the calculation. That's all you have to do. We'll Hit OK and now it
creates a name called PLUSONE. I'm going to close that and over here I'm gonna reference my PLUSONE
formula and you can see it's in the list now and the parameter that we're gonna pass in is gonna be
this A2 value and there it adds one to that value. We'll copy it down and you can see that it is now
its own function called PLUSONE. That's the real power of the LAMBDA function. We can now use this
newly named function throughout the spreadsheet and it saves with your spreadsheet so you can
come back and use it again in the future. Now let's take LAMBDA a little bit farther. Let's
say we want two input variables instead of one so let's create a column here called y and we'll put
some numbers in here as well. We'll clear this out and we're going to go directly to the Name
Manager and create something that is going to multiply x and y so let's call it MULTIPLYXY
and the formula we want to use here is going to be =LAMBDA and we're going to input both an x
and a y variables and for the calculation we want it to be x times y. Hit OK. There
it creates this MULTIPLYXY and now we can reference our function with input variables for x
and for y and you can see that it multiplies those two values together for your results.
Another feature of the LAMBDA function is that the input value can be a range as well as an
individual value. So for example, let's say we delete these and we edit this A2
value for input and instead of selecting just the A2 value of 8 let's go ahead and do the range
of the entire column and we'll do the same thing on column B. That calculates the values
by multiplying these two across and it spills the results dynamically over the
additional cells. The LAMBDA function handles dynamic values as well. A LAMBDA
function is even more powerful when combined with Data Types. So let's clear this data out
and for x we're going to put in some states.
We're going to highlight that and go to Data and in the list I'm going to
pick Geography as a data type. That assigns the show card for each one of these states
and now if I were to reference them I could say = this state and put a period in and select the
value that I want like Median Household Income. So if we were to take and copy this now let's
create a LAMBDA function. So I'm going to go back to Formulas, go to Name Manager,
create a new, and we're going to call this StateMedianIncome and in the formula let's
put in LAMBDA. We'll pass in a parameter called x which is the state and we're going to paste the
value that we calculated for the median household income, except instead of A2 I'm referencing x.
Put an End Parentheses on that, hit OK, and now it's created our StateMedianIncome.
So now I can do StateMedianIncome and pass in the parameter for this
and now I have my values. So the LAMBDA function works great using Data Types as well
and if we clear this out we can also use a dynamic range with multiple states
and it spills the values over into the additional cells.
One of the most powerful features of the LAMBDA function is its ability to handle recursion.
Recursion is the idea that a function calls itself within that function and it repeats as a loop.
Brian Jones provided a blog entry on the Microsoft tech community that explains how this works and he
gave an example. I've copied that example into a spreadsheet to show you how this works. So here we
have a text string with some sample information. We have illegal characters that we want to remove
from these text strings with the first being numbers, the second being letters, and the third
being special characters and these are the results that come out of that. The function that we're
calling is a LAMBDA function that we've defined and named. I can come up here to the Formulas,
look in the Name Manager, and you can see that I've created this function called ReplaceChars.
The concept behind this LAMBDA function is we pass two parameters in. First the TextString and then
the aIllegalChars. The formula that we're using in the LAMBDA function is this area right here
and you'll see inside of this that it's calling itself. The idea being that it passes in this
TextString, removes the first character from this text string and then it passes in the remaining
characters so that it removes one character at a time all the way down to where there's nothing
left. That creates a loop as it removes each one of those numbers and it puts the results in this
field right here. The concept of recursion is very powerful and this is the first time you've been
able to use this in a function without doing more complex VBA code. So that makes the LAMBDA function
even more powerful than any other function available in Excel. If you want to understand
this in more detail I'll provide a link to Brian's post and you can take a look at some of the detail
explanation he gives. You'll want to note that the LAMBDA function was released in the beta
version of Excel on December 3rd 2020 so it will be a few months before it's available in general
release versions of Excel for Microsoft 365. That explains the new LAMBDA function in Excel.
Be sure to look in the description of this video to find links to Brian's blog post and video links
for Excel Data Types and Excel Dynamic Array Formulas. If you've discovered any other unique
ways to use the LAMBDA function please share them in the comments of this video. Thanks for watching!
Hey, if you want to see more videos like this one please subscribe and if you've enjoyed this
video be sure to click the thumbs-up and leave a comment. I really do appreciate your support!