The PIVOTBY function in Excel builds a pivot-style report with one formula: row groups down the side, column groups across the top and an aggregated value at each intersection. If GROUPBY feels like the formula replacement for one-dimensional summaries, PIVOTBY is the natural next step, giving you the logic of a cross-tab without a separate reporting object.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowThat matters when you want a formula-driven report tab that updates cleanly and sits comfortably beside the rest of your workbook logic.
When should you use PIVOTBY in Excel?
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.
How is PIVOTBY different from a PivotTable?
PIVOTBY is a formula that spills its report into the grid, while a PivotTable is a separate drag-and-drop object.
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.
If you want to learn pivot-style reporting with an instructor, try The Complete Excel Course with AI Integration on Coding Liquids.
How do you think about the PIVOTBY arguments?
Think of the PIVOTBY arguments as three questions: 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.
What is PIVOTBY best used for?
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.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowWorked 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 should you use a PivotTable or GROUPBY instead of PIVOTBY?
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.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowFrequently asked questions
What does PIVOTBY do?
PIVOTBY builds a pivot-style report from one dynamic-array formula. It sets row groups down the side, column groups across the top and an aggregated value at each intersection, then spills the result into the grid, so the report lives in the workbook model rather than as a separate object.
How is PIVOTBY different from a PivotTable?
PIVOTBY is a formula, while a PivotTable is a drag-and-drop object. A PivotTable is great for exploration, but PIVOTBY suits cases where 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?
Answer 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). Once those are clear, the formula almost writes itself, and you can test one small version before building a huge grid.
What is PIVOTBY best for?
PIVOTBY is best for compact reporting tabs with a known shape, such as sales by region and month, spend by department and quarter, or tickets by priority and team. These fixed cross-tabs refresh with the data, which suits a report tab that sits beside the rest of your workbook logic.
Can I change the aggregation (sum, average, count)?
Yes. PIVOTBY takes the aggregation as an argument, so you can sum, average or count the values, or pass a custom LAMBDA to calculate the value at each intersection. Pick the function that matches your report question, such as total revenue or number of tickets, and the whole grid follows it.
When should I still use a PivotTable instead?
Use a PivotTable when the question is open-ended and you want to drag fields around. It is also the safer choice when you are handing the workbook to someone comfortable with classic Excel but not dynamic arrays, because they can work with field lists rather than a formula.
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.
- The Complete Excel Guide 2026: Formulas, AI Copilot, and Modern M365
- GROUPBY Function in Excel: How It Works and When to Use It
- GROUPBY vs PivotTable in Excel: Which Should You Use?
- Excel Tables Best Practices: Structured References, Growth, and Cleaner Models
- How to Create a Pivot Table in Excel: Step-by-Step Tutorial
