---
title: "3-Minute Win: Use BYROW to Apply a Formula to Every Row Without Dragging It Down"
date: 2026-05-16
updated: 2026-06-27T11:13:15Z
tags: ["DAX", "Excel", "Dynamic Arrays", "LAMBDA"]
canonical: https://bianca.codes/blog/3-minute-win-use-byrow-to-apply-a-formula-to-every-row-without-dragging-it-down/
---

# 3-Minute Win: Use BYROW to Apply a Formula to Every Row Without Dragging It Down

_BYROW hands your function an entire row at a time — which means 'apply this logic to every row in this range' is one formula in one cell. No dragging, no CTRL+SHIFT+ENTER, no missing the last row w…_

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:

```excel
=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

1. Pick a cell where the spilled result should start - somewhere with empty space below it for the spill.
2. Type `=BYROW(`
3. Pass the range of rows. For a table this should be `Table1[[Column1]:[ColumnN]]` so it auto-extends.
4. Type `,LAMBDA(r,` - `r` is the variable name for the current row. You can call it anything; `r` is short and conventional.
5. 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.
6. Close with `))` to close the LAMBDA and the BYROW.

A full example with a structured table named `Budget`:

```excel
=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_](/blog/byrow-and-bycol-apply-any-formula-to-every-row-or-column-automatically/)_._