Excel PIVOTBY Function — Build a Pivot Table With a Formula

TL;DR — PIVOTBY(row_fields, col_fields, values, function) is GROUPBY with a second dimension. Give it a field for the rows, a field for the columns, the values to aggregate, and the aggregation — =PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM) — and it spills a full cross-tab: regions down the side, months across the top, totals in the grid, and grand totals on both edges. It's a pivot table as a formula: it recalculates live with no Refresh, and other formulas can point at it. Pass the function by name (SUM, not SUM()). Needs Excel 365 (rolled out 2024–2025).

=PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM)        ' Region (rows) x Month (cols), summed
=PIVOTBY(C2:C1000, B2:B1000, D2:D1000, AVERAGE)    ' same grid, averaged
=PIVOTBY(C2:C1000, B2:B1000, D2:D1000, COUNT)      ' how many orders in each cell
=PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM, , 1, , 1)   ' grand totals on both axes

Everyone knows the pivot table: the drag-and-drop object you insert, configure in a side panel, and refresh whenever the data moves. PIVOTBY gives you the same rows-by-columns grid — but as one formula that lives in the sheet and updates itself. If GROUPBY answers "total by one thing," PIVOTBY answers "total by one thing across another": sales by region and month, headcount by department and level, the classic matrix report.

What you'll learn

The mental model: PIVOTBY = GROUPBY plus a column dimension
The four required arguments, and why rows and columns aren't interchangeable
Adding totals on both axes with the two total_depth arguments
Where PIVOTBY beats a PivotTable — and where a PivotTable still wins
The traps: field order, SUM() vs SUM, and #SPILL!

The mental model: GROUPBY with a second dimension

GROUPBY collapses data down one axis — a list of groups with a number beside each. PIVOTBY adds a second axis, turning that list into a matrix. The same aggregation runs, but now it's split two ways at once:

=GROUPBY(C2:C1000, D2:D1000, SUM)                 ' Region -> one column of totals
=PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM)       ' Region x Month -> a grid of totals
'         └ rows      └ columns  └ values  └ how

That extra argument — col_fields — is the entire difference. Everything you know from GROUPBY (pass the function by name, it spills, it recalculates live, it sorts and totals via arguments) carries straight over. PIVOTBY is not a new idea to learn; it's GROUPBY with one more field to say what runs across the top.

The four required arguments

=PIVOTBY( row_fields , col_fields , values , function )
'          down the     across the   what to    how to
'          side         top          aggregate  aggregate

row_fields — what runs down the left edge (Region).
col_fields — what runs across the top (Month).
values — the numbers filling the grid (Sales).
function — the aggregation, by name (SUM).

The result is a labeled matrix with both axes de-duplicated and sorted, and grand totals by default. Four arguments, and you've replaced the entire Insert PivotTable → drag → configure flow.

Rows and columns are not interchangeable

The most common PIVOTBY mistake is swapping the first two arguments and getting a grid that's technically correct but unreadable — 40 months down the side and 4 regions across, or worse, the same field in both.

=PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM)   ' 4 regions down, 12 months across — readable
=PIVOTBY(B2:B1000, C2:C1000, D2:D1000, SUM)   ' 12 months down, 4 regions across — transposed

The rule of thumb that keeps reports legible: the field with fewer distinct values goes in col_fields (across the top), and the one with more goes in row_fields (down the side), because a page scrolls down comfortably but only shows a few columns across. Regions (few) across, dates or customers (many) down. Get the two backwards and the grid still computes — it just spills the wrong way and runs off the screen.

Totals on both axes

A pivot table gives you row totals and column totals; so does PIVOTBY, through two separate total_depth arguments — one for rows, one for columns:

=PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM, , 1, , 1)   ' grand total row AND grand total column
=PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM, , 0, , 0)   ' no totals on either axis
=PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM, , 1, , 0)   ' row total only (a total column, no total row)

The argument order is field_headers, then row_total_depth, then row_sort_order, then col_total_depth — so the two totals you care about sit at positions 6 and 8, which is why you see the skip commas. 1 means "show the grand total," 0 means "hide it." Being able to total one axis but not the other is something a real PivotTable makes fiddly and PIVOTBY makes a single digit.

Where PIVOTBY wins — and where the PivotTable still does

This is a genuine trade-off, not a replacement, and choosing wrong wastes time either way.

PIVOTBY wins when the report must stay live and connected. It recalculates the instant data changes — no Refresh, no stale cache — so it's the right tool for a dashboard that updates as numbers land. Because it's a formula, other formulas can reference its cells, and it lives in version control and diffs like any text. It has no setup panel to misconfigure.

A classic PivotTable still wins when you want to explore. Drag fields between rows and columns on the fly, drop in slicers and timelines, double-click a cell to drill through to the source rows, group dates into quarters with a right-click, or pivot tens of millions of rows from the Data Model — none of that is what PIVOTBY is for. PIVOTBY renders one report you've already decided on; a PivotTable is an interactive tool for deciding what the report should be.

The honest line: PIVOTBY for reporting, PivotTables for exploring. If the layout is settled and needs to stay current, use the formula. If you're still poking at the data to see what matters, use the object.

The traps in one place

Three failures cover almost every PIVOTBY that "doesn't work":

=PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM())   ' WRONG — pass SUM, not SUM()
=PIVOTBY(C2:C1000, C2:C1000, D2:D1000, SUM)      ' WRONG — same field in rows AND cols = a diagonal mess
=PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM)      ' #SPILL! if any cell the grid needs is occupied

First, the function is a bare name — the same SUM not SUM() rule as GROUPBY. Second, row_fields and col_fields must be different fields; passing the same one gives a meaningless diagonal. Third, PIVOTBY spills a whole rectangle, so if anything sits where the grid needs to land you get #SPILL! — clear the space below and to the right, and remember the grid grows as new months or regions appear in the data.

The judgment call

Reach for PIVOTBY the moment you need a cross-tab that stays current and feeds the rest of the sheet — a live matrix on a dashboard, a grid whose cells other formulas read. Keep the PivotTable for hands-on exploration: slicers, drill-through, drag-and-drop, huge Data Model sources. The mechanical rules that prevent the usual failures: fewer categories go across (col_fields) and more go down (row_fields); the function is a bare name; and the two total_depth arguments (rows at position 6, columns at position 8) turn grand totals on and off per axis. Learn the argument grammar once — it's just GROUPBY with a column field — and the matrix report stops being an object you rebuild and becomes a formula that keeps itself honest.

How ExcelMaster helps

PIVOTBY's power sits behind a wall of positional, skip-comma arguments — the totals you want are at positions 6 and 8, and it's easy to set the wrong one. Tell ExcelMaster "sales by region across months, with totals on both sides" and it writes =PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM, , 1, , 1) with every comma placed. Ask it to "swap the rows and columns" or "count orders instead of summing sales" and it edits the right argument. And if your grid throws #SPILL! or comes out transposed, it explains which argument to change — no guessing which of eleven positions you missed.

Frequently asked questions

What does PIVOTBY do in Excel?

PIVOTBY(row_fields, col_fields, values, function) builds a cross-tabulation: it groups data down the rows by one field and across the columns by another, aggregates the values in each cell with function, and spills a labeled grid with grand totals. It's a pivot table expressed as a single formula, and it recalculates automatically when the data changes.

How is PIVOTBY different from a normal PivotTable?

PIVOTBY is a live formula — it updates instantly with no Refresh, its cells can be referenced by other formulas, and there's no setup panel. A classic PivotTable is an interactive object: you can drag fields, add slicers, drill through to source rows, and pivot Data Model sources of tens of millions of rows. Use PIVOTBY for reporting that must stay current; use a PivotTable for exploring.

Why does PIVOTBY return an error or a weird diagonal?

Two usual causes. If it errors like #CALC!, you probably wrote SUM() instead of SUM — the function argument is a bare name. If the result looks like a meaningless diagonal, you passed the same field to both row_fields and col_fields; they must be different fields. A #SPILL! means something is blocking the grid — clear the cells below and to the right.

How do I add row and column totals in PIVOTBY?

Use the two total-depth arguments: =PIVOTBY(C2:C1000, B2:B1000, D2:D1000, SUM, , 1, , 1). The first 1 (position 6) adds a grand-total column for the rows; the second 1 (position 8) adds a grand-total row for the columns. Set either to 0 to hide that total. The empty commas skip field_headers and the two sort arguments.

Which Excel versions have PIVOTBY?

PIVOTBY is a Microsoft 365 function released alongside GROUPBY through 2024–2025 to the Current Channel. It is not in Excel 2021, 2019, or 2016. If =PIVOTBY(...) returns #NAME?, your build doesn't include it yet; a classic PivotTable is the fallback.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-18.