For most of Excel's history, "apply a calculation to every row in this range" had one answer: write the formula in the first row, then drag it down. The pattern was so deeply baked into how spreadsheets worked that very few people questioned it. Dragging was just what you did.
The questions you should have been asking - what happens when the source range grows, what happens when someone tweaks the formula in row 47 but not the others, what stops the formula from extending itself - had no good answers. You either accepted them or you reached for VBA, Power Query, or a pivot table that warped your data into a shape it didn't want to be in.
BYROW and BYCOL replaced this entire category of work in one stroke. They hand a LAMBDA an entire row or column at a time, return one value per row or column, and the result spills as an array. The formula is in one cell. The output covers the whole range. The drag is gone.
The mental model before the syntax
BYROW is an iterator. You give it a 2D range and a LAMBDA. The LAMBDA accepts a single argument, which is one row of that range as a 1D array. BYROW calls the LAMBDA once for each row, collects the results into a vertical array, and spills.
BYCOL is the same idea rotated ninety degrees. It hands the LAMBDA one column at a time and spills horizontally.
The shift to internalise is what the LAMBDA gets. It is not getting one cell at a time. It is getting an entire row (or column) packaged as a small array, and your LAMBDA body has to pull values out of it by position. This is what INDEX(r, 1) means inside a BYROW LAMBDA: "the first cell of the row I was just given." Once that clicks, the rest follows.
The reason both functions exist together is that 2D ranges have two axes and you want to be able to collapse along either of them. Need a per-row summary? BYROW. Need a per-month total when months are columns? BYCOL.
What this looks like on a real problem
A project tracker with these columns: Project name, Owner, Budget hours, Actual hours, Status, Due date. Rows 2 through 100. For every project, you want a single "Health" label - "Done," "Critical" if actuals are over 110% of budget, "Watch" if between 90% and 110%, "On track" otherwise.
The old way: write the formula in G2, drag it to G100. If new projects get added, drag further. If you change the threshold from 110% to 120%, hope every row's formula matches.
The BYROW way:
=BYROW(A2:F100, LAMBDA(r,
LET(
budget, INDEX(r, 3),
actual, INDEX(r, 4),
status, INDEX(r, 5),
ratio, IFERROR(actual/budget, 0),
IF(status="Done", "Done",
IF(ratio > 1.1, "Critical",
IF(ratio > 0.9, "Watch", "On track")))
)
))One cell. The result spills down column G covering every project. The LAMBDA reads top to bottom because LET names the values it pulls from the row, which makes the logic legible without a parenthesis tree.
The same pattern with a structured table called Projects is even tidier - point BYROW at the relevant columns of the table, and the spill auto-extends when rows are added:
=BYROW(Projects[[Budget]:[Status]],
LAMBDA(r,
LET(
budget, INDEX(r,1),
actual, INDEX(r,2),
status, INDEX(r,3),
ratio, IFERROR(actual/budget, 0),
IF(status="Done", "Done",
IF(ratio > 1.1, "Critical",
IF(ratio > 0.9, "Watch", "On track")))
)
)
)The columns inside the LAMBDA are now numbered relative to the slice you passed in, not the full table. That's why it's INDEX(r, 1) for Budget here and INDEX(r, 3) in the earlier version - the input range changed.
BYCOL does the same shape for column-wise work. Monthly budget data with months as columns and categories as rows? =BYCOL(monthly_data, LAMBDA(c, SUM(c))) gives you a per-month total, spilled horizontally. The structural twin of the previous example, with the axis flipped.
Where it breaks down
Three things to know before this becomes your default.
The spilled output is not a column of the table.
This is the most common surprise. BYROW pointed at a table column-range produces a spilled array that sits next to the table, but it's not part of the table. Sort the table, the spill doesn't reshuffle. Filter the table, the spill doesn't hide rows. If you need the result to behave like a real table column, add a calculated column inside the table or do the work in Power Query. BYROW is for the worksheet, not the data model.
Performance degrades with expensive LAMBDAs.
BYROW calls the LAMBDA once per row, with no clever vectorisation. If the LAMBDA does an XLOOKUP against another large table, you're running N XLOOKUPs sequentially. For 100 rows you won't notice. For 50,000 you will. The fix is either to move the join into Power Query, or to refactor so the lookup happens once at the start of the formula and the LAMBDA just consumes the pre-resolved values.
Errors inside the LAMBDA pollute the entire spill.
If one row generates a #VALUE!, the spilled result can come back as a single error rather than the array you expected. The fix is to wrap the LAMBDA body in IFERROR for any row-level error you can anticipate (IFERROR(actual/budget, 0) in the example above is exactly this). Don't catch errors at the BYROW level - catch them inside the LAMBDA where they happen.
The payoff
Once BYROW is in your toolkit, the workbook starts to look different. The dragged-formula column that used to occupy your peripheral vision - the one you had to remember to extend, the one that broke when someone deleted row 47 - stops being a feature of the workbook. The calculation is now in one cell, and the cell knows how to spill.
The deeper shift is in how you think about per-row work. Instead of "where do I put the formula and how far do I drag it," the question becomes "what does the LAMBDA need from each row, and what does it return." That framing is closer to how engineers think about iteration, and it's not an accident. BYROW is "for each row, apply a function." That sentence describes a for-loop. Excel finally has one written as a formula instead of a drag.
For the three-minute version of this with a simpler example, the May 16 Click Bait walks the syntax. The May 19 Build It takes BYROW further by combining it with LAMBDA-defined helper functions to build a self-extending summary table without a pivot. The May 20 Hot Take makes the case for why this category of function changes how you should approach Power BI report design too - the same pattern applies in DAX, with different syntax.
Frequently Asked Questions
What's the difference between BYROW and MAP?
MAP applies a LAMBDA to every cell of an array, one cell at a time. BYROW applies a LAMBDA to every row, passing the whole row to the LAMBDA. Use MAP when the per-cell logic doesn't need the rest of the row; use BYROW when the result for a row depends on multiple cells in that row.
Can the LAMBDA inside BYROW use more than just INDEX?
Yes. The argument the LAMBDA receives is a 1D array, and any function that accepts an array works on it - SUM, AVERAGE, MAX, COUNTA, FILTER, even another LAMBDA. INDEX is just the most common because pulling individual columns out by position is the most common need.
Why does my BYROW return a `#CALC!` error?
Two common causes. First, the LAMBDA is returning an array instead of a single value for some rows - BYROW expects one scalar back per row. Wrap the body in something that collapses to a single value if you're doing array operations inside. Second, the input range includes the cell that BYROW is being written into, creating a circular reference. Move the formula or restrict the input range.
Can I pass a named LAMBDA into BYROW?
Yes. If you've defined HEALTH_LABEL in the Name Manager as a LAMBDA that takes a row, you can write =BYROW(Projects, HEALTH_LABEL). This is where BYROW becomes most powerful - the per-row logic lives in a named function used across the workbook, and BYROW is just the iterator.
Does BYROW work on filtered tables?
BYROW operates on the underlying range, not the visible-after-filter rows. If you point it at a structured table column, it processes every row of that column regardless of filter state. To restrict to filtered rows, use FILTER first and pass the filtered result into BYROW.
What's the difference between BYROW and SUMPRODUCT for per-row work?
SUMPRODUCT can simulate per-row calculations through array math, but the readability falls off a cliff once the logic involves conditional branching. BYROW handles arbitrary logic inside the LAMBDA - including IF, LET, named function calls, and lookups - in a form that reads like normal code. For straight numeric multiplication across rows, SUMPRODUCT is still fine. For anything with branching, BYROW wins.
