The classic "drag this formula down the column" workflow has a problem nobody talks about: it never extends itself. Add a new row of source data and the calculated column ends one row short of where it should. BYROW fixes this in one formula in one cell.
The win
You have a budget table in A2:F100. For each row, you want a single output - say, the variance between budget and actuals as a percentage. The old way is to write a formula in G2 and drag it down. Add new rows next month and you drag again, or you forget and the last few rows show blank.
BYROW hands an entire row to a function and returns one value per row, as a spilled array:
=BYROW(A2:F100, LAMBDA(r, INDEX(r,4)/INDEX(r,3) - 1))One cell. One formula. The result spills down the column automatically, and if you point BYROW at a structured table column-range, it extends itself when rows are added.
How to do it
- Pick a cell where the spilled result should start - somewhere with empty space below it for the spill.
- Type
=BYROW( - Pass the range of rows. For a table this should be
Table1[[Column1]:[ColumnN]]so it auto-extends. - Type
,LAMBDA(r,-ris the variable name for the current row. You can call it anything;ris short and conventional. - Inside the LAMBDA body, use
INDEX(r, column_number)to pick out specific values from the row. Then write whatever expression returns your single output. - Close with
))to close the LAMBDA and the BYROW.
A full example with a structured table named Budget:
=BYROW(Budget[[Budget]:[Actual]], LAMBDA(r, INDEX(r,2)/INDEX(r,1) - 1))This gives you the actual-over-budget variance for every row in the table, in one cell, self-extending.
Why it works
BYROW iterates over the rows of an array and calls your LAMBDA once per row, passing that row as a one-row array. The LAMBDA returns one value. BYROW collects those values into a vertical array and spills them. The mechanic is the same as a worksheet-level for each row loop - just expressed as a function instead of a drag.
The reason this matters more than "saves dragging": the formula is a single source of truth. There is one expression. If the logic changes, you change it once, in one cell. The drag-this-down pattern produces N copies of the same formula scattered down a column, and the next person to maintain the workbook has to verify all N of them still match.
When not to use it
If you need the result to behave like a column inside a structured table - sorting moves it, filtering hides it - BYROW is the wrong tool. The spilled output is a separate range that happens to sit next to the table. It doesn't participate in table sorts or filters.
For that, you want a real calculated column added through the table itself, or a Power Query custom column if the source is loaded through a query.
For the full BYROW (and BYCOL) mental model - including how to combine it with named LAMBDAs and where it fits with SCAN and MAP - see the May 18 Magic Monday.