If GROUPBY feels like the formula replacement for one-dimensional summaries, PIVOTBY is the natural next step for report-style summaries that resemble a PivotTable. It gives you the logic of a cross-tab without forcing you into a separate reporting object.
Complete Excel Guide with AI Integration
Master formulas, pivot tables, data analysis, and charts — with AI integration.
Learn moreThat matters when you want a formula-driven report tab that updates cleanly and sits comfortably beside the rest of your workbook logic.
Quick answer
Use PIVOTBY when you want row and column groupings in a dynamic formula output. It is especially useful for compact reporting grids, spill-friendly dashboards, and workbooks where formula transparency matters more than drag-and-drop exploration.
- You need both row and column groupings in one live formula result.
- You want the output to sit exactly where your report template expects it.
- You are already comfortable with modern Excel array functions.
Why PIVOTBY is different from a PivotTable
A PivotTable is brilliant for exploration. PIVOTBY is better when you already know the reporting question and want the result to behave like part of the model.
You can place it, reference it, and combine it with other formulas more naturally.
Think in report questions first
The simplest mental model is this: what should appear down the left, what should appear across the top, and what number should sit in the middle of each intersection? Once those three things are clear, the formula becomes much easier to reason about.
- Rows: the categories you want to compare.
- Columns: the second dimension you want to break them by.
- Values: the measure you want to aggregate.
Where it is especially useful
PIVOTBY is ideal for compact reporting tabs: sales by region and month, spend by department and quarter, tickets by priority and team, or stock by warehouse and product family.
Because the output is a formula result, it is easier to control alongside other spill ranges than a PivotTable would be.
Worked example: sales by region and month
A business development lead wants one sheet that shows monthly revenue across the top and region down the side. The data lives in a single sales table that grows every week.
PIVOTBY lets them create the report grid with one formula, keep the output next to commentary and targets, and link a chart to the result without maintaining a separate PivotTable.
Common mistakes
- Using untidy source data with inconsistent category values.
- Building a huge report grid before testing one small version first.
- Assuming it must replace PivotTables rather than complement them.
When to use something else
Use a PivotTable when exploration is the main goal and the audience is more comfortable with field lists than formulas. Use GROUPBY when you only need one grouping dimension rather than a cross-tab.
Frequently asked questions
What does PIVOTBY do?
It builds a pivot-style report — row groups, column groups, and an aggregated value at each intersection — as one dynamic-array formula that spills, so the result lives in the model rather than as a separate object.
How is PIVOTBY different from a PivotTable?
A PivotTable is a drag-and-drop object great for exploration; PIVOTBY is a formula for when you already know the report question and want the output to behave like part of the workbook, spilling, feeding charts, and combining with other formulas.
How do I think about its arguments?
In three questions: what goes down the left (row fields), what goes across the top (column fields), and what number sits at each intersection (the values plus their aggregation). Get those clear and the formula almost writes itself.
What is PIVOTBY best for?
Compact reporting tabs with a known shape: sales by region and month, spend by department and quarter, tickets by priority and team. Fixed cross-tabs that should refresh with the data.
Can I change the aggregation (sum, average, count)?
Yes. PIVOTBY takes the aggregation as an argument, so you can sum, average, count, or pass a custom LAMBDA for the value at each intersection.
When should I still use a PivotTable instead?
When the question is open-ended and you want to drag fields around, or when you are handing the workbook to someone comfortable with classic Excel but not dynamic arrays.
Related guides on this site
If you want to keep going without opening dead ends, these are the most useful next reads from this site.