Excel like a finance pro.
BYROW & BYCOL
Dynamic arrays (Microsoft 365)Run a calculation down every row at once — no helper column.
Difficulty
1What is it?
Some things can't be done with a plain array formula because they need a calculation performed per row rather than element by element — a row maximum, a row average, a count of months above target. The old answer was a helper column dragged down. BYROW applies a LAMBDA to each row and returns one value per row, as a single spilled formula. It's the natural next step after LET and LAMBDA, and the first time a LAMBDA does something you genuinely could not do another way.
2What it looks like
BYROW(array, LAMBDA(row, calculation)) · BYCOL(array, LAMBDA(col, calculation))- array
- The grid to walk.
- LAMBDA(row, …)
- A function taking ONE argument — the current row as an array — and returning ONE value. Returning more than one value is the usual mistake.
3When you use it
- The best month for each account, as one formula beside the grid.
- Count how many months each department was over budget.
- Column totals under a grid without a row of SUMs.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Pick what to compute per row. One formula covers the whole grid.
=BYROW(B2:D4, LAMBDA(r, MAX(r)))Revenue $44,100| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Account | Jul | Aug | Sep | Result |
| 2 | Revenue | 41,000 | 38,500 | 44,100 | Revenue $44,100 |
| 3 | COGS | 23,400 | 22,100 | 25,800 | COGS $25,800 |
| 4 | Travel | 3,100 | 900 | 2,400 | Travel $3,100 |
| 5 | The LAMBDA must return ONE value — MAX, AVERAGE, SUM. Return an array and you get #CALC!. |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#CALC!The LAMBDA returns an array rather than a single value — the most common mistake here.Fix: Wrap the body in something that collapses it: MAX, SUM, TEXTJOIN, COUNT.
#VALUE!The LAMBDA takes the wrong number of arguments.Fix: Exactly one parameter. BYROW supplies the row and nothing else.
#NAME?LAMBDA isn't available in this version.Fix: Needs Microsoft 365. Older builds have no LAMBDA at all, so there's no fallback short of a helper column.
Results look transposedBYCOL was used where BYROW was meant, or the other way round.Fix: BYROW returns a COLUMN of results, one per row. BYCOL returns a row.
6Better functions & alternatives
- A helper column — Still perfectly fine, and works in every version. This removes the column, which matters most when the grid grows.
- MAP / REDUCE — MAP walks every CELL rather than every row; REDUCE accumulates across them. Different jobs.
Want BYROW & BYCOL already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.