Excel MAP, REDUCE & BYROW — Loop Over Arrays with LAMBDA Helpers

TL;DR — MAP, REDUCE, SCAN, BYROW and BYCOL are higher-order functions: they take a LAMBDA and run it across an array — For Each without a macro. The #1 gotcha: BYROW/BYCOL's LAMBDA must return one value per row/column, or you get #CALC!. Pick the helper by the shape of the answer you want. Excel 365 and 2024+.

=MAP(A2:A100, LAMBDA(v, IF(v<0, 0, v)))          ' transform each cell
=BYROW(data, LAMBDA(row, SUM(row)))              ' one total per row
=REDUCE(0, A2:A100, LAMBDA(acc, v, acc + v))     ' collapse to a single value

LET gave you variables. LAMBDA gave you functions. The helpers give you the last piece of a programming language: loops. Each one takes a LAMBDA and runs it over an array, so you never write For Each — you describe what to do to each element, and Excel iterates.

What you'll learn

• The mental model: the loops — feed a LAMBDA to an array
• Which helper does what: MAP (transform), REDUCE (collapse), SCAN (running totals), BYROW/BYCOL (one value per row/column)
• The #1 rule: BYROW must return a single value — the #CALC! fix for "a sum per row"
• The REDUCE accumulator pattern — and when a plain SUMIFS still wins

The mental model: these are the loops

There are five helpers, and they split cleanly by what shape comes out:

• MAP — transform each element → an array the same shape (10 cells in, 10 cells out).
• REDUCE — collapse an entire array down to one value (a running accumulator: total, concatenation, max).
• SCAN — like REDUCE, but keeps every intermediate step → a running total spilled down the column.
• BYROW / BYCOL — run the LAMBDA on each whole row (or column) → one value per row (or column).

Once you sort them by output shape — same shape, one value, every step, one-per-row — choosing the right helper stops being guesswork.

Copy-paste patterns

MAP — apply a transform to every element:

=MAP(A2:A100, LAMBDA(v, IF(v<0, 0, v)))

REDUCE — boil an array down to a single value (the accumulator acc carries the result forward):

=REDUCE(0, A2:A100, LAMBDA(acc, v, acc + v))

SCAN — same idea, but spill every step (a running total):

=SCAN(0, A2:A100, LAMBDA(acc, v, acc + v))

BYROW — one value per row (here, the max of each row):

=BYROW(A2:D100, LAMBDA(row, MAX(row)))

The rule that breaks BYROW: return one value per row

This is the helper people get wrong, and the symptom is #CALC!. The LAMBDA you give BYROW (or BYCOL) must return a single value for each row — a number, a text string, one cell. If it returns an array, BYROW can't fit it and throws #CALC!.

=BYROW(data, LAMBDA(row, SUM(row)))      ' one number per row -> works
=BYROW(data, LAMBDA(row, row*2))         ' #CALC!  — row*2 is an array, not one value

That second case is really a MAP job (transform every element), not a BYROW job. The mistake goes the other way too: people reach for MAP to get "a sum per row" and can't make it work — because MAP is element-wise and never aggregates a row. If you want one value per row, that's BYROW. If you want to transform every cell, that's MAP. Output shape decides.

The REDUCE accumulator: the one pattern to internalise

REDUCE is the most powerful and the least intuitive, because of acc — the accumulator that carries the running result from one element to the next. Read LAMBDA(acc, v, …) as "here's the result so far (acc) and the next value (v); return the new result-so-far."

• Sum: LAMBDA(acc, v, acc + v) starting at 0
• Concatenate: LAMBDA(acc, v, acc & ", " & v) starting at ""
• Conditional count: LAMBDA(acc, v, acc + (v>100)) starting at 0

Once acc clicks, REDUCE handles the "walk the list and build something up" jobs that used to force you into VBA — running balances with rules, custom aggregations, parsing loops.

The judgment call: when a plain aggregate still wins

These helpers iterate in the calculation engine and can be slow on very large arrays. For a plain sum, SUM/SUMPRODUCT/SUMIFS are faster and simpler — reach for the helpers when the per-element logic is genuinely custom, not when a built-in aggregate already does the job.

If you're dragging a running-total formula down 10,000 rows, that's a SCAN; if you're adding a helper column just to compute one value per row, that's a BYROW. But if a SUMIFS already answers the question, use it — the helpers are for custom logic, not for re-implementing aggregates.

How ExcelMaster helps

Higher-order functions are the steepest part of modern Excel — acc, output shapes, MAP-vs-BYROW — and exactly where an AI copilot saves the most time. ExcelMaster turns "give me the max of each row" or "a running balance that resets each month" into the right MAP/REDUCE/BYROW formula, picks the correct helper for the output shape you want, and explains the accumulator step by step. When a helper throws #CALC!, it tells you whether you needed BYROW instead of MAP — and rewrites it. Try it on your own data.

Frequently asked questions

What are the LAMBDA helper functions in Excel?

MAP, REDUCE, SCAN, BYROW and BYCOL are higher-order functions: each takes a LAMBDA and runs it across an array. They let you loop over data in a formula — transforming, aggregating, or accumulating — without writing a VBA macro.

Why does BYROW return #CALC!?

The LAMBDA you passed returns an array instead of a single value. BYROW needs one value per row — aggregate the row with SUM(row), MAX(row), etc. If you actually want to transform every element, use MAP, not BYROW.

What is the difference between MAP and BYROW?

MAP works element-by-element and returns an array the same shape as the input. BYROW works on whole rows and returns one value per row. Use MAP to transform every cell; use BYROW to get a per-row total, max, or count.

How does REDUCE work in Excel?

REDUCE(initial, array, LAMBDA(acc, v, …)) walks the array carrying an accumulator acc. For each value v it computes a new acc. =REDUCE(0, A2:A100, LAMBDA(acc, v, acc+v)) sums the range; starting at "" with acc & v concatenates it.

Which Excel versions have MAP, REDUCE and BYROW?

The LAMBDA helpers are available in Excel 2024 and Microsoft 365 for Windows and Mac. They are not in Excel 2021, 2019, or 2016.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-22.

Related guides: Excel LAMBDA · Excel LET · Excel SORT · Excel SEQUENCE