PIVOTBY Function in Excel: Build Pivot-Style Reports With a Formula

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.

That 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.

How to make this pattern hold up in a real workbook

PIVOTBY Function in Excel: Build Pivot-Style Reports With a Formula becomes much more useful once it is tied to the rest of the workflow around it. In real work, the result depends on table structure, formula clarity, edge cases, and what the workbook has to support next, not only on following one local tip correctly.

That is why the biggest win rarely comes from one clever move in isolation. It comes from making the surrounding process easier to review, easier to repeat, and easier to hand over when another person inherits the workbook or codebase later.

• Check the data shape first, because most workbook pain starts upstream of the formula or feature.
• Prefer patterns that another analyst can still read and support later.
• Test the technique on one real edge case before you spread it across the model.

How to extend the workflow after this guide

Once the core technique works, the next leverage usually comes from standardising it. That might mean naming inputs more clearly, keeping one review checklist, or pairing this page with neighbouring guides so the process becomes repeatable rather than person-dependent.

The follow-on guides below are the most natural next steps from PIVOTBY Function in Excel: Build Pivot-Style Reports With a Formula. They help move the reader from one useful page into a stronger connected system.

• Go next to GROUPBY Function in Excel: Formula-Based Summaries Without a Pivot Table if you want to deepen the surrounding workflow instead of treating PIVOTBY Function in Excel: Build Pivot-Style Reports With a Formula as an isolated trick.
• Go next to GROUPBY vs PivotTable in Excel: When Formula Summaries Beat the Ribbon if you want to deepen the surrounding workflow instead of treating PIVOTBY Function in Excel: Build Pivot-Style Reports With a Formula as an isolated trick.
• Go next to Excel Tables Best Practices: Structured References, Growth, and Cleaner Models if you want to deepen the surrounding workflow instead of treating PIVOTBY Function in Excel: Build Pivot-Style Reports With a Formula as an isolated trick.

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.

• GROUPBY Function in Excel: Formula-Based Summaries Without a Pivot Table
• GROUPBY vs PivotTable in Excel: When Formula Summaries Beat the Ribbon
• Excel Tables Best Practices: Structured References, Growth, and Cleaner Models
• How to Create a Pivot Table in Excel Step by Step