Formulas & Functions•7 min read
Excel LAMBDA Helper Functions: MAP, REDUCE, SCAN & BYROW
Master Excel LAMBDA helper functions: BYROW, BYCOL, MAP, SCAN and REDUCE for running totals and per-row logic.
SheetHub••7 min
One formula. 10,000 rows. Zero dragging.
That is the difference between writing a calculation once and writing it for every row in your sheet. Excel LAMBDA helper functions let you apply one reusable calculation across rows, columns, or individual cells without dragging formulas. If you have ever copied a formula down a column and watched it slow to a crawl, these helpers can replace that repeated fill operation.
If you have not written a LAMBDA yet, start with our LAMBDA guide. Helpers take a LAMBDA as their engine, so the syntax has to feel familiar before they make sense.
A LAMBDA is a formula you can name and reuse, written as parameters followed by a calculation:
On its own this returns a function rather than a result. Pass it to a helper function and Excel calls it once per row, per column, or per cell. You describe the logic once, and Excel applies it throughout the range.
All five helpers (BYROW, BYCOL, MAP, SCAN, REDUCE) require Excel for Microsoft 365, version 2022 or later. They do not exist in Excel 2021, 2019, or earlier, so older versions return
BYROW takes a range and a LAMBDA, runs the LAMBDA once per row, and returns one result per row in a single spilled column:
Here
Think of BYROW as a per-row loop. Whatever you write inside the LAMBDA runs independently for every row, which is how you avoid dragging formulas.
BYCOL mirrors BYROW. The LAMBDA receives one whole column at a time and returns one result per column, spilled horizontally:
This returns the average of columns A, B, and C in adjacent cells. Swap
MAP is the per-element helper. Instead of one result per row, it gives you one result for every single cell in the input:
Every value in
SCAN keeps a running value as it walks through the array, returning every intermediate step. The first argument is the starting value:
Start with 0, add
This counts "Yes" values as it goes, so every row shows how many yeses have appeared so far.
REDUCE does the same walk as SCAN but keeps only the final result:
That single formula equals
This joins every non-empty cell into one comma-separated string. SCAN returns every intermediate value, while REDUCE returns only the final value.
MAKEARRAY builds a grid with LAMBDA, while BYROW and MAP apply logic to existing ranges. Generate the grid with MAKEARRAY, then summarize it with a helper:
MAKEARRAY is part of Excel's broader dynamic-array toolkit. For the surrounding spill behavior and array patterns, see our guide to Excel dynamic array functions.
Do LAMBDA helper functions exist in Google Sheets? No. Sheets has no BYROW, SCAN, or REDUCE. The closest equivalents are
Prerequisites: LAMBDA in 60 seconds
=LAMBDA(x, x * 2)#NAME?.
BYROW: apply logic per row
=BYROW(A2:C100, LAMBDA(r, SUM(r)))r is one whole row as a horizontal array. SUM(r) totals that row, and BYROW repeats it for all 99 rows. The result spills down, one total per row, with no fill handle involved.
The LAMBDA is not limited to SUM. Any calculation that accepts an array works, so you can compute a standard deviation or a max per row:
=BYROW(B2:B100, LAMBDA(r, STDEV.P(r)))
=BYROW(C2:C100, LAMBDA(r, MAX(r)))BYCOL: apply logic per column
=BYCOL(A2:C100, LAMBDA(c, AVERAGE(c)))AVERAGE for COUNT, MIN, or another single-array function to create a column summary in one formula.
| Helper | Unit of Work | Result | Typical Use |
|---|---|---|---|
| BYROW | One row | One value per row | Sums, max, standard deviation per row |
| BYCOL | One column | One value per column | Averages, counts per column |
| MAP | One cell | One value per cell | Unit conversion, cell-level validation |
| SCAN | One cell | One value per cell, cumulative | Running totals, running balances |
| REDUCE | One cell | One final value | Conditional totals, custom aggregation |
MAP: transform every cell
=MAP(A2:A100, LAMBDA(x, x * 2))A2:A100 gets doubled, and the results spill into a column of the same size. This is the classic unit-conversion move: multiply prices by a tax rate, convert kilograms to pounds, or normalize scores.
MAP operates on each cell independently, so its LAMBDA gets single values. BYROW hands you an entire row array, which is why SUM(r) works there but not inside MAP. Use MAP for cell-level transformations and BYROW for row-level aggregations.
SCAN: running totals
=SCAN(0, A2:A100, LAMBDA(acc, x, acc + x))A2, then add A3 to that result, and so on. The output is a column of running totals: the first cell equals A2, the second equals A2 + A3, and the last cell equals the grand total. That makes SCAN the tool for running balances, cumulative sales, or a running count of events:
=SCAN(0, C2:C100, LAMBDA(acc, x, acc + IF(x = "Yes", 1, 0)))REDUCE: collapse to one value
=REDUCE(0, A2:A100, LAMBDA(acc, x, acc + x))SUM(A2:A100). REDUCE becomes useful when the accumulator does more than add numbers. You can concatenate text, find a value that meets a condition, or build a result SUM cannot express:
=REDUCE("", B2:B100, LAMBDA(acc, x, acc & IF(x <> "", x & ", ", "")))Combining with MAKEARRAY
=BYROW(MAKEARRAY(9, 9, LAMBDA(r, c, r * c)), LAMBDA(row, SUM(row)))Error handling and limitations
- Excel 2021 and 2019 do not recognize these functions, so
#NAME?means the version does not support helpers. Check that the file opens in Excel for Microsoft 365. - MAP with one range needs a one-parameter LAMBDA, while MAP with two ranges needs two. A mismatch returns
#VALUE!. - An empty array can return
#CALC!. Guard withIFERRORor make sure the range has data. - Starting text concatenation with
0instead of""produces#VALUE!in SCAN or REDUCE. - Helpers on 100,000+ rows are slower than native aggregations such as SUMIFS.
- A failing LAMBDA inside another LAMBDA produces a cryptic error with no trace, so break the logic apart before nesting.
Pro tips
- Wrap long helper formulas in LET to name the LAMBDA and keep the formula readable. Naming the LAMBDA once and reusing it inside BYROW or MAP cuts the formula length in half.
- Combine MAP with FILTER for conditional transformation: filter first, then transform only the rows that survive.
- Store a LAMBDA in the Name Manager once and reference it by name inside helpers, the same pattern as a custom function minus VBA.
Common mistakes to avoid
- BYROW passes a whole row, while MAP passes single cells.
SUM(r)works in BYROW and fails in MAP. - SCAN and REDUCE both require an initial value. Leaving it out returns
#VALUE!. - The helpers do not run on Excel 2021. The
#NAME?error comes from the Excel version, not the formula.
FAQ
ARRAYFORMULA for element-wise work. See our Excel vs Google Sheets formula differences for a side-by-side look.
Why do I get #NAME? even in Microsoft 365? The helper functions shipped in 2022, so older Microsoft 365 builds can still return #NAME?. Update Excel to the current channel.
Can helpers work with text? Yes. MAP accepts any LAMBDA expression, and REDUCE concatenates text through its accumulator.
Do I need to confirm the formula with Ctrl+Shift+Enter? No. Helpers are dynamic array functions, so they spill automatically. A normal Enter is all it takes.
Start with BYROW and MAP for row or cell transformations. Use SCAN for running totals and REDUCE when you need one final result. Each helper replaces a repeated formula with one description of the calculation.Article Topics
Recommended Next Reading
Excel
=EXCEL(...)Excel LAMBDA Recursive Loops: Advanced Calculations
Explore ↗
Excel
=EXCEL(...)Excel ROUND Function: MROUND, CEILING & FLOOR Guide
Explore ↗
Excel
=EXCEL(...)Excel REGEXEXTRACT: Extract Patterns and Substrings
Explore ↗
Share this tutorial
Discussion & Community
Share questions, tips, or edge-cases about this spreadsheet formula.