I didn't think much of LAMBDA helper functions when Microsoft added them to Excel. However, after finally trying them out, they proved to be a revelation. They work with LAMBDA functions, allowing you to apply custom functions to rows, columns, and even entire arrays without writing multiple formulas or complex VBA. While there are seven LAMBDA helper functions in Excel, I will focus on the four I use the most—BYCOL, BYROW, SCAN, and REDUCE.
If you've never worked with LAMBDA functions, these helper functions might sound intimidating. The good news is that you don't need to do a deep dive into LAMBDAs to use them. Before you proceed, though, ensure that you're using Excel 365 or Excel 2024 if you want to use these formulas (Excel 2021 and earlier do not support them).
BYCOL
Apply LAMBDAs to columns
Suppose you're working with wide sales data, where columns represent the products, rows are the months, and cells are the sales figures. If you had to get the total sales for each of them, assuming Product A's sales figures are in B2:B6, the first formula you would write would be something like this in cell B7:
=SUM(B2:B6)
If you have 20 products, you would need to write 20 separate formulas. The good thing is that it's not that time-consuming, since you can just copy the formula to the adjacent cells. The bad thing is that it clutters your spreadsheet, makes formulas harder to update later, and increases the risk of errors.
Alternatively, you can use the BYCOL function. It applies a custom function (LAMBDA) to each column of an array or range and then returns one result per column as a horizontal array.
=BYCOL(array, lambda)
Continuing with the products example. Assuming we have three products and the sales figures are in the range B2:D6, the following formula will sum each of the columns nicely.
=BYCOL(B2:D6, LAMBDA(myCol, SUM(myCol)))
In the LAMBDA, I define a parameter called myCol, which BYCOL will pass one column at a time from the range B2:D6. Then, I use the parameter in the SUM function. Even though I'm passing the entire range, BYCOL will sum the sales column by column and return each one in a separate column.
If you haven't been put off by all this LAMBDA talk, here's the kicker: you don't have to use a LAMBDA. You can use any named function, including SUM, directly. But if you need to do a complex calculation, stick with LAMBDAs.
=BYCOL(B2:D6, SUM)
You can also use BYCOL with other functions in interesting ways. If the product names are in columns B1:D1 (the header row), the following formula, which uses the FILTER function, should return only products with total salesabove $600.
=FILTER(B1:D1, BYCOL(B2:D6, LAMBDA(myCol, SUM(myCol))) > 600, "")
BYROW
Apply LAMBDAs to Rows
The BYROW function applies a LAMBDA to each row in an array or range and returns one result per column as a vertical array. It's ideal for working with long data.
BYROW(array, lambda)
Continuing with the example from the previous section, we can sum each row in a single formula with the BYROW function.
=BYROW(B2:D6, LAMBDA(myRow, SUM(myRow)))
Again, you can use the SUM function directly if the calculation you need is simple.
=BYROW(B2:D6, SUM)
SCAN
Keep track of running totals
The SCAN function is best used when you need a running total—a sum that accumulates row by row, with the accumulated value being shown at each step. It takes a starting value, an array or range, and a LAMBDA function. It then returns an array of the intermediate results, beginning with the starting value.
SCAN(start_value, array, lambda)
If we were to do a running total of Product A from the previous example, the formula would look like this:
=SCAN(0, B2:B6, LAMBDA(a, v, a + v))
Again, this looks complicated, but it’s easy to understand what’s going on once we break it down.
The start_value parameter is 0, meaning we want to start tracking the sales from zero. The array parameter with the value we want to aggregate is B2:B6. In the LAMBDA, a is the running result we’re going to be tracking, and v is the current value of the array.
REDUCE
Many become one
The REDUCE function is like SCAN, but it just gives you the final result only, instead of an array of intermediate results. That is, all the results are accumulated into a single value.
REDUCE(start_value, array, lambda)
To demonstrate its usefulness, I'm not going to use the summing example because it only shows the final result. It won't tell us much. But let's say there's a set of tags in the range A2:A6. I can build a string with them with the following formula:
=REDUCE("", A2:A6, LAMBDA(str, tag, str & ", " & tag))
The start_value parameter is "" (an empty string), and A2:A6 is the range. In the LAMBDA, the str parameter is the string we're building, and the tag is the current tag in the array.
The str & ", " & tag part of the LAMBDA takes the string and applies a tag to it cumulatively. So if str is currently computers, mobile, and tag is video games, the string it has built so far will be computers, mobile, video games.
The problem with the formula above is that there’s a leading comma. We can remove that with the TEXTAFTER function.
=TEXTAFTER(REDUCE("", A2:A6, LAMBDA(str,tag, str & ", " & tag)), ", ")
The power of LAMBDAs unleashed
Initially, I didn't understand the power of these LAMBDA helper functions. I know the LAMBDA part can be a tough concept to grasp. But once I dove in and allowed them to break my brain, I discovered a whole new way to write formulas. Other LAMBDA helper functions you can look into are MAP, MAKEARRAY, and ISOMITTED.