Build a Dynamic Summary Table Using BYROW and LAMBDA — No Pivot Table Required

What you're building: a self-extending summary table that calculates a custom metric for every row in a source dataset, without a pivot, without helper columns, and without dragging a formula. The output lives in a single cell and spills the entire result. Add new rows to the source; the summary extends to match.

What you need to start: Excel for Microsoft 365 or Excel 2024. BYROW and LAMBDA are not available in earlier versions. The source data should be loaded into a structured table - if it isn't, select your data and press Ctrl + T first, then rename the table in the Table Design tab.

Why this approach instead of a pivot table

Pivot tables are excellent at one thing: aggregating data along grouping dimensions. Total sales by region, count of orders by month, average ticket size by product. When the question is "group by X and aggregate Y," pivots are the right tool and BYROW is not.

This Build It is for the other shape of summary: one calculated value per row of the source. Per-customer health scores. Per-project risk labels. Per-transaction fraud flags. Per-employee compensation band. The output has the same row count as the source - one calculated label per existing row - which is not what a pivot does.

The legacy pattern for this is a helper column with a long nested IF, dragged down the entire source column. The problems are familiar: the formula has to be re-dragged when source rows are added, the logic is duplicated N times instead of defined once, and any change to the logic has to propagate across every copy. BYROW plus LAMBDA collapses all of this into a single cell with a single definition.

Step 1 - Set up the source table

Start with a flat table of project data. The columns: Project, Owner, BudgetHrs, ActualHrs, Status, DueDate. Sample rows: Atlas / A. Lee / 40 / 35 / Active / 2026-06-15; Beacon / M. Tan / 80 / 92 / Active / 2026-05-30; Cinder / A. Lee / 20 / 22 / Done / 2026-05-10; Drift / R. Khan / 60 / 55 / Paused / 2026-07-01.

Press Ctrl + T to convert this to a structured table. In the Table Design tab on the ribbon, rename it to Projects. The rename matters - it's how the rest of the formulas reference the data.

Step 2 - Decide what the summary returns per row

The summary will produce one row per project, with three calculated outputs:

1. Health label - "Done," "Critical" if actuals are over 110% of budget, "Watch" if between 90% and 110%, "Risk" if past due and not done, "On track" otherwise.
2. Days remaining - days until the due date if active, blank if done.
3. Utilisation - actuals over budget as a percentage, formatted as a number.

These are three different LAMBDAs. We'll define each one separately, then call them from a single BYROW that pulls them together into the summary array.

Step 3 - Define the per-row LAMBDAs

Each LAMBDA accepts a row from the source table and returns one value. Build them in a cell first to test, then move to Name Manager.

The health label:

=LAMBDA(r,
    LET(
        budget,  INDEX(r, 1),
        actual,  INDEX(r, 2),
        status,  INDEX(r, 3),
        due,     INDEX(r, 4),
        ratio,   IFERROR(actual / budget, 0),
        IF(status = "Done", "Done",
            IF(due < TODAY(), "Risk",
                IF(ratio > 1.1, "Critical",
                    IF(ratio > 0.9, "Watch", "On track"))))
    )
)

Note the LAMBDA expects a row with budget, actual, status, due in that order - that's the order they appear in Projects[[BudgetHrs]:[DueDate]]. The column positions are relative to the slice you pass in, not the original table.

To test this, paste the LAMBDA into a cell and call it with the first row of the relevant columns:

=LAMBDA(r,
    LET(
        budget,  INDEX(r, 1),
        actual,  INDEX(r, 2),
        status,  INDEX(r, 3),
        due,     INDEX(r, 4),
        ratio,   IFERROR(actual / budget, 0),
        IF(status = "Done", "Done",
            IF(due < TODAY(), "Risk",
                IF(ratio > 1.1, "Critical",
                    IF(ratio > 0.9, "Watch", "On track"))))
    )
)(Projects[@[BudgetHrs]:[DueDate]])

The trailing (Projects[@[BudgetHrs]:[DueDate]]) invokes the LAMBDA on the current row. If this returns the right label for that row, the logic is working.

Once it works, open Name Manager (Ctrl + F3), New, name it PROJECT_HEALTH, paste the LAMBDA part (everything except the trailing call site) into the Refers to field. The function is now workbook-global.

Repeat the pattern for the other two LAMBDAs:

PROJECT_DAYS_REMAINING =
    LAMBDA(r,
        LET(
            status,  INDEX(r, 3),
            due,     INDEX(r, 4),
            IF(status = "Done", "",
                MAX(0, due - TODAY()))
        )
    )

PROJECT_UTILISATION =
    LAMBDA(r,
        IFERROR(INDEX(r, 2) / INDEX(r, 1), 0)
    )

Each named LAMBDA is now reusable. Calling PROJECT_HEALTH(some_row) from anywhere in the workbook returns the health label.

Step 4 - Build the summary in a single BYROW (or three)

You have two structural choices for the summary output. Either three BYROW calls in three adjacent columns, or one BYROW with a LAMBDA that returns multiple values per row using HSTACK.

The simpler approach is three BYROWs. Pick a starting cell - say H2 - and write:

=BYROW(Projects[[BudgetHrs]:[DueDate]], PROJECT_HEALTH)

The spill covers H2:H for as many rows as Projects has. In I2:

=BYROW(Projects[[BudgetHrs]:[DueDate]], PROJECT_DAYS_REMAINING)

In J2:

=BYROW(Projects[[BudgetHrs]:[DueDate]], PROJECT_UTILISATION)

Each call is one cell. Each one spills down a column. Add a project to Projects and all three spills extend to match.

The single-formula variant uses HSTACK to combine the three outputs into a 2D summary in one cell:

=BYROW(Projects[[BudgetHrs]:[DueDate]], LAMBDA(r,
    HSTACK(
        PROJECT_HEALTH(r),
        PROJECT_DAYS_REMAINING(r),
        PROJECT_UTILISATION(r)
    )
))

The result is a 2D spill - one row per project, three columns. Pick whichever shape works for your layout; both are valid.

Step 5 - Add headers and labels separately

The spilled BYROW output doesn't include headers. Put labels in the row above the spill anchor manually: H1 = "Health", I1 = "Days Remaining", J1 = "Utilisation". These are static labels in normal cells, unrelated to the BYROW formulas.

If you want the summary to live inside its own table, the cleanest pattern is to leave the BYROW output as a free-spilling array and reference it from a real table elsewhere if needed. Don't try to wrap the spilled output in a structured table - the spill won't behave as a table column for sorting and filtering.

Common mistakes

Wrong column positions inside the LAMBDA.

The most frequent issue. INDEX(r, 1) refers to the first column of the slice passed into BYROW, not the first column of the source table. If you pass Projects[[BudgetHrs]:[DueDate]], then INDEX(r, 1) is BudgetHrs. If you pass the whole table Projects[#All], the columns shift. Test the LAMBDA in a cell first to verify which column index returns which value.

Forgetting to point BYROW at the right range shape.

BYROW wants a 2D range. If you pass a single column, it'll iterate row-by-row over that single column, and INDEX(r, 1) is the only valid call inside the LAMBDA. Most useful per-row logic needs multiple columns - so the range passed to BYROW should always span the columns the LAMBDA references.

Building the LAMBDA in Name Manager first.

Don't. The Name Manager has no syntax highlighting, no error messages, no line wrapping. Build the LAMBDA in a cell where Excel formats it and reports issues. Move it to Name Manager only after it works.

Trying to do too much in a single LAMBDA.

If PROJECT_HEALTH ends up with eight nested IFs and three LET intermediates, break it into smaller named LAMBDAs that each handle one concern. PROJECT_HEALTH can call IS_OVER_BUDGET(r) and IS_PAST_DUE(r) instead of computing both inline. The workbook ends up with a small vocabulary of named functions that describe the problem in its own terms.

Expecting the spill to sort with the source table.

It won't. The spilled summary is a separate range that happens to align with the source. Sort Projects and the BYROW output stays in its original order - which means the summary no longer lines up with the visible source rows. If the summary needs to live inside the table, it needs to be a real calculated column added through Table Design, not a BYROW spill.

What you've built

A summary table that requires no maintenance. Adding a project to Projects extends every spill automatically. Changing the health logic is one edit in Name Manager - every cell that uses PROJECT_HEALTH updates immediately. The logic is defined once, called from one cell, and produces a result that covers the entire dataset.

The pattern generalises. Per-row work that used to be a helper-column-plus-drag exercise is now one LAMBDA plus one BYROW. The pivot table is still the right tool for grouped aggregation. BYROW is the right tool for everything else.

Frequently Asked Questions

Why not just use a pivot table for this?

A pivot groups data into aggregates - it doesn't return a value per row of the source. The output we built has one row per project, with a health label calculated from that project's own numbers. Pivots can't do this without going through Power Pivot or DAX measures, which is much more setup for the same result.

Can I use this pattern with a much larger source table?

Yes, but BYROW calls the LAMBDA once per row, so performance scales linearly. For 50,000 rows with a LAMBDA that does an XLOOKUP into another table, the calculation will be noticeable. For very large datasets, consider moving the logic into Power Query, where it executes once during refresh rather than every time the workbook calculates.

What if my health label rules change next quarter?

Edit the PROJECT_HEALTH LAMBDA in Name Manager. Every BYROW call that uses it picks up the new logic on the next calculation. This is the central win over the helper-column-plus-drag pattern: the logic lives in one place.

Can I use BYROW with multiple data tables joined together?

BYROW iterates over rows of a single 2D array. If you need data from a second table for each row, look it up inside the LAMBDA with XLOOKUP. For repeated lookups against a large second table this gets slow; in that case, do the join in Power Query and run BYROW on the joined result.

Why is the spilled output not behaving like a table column?

Because it isn't one. BYROW produces a spilled array that sits adjacent to the table but is not part of it. Excel treats the spill as a separate range. For values that need to behave like table columns - sortable, filterable, drag-selectable as a column - add a real calculated column through Table Design or use Power Query.

Can this pattern handle multiple outputs per row in one cell?

Yes, by returning an array from the LAMBDA. The HSTACK version in Step 4 shows how - the LAMBDA returns three values per row, BYROW collects them into a 2D spill. Useful when you want the entire summary anchored to one cell instead of three.