Microsoft Excel Tutorial - Easiest Way to Create a Lambda function in Excel.
A new version of the Advanced Formula Environment from Excel Labs has been released. It has an amazing new functionality called Import from Grid.
There are many times where I will build a complicated Excel formula in sub-formulas. I finally combine all the sub-formulas into one mega-formula by scooping the subformulas out of the formula bar and pasting in to the final formula.
The new Advanced Formula Envionment, found in the Excel Labs add-in, offers to create a LAMBDA function from existing worksheet logic in the grid.
Table of Contents
(0:00) Welcome
(0:11) Build sub-formulas in Excel
(0:29) Scooping sub-formulas into mega-formula
(0:55) Advanced Formula Environment replaced by Excel Labs
(1:27) Getting the Excel Labs add-in
(1:45) Two apps in the Pane
(2:01) Import from Grid
(2:47) Named the Lambda & use in grid
(3:14) Headings in grid used in LET function
(3:58) Wrap-up
(4:21) Outtake: TEXTBEFORE and TEXTAFTER
A new version of the Advanced Formula Environment from Excel Labs has been released. It has an amazing new functionality called Import from Grid.
There are many times where I will build a complicated Excel formula in sub-formulas. I finally combine all the sub-formulas into one mega-formula by scooping the subformulas out of the formula bar and pasting in to the final formula.
The new Advanced Formula Envionment, found in the Excel Labs add-in, offers to create a LAMBDA function from existing worksheet logic in the grid.
Table of Contents
(0:00) Welcome
(0:11) Build sub-formulas in Excel
(0:29) Scooping sub-formulas into mega-formula
(0:55) Advanced Formula Environment replaced by Excel Labs
(1:27) Getting the Excel Labs add-in
(1:45) Two apps in the Pane
(2:01) Import from Grid
(2:47) Named the Lambda & use in grid
(3:14) Headings in grid used in LET function
(3:58) Wrap-up
(4:21) Outtake: TEXTBEFORE and TEXTAFTER
Transcript of the video:
This one is amazing. I just happened upon it last week.
Excel Labs creates a LAMBDA from existing worksheet logic.
How many times have you had a problem where you had to build a formula.
And it was easier just to build the formula in little sub formulas?
So you do step one here, and then reuse that in step two here. And then step three here, and then step four here, and finally you get to the answer.
But now it's in five columns or however many columns it took.
And you want to get it back into one column.
So you do this little trick where you scoop it out of the formula editor, control C.
And then find every place where we mentioned B2, and put that in with paste and paste, and you just keep going.
And before you know it, you have some insanely long formula, like this formula out here that you will never be able to explain to your coworker. I hate that.
Well, if we go all the way back to February 8th of 2022, there was a video about the new advanced formula environment, this is a great way to edit LAMBDAs.
And I had heard recently that this had been replaced by a new add-in called Excel Labs, and I just hadn't gotten around to downloading it.
But then I had a new laptop, and I was missing the advanced formulator, so I went to get it and I decided, "Well, let's install the new one".
And I was amazed that it had the ability to take existing worksheet logic and automatically create a LAMBDA. First, let's talk about how to get the add-in.
So you go to insert, get add-ins, search for Excel Labs.
Excel Labs, a Microsoft Garage project, click add, and continue.
So what we get in the new Excel Labs pane is the advanced formula environment, and then some crazy thing where they're actually bringing ChatGPT right in to do work from Excel cells.
We're not talking about that today, you can check that out.
But I was intrigued by this icon out here called Import from Grid.
And so we provide the range providing the calculation.
For the cell to treat as parameter, that's the input cell in A2, and then the output cell, they assume to be the last thing. Click preview, and check this out.
They took all of my existing worksheet logic, and encapsulated it in a LAMBDA with the LET function already built in inside.
This is infinitely better and easier to explain than the method that I used by scooping each sub formula out from the formula bar.
Give the LAMBDA a name and click create.
Point to my phrase, double click to copy it down. Now check this out.
Look at the LAMBDA that they built over here.
They used the headings in the row above my calculation in order to name the variables in the LET function.
We're all waiting for co-pilot to happen, this isn't co-pilot, but heck, it should be co-pilot.
What an amazing tool to very quickly create LAMBDAs.
I mean, Excel LAMBDA, you're watching this, I'm always impressed that the LAMBDAs that you create...
In fact, there'll be times where I post a video just saying, "Boy, I hope Excel LAMBDA gives me a LAMBDA for this".
Well, look, now I can just use the Excel Labs advanced formula environment to come in here and generate my LAMBDAs automatically.
It's a pretty amazing tool. So check that out.
Go to insert, get add-ins, download Excel Labs, and test out the great new advanced formula environment. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, like, subscribe, and ring the bell.
Feel free to post any questions or comments down in the comments below.
This original video about the LET function predates the great new text before and text after functions.
So the fact that there is a much easier way to create this formula today does not change how impressed I am because there's a lot of different formulas where we build these sub formulas, and then finally come up with the answer.
The fact that this one has already been made easier with the new formulas that just came out last year doesn't change how amazing the Excel Labs advanced formula environment import from grid functionality really is.
Excel Labs creates a LAMBDA from existing worksheet logic.
How many times have you had a problem where you had to build a formula.
And it was easier just to build the formula in little sub formulas?
So you do step one here, and then reuse that in step two here. And then step three here, and then step four here, and finally you get to the answer.
But now it's in five columns or however many columns it took.
And you want to get it back into one column.
So you do this little trick where you scoop it out of the formula editor, control C.
And then find every place where we mentioned B2, and put that in with paste and paste, and you just keep going.
And before you know it, you have some insanely long formula, like this formula out here that you will never be able to explain to your coworker. I hate that.
Well, if we go all the way back to February 8th of 2022, there was a video about the new advanced formula environment, this is a great way to edit LAMBDAs.
And I had heard recently that this had been replaced by a new add-in called Excel Labs, and I just hadn't gotten around to downloading it.
But then I had a new laptop, and I was missing the advanced formulator, so I went to get it and I decided, "Well, let's install the new one".
And I was amazed that it had the ability to take existing worksheet logic and automatically create a LAMBDA. First, let's talk about how to get the add-in.
So you go to insert, get add-ins, search for Excel Labs.
Excel Labs, a Microsoft Garage project, click add, and continue.
So what we get in the new Excel Labs pane is the advanced formula environment, and then some crazy thing where they're actually bringing ChatGPT right in to do work from Excel cells.
We're not talking about that today, you can check that out.
But I was intrigued by this icon out here called Import from Grid.
And so we provide the range providing the calculation.
For the cell to treat as parameter, that's the input cell in A2, and then the output cell, they assume to be the last thing. Click preview, and check this out.
They took all of my existing worksheet logic, and encapsulated it in a LAMBDA with the LET function already built in inside.
This is infinitely better and easier to explain than the method that I used by scooping each sub formula out from the formula bar.
Give the LAMBDA a name and click create.
Point to my phrase, double click to copy it down. Now check this out.
Look at the LAMBDA that they built over here.
They used the headings in the row above my calculation in order to name the variables in the LET function.
We're all waiting for co-pilot to happen, this isn't co-pilot, but heck, it should be co-pilot.
What an amazing tool to very quickly create LAMBDAs.
I mean, Excel LAMBDA, you're watching this, I'm always impressed that the LAMBDAs that you create...
In fact, there'll be times where I post a video just saying, "Boy, I hope Excel LAMBDA gives me a LAMBDA for this".
Well, look, now I can just use the Excel Labs advanced formula environment to come in here and generate my LAMBDAs automatically.
It's a pretty amazing tool. So check that out.
Go to insert, get add-ins, download Excel Labs, and test out the great new advanced formula environment. I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
If you like these videos, please, down below, like, subscribe, and ring the bell.
Feel free to post any questions or comments down in the comments below.
This original video about the LET function predates the great new text before and text after functions.
So the fact that there is a much easier way to create this formula today does not change how impressed I am because there's a lot of different formulas where we build these sub formulas, and then finally come up with the answer.
The fact that this one has already been made easier with the new formulas that just came out last year doesn't change how amazing the Excel Labs advanced formula environment import from grid functionality really is.
