Guide Dynamic arrays
I want one number per row, without a helper column
A helper column works. It also has to be filled down, kept in step with new rows, and explained to whoever inherits the file. BYROW does the same job from one cell.
=BYROW(range, LAMBDA(row, calculation)) runs your
calculation on each row and returns a single column of answers. BYCOL does the
same across columns. The result spills and resizes itself, so rows added later are included without
anyone filling anything down. Both need Microsoft 365, because both take a LAMBDA.
What is wrong with a helper column
Usually nothing. It is clear, it is easy to debug, and everyone understands it. Reach
for BYROW when one of these bites:
- It stops part way down. Somebody added rows and did not fill the formula, so the last forty rows are blank and the total is quietly wrong.
- It has to be a single value. A formula that needs one number per row inside a larger calculation cannot use a column that lives somewhere else.
- There is nowhere to put it. A protected sheet, or a layout that has no free column.
Where none of those apply, a helper column is still a perfectly good answer, and often the kinder one for whoever maintains the file next.
| What you see | What actually happened | What fixes it |
|---|---|---|
| Helper column stops part way | Nobody filled it down after adding rows | BYROW resizes itself |
MAX across a row of a range fails | Most functions take the whole range, not per row | BYROW hands them one row at a time |
#CALC! from BYROW | The LAMBDA returned more than one value | It must return exactly one per row |
#NAME? | Not available in your version | Use a helper column |
| Slow on a large range | The LAMBDA runs once per row | Keep the calculation simple, or bound the range |
How to use BYROW
Write the calculation for a single row first
Prove the logic on row 2 with ordinary references. If you cannot get one row right,
wrapping it in BYROW will not help.
Wrap it in BYROW with a LAMBDA
The LAMBDA receives one row at a time, as a small range:
=BYROW(A2:C100,LAMBDA(row,MAX(row)-MIN(row)))You choose the name row; it stands for whichever row is being handled.
The result is one column, as tall as the range.
Make sure it returns exactly one value per row
BYROW builds a single column of answers, so each call must give it
exactly one value. A LAMBDA returning a range produces #CALC!.
Wrap anything that might return several in something that reduces it —
SUM, MAX, TEXTJOIN, COUNT.
Use BYCOL the same way, across columns
Same shape, other axis:
=BYCOL(A2:C100,LAMBDA(col,SUM(col)))Returns one row with a total per column. Useful under a block where the number of columns changes.
Everything on this page, as a workbook you can use on your own data
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.
Where it beats a helper column properly
Counting how many cells in each row exceed a threshold is awkward as a helper column and clear here:
=BYROW(B2:M100,LAMBDA(r,SUM(--(r>100))))The double minus turns TRUE and FALSE into 1 and 0 so SUM can add them. It reads
oddly and it is the standard way to count conditions inside an array.
The functions that take a LAMBDA
BYROW,BYCOL— one answer per row or columnMAP— one answer per cellSCAN— a running total, keeping what came beforeREDUCE— the whole range down to one valueMAKEARRAY— builds a block from scratch by position
SCAN is the one worth knowing after BYROW: a running balance without
filling a formula down.
When not to use it
If the per-row number is something people need to see, put it in a column where they can
see it. BYROW is best where the number is an intermediate step. A workbook that hides
every intermediate value inside one clever formula is harder to check, and being checkable is usually
worth more than being clever.
Questions people ask
What does BYROW do?
It runs a calculation on each row of a range and returns one answer per row, as a single spilled column, replacing a helper column filled down.
Why do I get #CALC! from BYROW?
The LAMBDA returned more than one value for a row. Each call must produce exactly one — wrap it in SUM, MAX, COUNT or TEXTJOIN.
Is BYROW better than a helper column?
Not always. Use it when the helper column keeps getting out of step with new rows, when the value must be a single expression, or when there is no free column. Otherwise a helper column is easier for the next person to check.
What is the difference between BYROW and MAP?
BYROW hands your LAMBDA a whole row at a time. MAP hands it one cell at a time and returns an answer per cell.
Which versions have these?
Microsoft 365 only. They take a LAMBDA, which does not exist in Excel 2021, 2019 or LibreOffice.
How do I do a running total?
SCAN is the function for that — it keeps the value so far as it moves through the range, giving a running balance without filling a formula down.
Related guides
Take the workbook with you
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.