The Excel BYROW function hands each entire row of a range to a LAMBDA and spills back one result per row. It is the one-formula replacement for a totals column you’d otherwise drag down: SUM each row, MAX each row, or any custom per-row verdict. Its twin BYCOL does the same thing column by column.
r receives a whole row (four cells at once), so aggregation functions like MAX, SUM, and AVERAGE are exactly what belongs inside.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array to process row by row. |
lambda | Required | A one-parameter LAMBDA. The parameter receives one complete row as a horizontal vector — not a single cell — and the body must boil it down to a single value. |
Available in: Excel for Microsoft 365 and Excel for the web only. Older versions show #NAME?. The output is a one-column array with exactly one value per input row.
One result per row, no fill handle
Quarterly sales sit in B2:E5, one row per region. A single formula in F2 returns each region’s best quarter:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 | Q4 | Best quarter |
| 2 | North | 410 | 520 | 480 | 610 | 610 |
| 3 | South | 380 | 290 | 350 | 400 | 400 |
| 4 | East | 510 | 545 | 530 | 490 | 545 |
| 5 | West | 300 | 360 | 420 | 455 | 455 |
Inside the LAMBDA, r is the whole row — B2:E2 on the first pass, B3:E3 on the second, and so on. Anything that aggregates a range works on it:
Try it: aggregate each row live
Choose what the LAMBDA does to each row and watch the spilled column update.
Per-row logic and dynamic-array combos
The LAMBDA body can be any expression that returns one value — including conditional verdicts per row:
Pair BYROW with other dynamic arrays. Rank regions by their totals without any helper column:
Or filter a table to rows whose own values pass a test:
Family map: element-by-element transforms are MAP’s job; per-column results are BYCOL; folding the whole array to one value is REDUCE; generating a grid from scratch is MAKEARRAY.
Errors & common pitfalls
#CALC! — the LAMBDA returns an array, not a single value. Each row must collapse to exactly one result. LAMBDA(r, r*2) returns a whole row of values per row — BYROW can’t place it. If you meant to transform every cell, use MAP.
#CALC! — wrong parameter count. BYROW’s LAMBDA takes exactly one parameter (the row vector). Declaring two, or none, fails.
#NAME? — older Excel. BYROW exists only in Excel for Microsoft 365 and Excel for the web. Excel 2021 and earlier don’t recognize it.
Pitfall: thinking the parameter is one cell. Unlike MAP, the parameter is the entire row. LAMBDA(r, IF(r>100, …)) compares a whole vector and misbehaves — wrap the row in an aggregate first: IF(SUM(r)>100, …).
Pitfall: #SPILL! below the formula. BYROW returns one value per input row — those cells must be empty.
Practice workbook
Frequently asked questions
What does the Excel BYROW function do?
What's the difference between BYROW and MAP?
Why does BYROW return #CALC!?
r*2), or the LAMBDA doesn't declare exactly one parameter. Each row must reduce to one result — wrap the row in SUM, MAX, TEXTJOIN, or similar.Can I use BYROW to sum each row of a table?
=BYROW(B2:E100, LAMBDA(r, SUM(r))) spills 99 row totals from one formula. The classic alternative was typing =SUM(B2:E2) and dragging it down.Which Excel versions support BYROW?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class