Excel PIVOTBY function: dynamic pivot in one formula

You have a table of sales, and someone wants totals by region and month. A PivotTable takes ninety seconds to build, another ten to click Refresh whenever the data moves, and lives in a place the workbook’s other formulas can’t easily read. PIVOTBY does the same job in a single formula. It spills a matrix — row groups down, column groups across, aggregated values in the middle — and it recalculates the instant the source changes. If you already spend the day inside dynamic arrays, PIVOTBY is where the pivot finally joins them.

Where PivotTable friction actually shows up

PivotTables are excellent for exploration: you drag a field, the layout snaps, you drag another, it snaps again. That interactive muscle is also where the friction lives. A report bolted to a PivotTable object needs a Refresh every time the source table grows, and downstream formulas that read from the pivot break the moment a group appears, disappears, or reorders. If the workbook is emailed around, half the recipients forget to refresh at all.

PIVOTBY inverts the trade-off. You lose the drag-and-drop panel, and you gain a spilled range that other formulas can index into by row and column position, that refreshes on every recalc, and that lives beside your other formulas instead of in a separate object model.

What PIVOTBY does in one formula

The full signature has eleven arguments, but only the first four are required and they carry the shape of every report you’ll ever build with the function.

=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])

row_fields is the column that becomes your row groups. col_fields is the column that becomes your column groups. values is the column being aggregated. function is the aggregator — pass a bare function name like SUM, AVERAGE, or COUNT, no parentheses, because PIVOTBY calls it as a lambda internally. That last point is the one people trip on first: you pass the function, not a call.

The other seven arguments are all optional. They control header rendering, subtotal depth on either axis, sort order on either axis, an inline filter mask, and whether percentages are computed against the row, the column, or the grand total. You reach for them one at a time as the report grows.

The three arguments you use every time

Imagine a plain sales table on Sheet1 — Region in column A, Product in column B, Quarter in column C, Revenue in column D, headers in row 1, data starting on row 2. A basic cross-tab of revenue by region and quarter is four arguments long.

Drop this formula in F1 and the entire cross-tab spills below it, headers included:

=PIVOTBY(Sheet1!A2:A200, Sheet1!C2:C200, Sheet1!D2:D200, SUM)

Each range is a single column, all three the same length. Mismatched lengths return #VALUE!. The result grows or shrinks as new rows land in the source, and the columns rearrange themselves alphabetically unless you tell them otherwise. That behavior is not automatic magic — it’s the sort arguments doing their default job, which is the next section.

Subtotals and sort order without changing the layout

Two arguments do most of the polish work: row_total_depth and the matching col_total_depth. Pass 1 for a single grand total, 2 for grand plus subtotals when the row axis has two columns, or 0 to suppress the total entirely. The row_sort_order and col_sort_order take a signed integer: positive sorts ascending by that value column, negative sorts descending. Put them together and the report reorders itself the way an analyst would rearrange it by hand.

=PIVOTBY(A2:A200, C2:C200, D2:D200, SUM)

=PIVOTBY(A2:A200, C2:C200, D2:D200, SUM, 3, 1, -2, 1, 1)

The 3 in position five turns on both row headers and the header row on top. Setting row_sort_order to -2 sorts regions by column 2 of the spilled result (revenue) largest to smallest; the columns stay in the natural quarter order because their sort value is a plain 1. This is where PIVOTBY starts to feel less like a formula and more like a small report DSL.

Custom aggregation with a LAMBDA

The function argument accepts any lambda, which means the aggregator does not have to be one of the built-ins. That is how you get percent-of-parent, weighted averages, or medians that ignore blanks — patterns a stock PivotTable needs a calculated field for. If you already write reusable lambdas with LAMBDA and named functions, this is the same muscle.

=PIVOTBY(A2:A200, C2:C200, D2:D200, LAMBDA(v, SUM(v)/SUM(D2:D200)))

That aggregator receives a slice of the values column for each cell of the spilled grid and returns its share of the grand total. Format the spill range as a percentage and you have a share-of-total heat map that updates live. Swap the lambda for a weighted average and the same layout answers a different question:

=PIVOTBY(A2:A200, C2:C200, D2:D200, LAMBDA(rev, SUMPRODUCT(rev, INDEX(E2:E200, 0)) / SUM(rev)))

The relative_to argument (position eleven) gives you the common shares — of grand total, of row, of column — without hand-rolling a lambda. Reach for the lambda when the metric is not one of those three shapes.

When PIVOTBY beats a PivotTable, and when it doesn’t

PIVOTBY is not a full replacement. It gives up the slicer panel, the drag-and-drop pivot cache, drill-through on double-click, and the automatic formatting styles. It wins on live updates, on being readable to downstream formulas, and on carrying its logic in cell text you can review and version-control alongside the rest of the workbook. The choice is task-shaped, not tribal, and it lines up neatly against the older PivotTable workflow and the newer GROUPBY function.

Troubleshooting the four errors you’ll see

PIVOTBY has a small set of failure modes and each maps cleanly to one cause. Working through them in order clears every real problem I’ve seen in the wild.

• ✓ #NAME? — the workbook is Excel 2021 or older; PIVOTBY does not exist there.
• ✓ #VALUE! — the three input ranges are not the same length, or one of them is a row instead of a column.
• ✓ #SPILL! — a cell inside the spill zone is not empty. Clear the block below and to the right of the formula.
• ✓ #CALC! — the function argument got called with parentheses. Pass SUM, not SUM().

A fifth issue, less an error than a surprise: subtotal arguments do nothing unless the corresponding row_fields or col_fields spans two columns. If you passed a single column for rows and set row_total_depth to 2, PIVOTBY silently falls back to depth 1.

Where to start

Open a workbook you already have a PivotTable in. Copy the source range references, drop them into the first three arguments of a fresh PIVOTBY, pass SUM as the fourth, and check that the numbers agree. Once they do, add the sort arguments and delete the PivotTable object. Most reports migrate in under a minute; the ones that don’t are the ones that need a slicer, and now you know to keep the PivotTable for those.