LAMBDA’s Little Helpers
The holidays are upon us and this year Santa is not the only one who has some little helpers. Excel has given us a stocking suffer known as LAMBDA’s little helpers.
Image by Frauke Riether from Pixabay
LAMBDA is one of the newest additions to Excel and is currently only available in the beta version of Excel. If you are interested about learning how to subscribe to the insider channel of Excel so you can test these formulas before they are released to the general public you can go to the following link and it will walk you through the process.
How to become Office Insider - Excel Exercise (excel-exercise.com)
LAMBDA allows us to create our own custom functions in the formula bar and then use the name manager to name the function and reuse it throughout the Excel document. This is a big step forward as it continues to build upon all the changes the Microsoft team has made to Excel the last few years to increase the ways we can use Excel to make our lives easier. For more information on LAMBDA jump over to my LAMBDA blog here.
Microsoft so far has created 7 LAMBDA helper functions. These functions are primarily used as inputs to LAMBDA and extend and expand the ways one would use LAMBDA. A good article to introduce you to these 7 functions can be found on the Microsoft Excel Blog and it is written by Chris Gross.
Announcing LAMBDA Helper Functions: Lambdas as arguments and more He gives a refresher on what the LAMBDA function is and how it works and then he goes into each of the 7 functions.
Lets talk briefly about each of the 7 functions then we will discuss some of the best resources on the web for learning more about these little “stocking stuffers” or as Office-Watch.com calls them LAMBDA extensions. Definition and syntax for each function from Microsoft Help in Excel below:
MAP - Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value
Syntax: =MAP (array1, lambda_or_array<#>)
Reduce - Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator
Syntax: =REDUCE([initial_value], array, lambda(accumulator, value))
ByRow - Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
Syntax: =BYROW(array, lambda(row))
ByCol - Applies a LAMBDA to each column and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 3 columns by 1 row.
Syntax: =BYCOL (array, lambda(column))
Scan - Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.
Syntax: =SCAN ([initial_value], array, lambda(accumulator, value))
MakeArray - Returns a calculated array of a specified row and column size, by applying a LAMBDA.
Syntax: =MAKEARRAY(rows, cols, lambda(row, col))
IsOmitted - Checks whether the value in a LAMBDA is missing and returns TRUE or FALSE.
Syntax: =ISOMITTED(argument)
Many of you after looking at the above descriptions are probably wondering how you would ever use these functions. I will admit that is what I thought when I first read about them as I thought this stuff is way over my head but then after watching some videos and practicing with a couple of the formulas I realized that at least two of the formulas would be a big help for me, today.
Excel Sales Table: Table created using dynamic array formula and totals created using LAMBDA helper formula BYCOL. =BYCOL(B2#, LAMBDA(MYcol, SUM(MYcol)))
So lets jump into how one could use a helper formula. The above image shows a table of sales people with monthly performance. Today when we add a new month most people will just drag over the totals forula at the bottom to accommodate the new month. With LAMBDA and the BYCOL function you can create a formula that will automatically sum the table as one formula, and dynamically adjust when new columns are added to the table.
In cell b7 I entered the following function =BYCOL(B2#,LAMBDA(Mycol,SUM(Mycol))).
The first argument is taking the range of the dynamic array which is referenced by using the nomenclature B2# which takes the range (B2:M8) which in this case gives us everything from Jan to December in columns and Doug to Cat in rows.
We then put this range inside the LAMBDA and call range mycol and we then sum mycol and it creates array from cells B9:M9 summing each column of data from row 2:8. The formula lives in cell B9 and dynamically expands to spill results from column B to column M. If we added another year to the columns the formula would automatically expand to sum those columns as well.
This is one of many examples of how LAMBDA Helpers can be used in excel. Next we will review some of the webs best resources for learning more about these functions.
Best websites to learn more about LAMBDA’s Little Helpers
We will start today with one of my favorite sites on the web for learning various Excel Functions.
The FIRST site is ECXELJET, run by EXCEL MVP Dave Bruns and his moto is Quick, clean, and to the point and it is true. His stated goal on his website is to help you work faster in Excel and that he does.
The below link will take you to his website and will provide an example of how to use the Excel Scan function.
EXCELJET website showing how to use the SCAN function
The above image only captures a part of all the detail contained on each function. After providing a summary he also includes for each formula:
Formula Syntax
Arguments
Version
Usage Notes
Examples
Other details as needed
The Second website is the sumproduct.com and they have a blog series called A to Z of Excel Functions and in this series they cover a three of the new LAMBDA helper functions. They provide details for bycol, byrow, and isommitted in separate blogs. At present not a lot has been written about these functions as they are still in beta and relatively new but over time the content for these functions will grow.
Best Youtube Videos for learning about LAMBDA’s Little Helpers
The first video I recommend is by the master of Youtube Excel videos Mike “ExcelisFun” Girvin. This videos provides a brief explanation of all 7 videos with examples on how you can use each of them. His video was how I learned to use the ByCol and ByRow functions.
2. The second video is by Sele Training and the video explains how LAMBDA works and then how the LAMBDA helper functions work as inputs to LAMBDA. It briefly goes over each function in one or two minutes showing a usage case for each function.
3. The third video on my list is on that gives real world examples of how you would use the ByCol and ByRow functions and the video is by Leila Gharani one of the most follows on Youtube.
4. The fourth and final video on this list is by one of the OG’s of Excel, MR. Excel himself. He has a ton of Excel content including his website, videos, and multiple Excel books. This video looks at the ByCol and ByRow functions and how we use them in Excel.
Conclusion
I hope you will find the above resources helpful in your Excel Journey. Today’s blog is for the advanced user of Excel as Excel LAMBDA and Excel LAMBDA helper functions are not for beginners. That being said those who want to expand what is capable in Excel and push the limits will find these new functions very useful. As always leave your comments on this blog below.