The LAMBDA Function Explained - How to Create Custom Functions in Excel

In this video, learn how to create custom functions with the new LAMBDA feature of Excel. This covers everything you need to know to get started, best practices, using LAMBDAS in other workbooks, and more.

Related Videos:

Office Insiders – Seven New LAMBDA Functions:

#MsExcel #ExcelCampus
00:00 Introduction
01:36 How to use LAMBDA
08:19 How to calculate the LAMBDA
09:14 Reusability
09:56 Using Lambdas in Other Workbooks
11:27 Conclusion

[wayneedmondson]

Hi Jon. A very nice tutorial and explanation of the new LAMBDA function. As always, your examples and step by step approach make it easy to follow and understand. Thanks for sharing :)) Thumbs up!!

[MrSweck]

GREAT!!! I learned about this today and an hour after it I had already implemented it into my work. I use to write macros for people around the organisation and people like what they can do but they don't like handling macros very much... if it's new, then it's scary. Recently I started to do a few of the simpler ones in dynamic array version too. They could help with the same things but instead many of them turned into very long and confusing formulas where there was challenging for them to replace references and variables to make them work. But THIS is perfect. Thank you very much!

[IvanCortinas_ES]

Excellent explanation. Very brilliant Jon. Thank you for this great tutorial!

[indzara]

Nice video, Jon. Fully agree. This can change the future of Excel development. We can hide all the complexities of the formulas and make it simple and clean for end-users. I can't wait for this to be available for all users. Thanks for the clear explanation in the video. Best wishes.

[dreivance]

Interesting. So much more wonders with this new thing coming! Thanks, Jon, as always.

[dontown]

Excel has sure come a long way since ver.5 (1993) when it came on 11 floppy discs. Great video. Thanks

[nadermounir]

Thank you Jon for this great explanation. Thank you for sharing this knowledge. I think it is great function for any user but setting it up may require an advance user

[antygona-iqew]

Great function, certainly will allowed to extend the use of some spreadsheet with complex formula to less advanced users.

[ltangst]

Thanks very much! This is cool and great.

[JennTheWriter]

WOW! This is great! Thank you for the video to explain it all. :)

[jazzista]

Hi Jon. Great posting. It was my understanding that those new functions that MS has released specially BYROW and BYCOL was to make the LAMBDA function easier to use instead of using name ranges as arguments inside the function. I need to play around some with this new function that is now available in my excel version

[teoxengineer]

Thank you for good explation of LAMBDA.
Maybe, we can use a Lambda function by using it with VBA. If we create a Lambda function in a workbook and use it with VBA, you can take benefits of this function if we save it as Personal Macro Workbook and use it different workbooks without opening it again and again.
It maybe be tested accordingly

[darrylmorgan]

Looking Forward To Having Fun With The LAMBDA Function.Unfortunately Most Of The Projects I Work On I'm Using Either Power Bi, Tableau Or Python...Great Tutorial Thank You Jon :)

[patrickschardt]

Hey Jon, great tutorial and explanation as always. You always explain everything so well.

LET is great friends with LAMBDA. With that in mind since AVERAGE_TOP & AVERAGE_BOTTOM you could probably combine them into AVERAGE_TOPBOT. Just add a third argument.
=AVERAGE_TOPBOT(Range, Percent, Top_Bot)" *I had to replace the "." with "_" to avoid auto deletion.

Enter 1 or 0 for Top or Bottom percentage respectively or maybe "T" for Top or "B" for Bottom or vice versa. Then using LET and other functions modify the logic for the AVERAGEIF Criteria argument.

[duncanwil]

Hi Jon, You have presented a good quality, real word example for us. I have just taken the opportunity to use LAMBDA functions and I am working on a series of examples. I was creating a CAGR (Compound annual growth rate) and it returned errors because of the nature of my data inputs. So, I wanted to use IFERROR() inside my LAMBDA() function but it seems impossible ... is it?

[kylebuggie]

Are there any performance considerations using LAMBDA? As you mentioned, it will likely be used with more complex formulas. I wasn't sure if wrapping this around it would affect the time to process. Great explanation!

[davidwelsh]

Excellent description. Thank you. You compare using LAMBDA to using a formula. What do you see as the pros and cons of using LAMBDA with user defined functions?

I use user defined functions often to build Excel tools for other users, but my user defined function references in the workbook that I intend to reference the user defined function associated with the current workbook, but without my knowing switch to referring to user defined functions of the same name on my local computer. These functions then only work on my computer. A different user on a different computer must then edit the function references to remove the path portion of the reference such that the formula will point to the version of the function in that workbook. Would using LAMBDA's resolve this issue? Is there another way to resolve this issue with user defined functions?

[smanzoli]

It is very easy to create new functions to use in the cells using VBA Functions (not subs).

Lambda let us do SIMPLE functions, without VBA. Nice. But VBA allows more complex functions since it's a full program, and Lambda is just one formula.

[ChristianLouiseMagpantay]

Is there a lambda function for spelling out numbers or currency into words?

[CreativelyChallenged]

I want to get this, my concern is that I get it to do all this work but then be unable to share what I have done because they don't have it. Am I able to get this, and when I share documents they will see whatever I have done without needing the add-in themselves?