This is not a new-versus-old fight. GROUPBY and PivotTables solve overlapping problems, but they shine in different workflows. The right choice depends less on ideology and more on how you build reports, who maintains the workbook, and how much formula control you want.
Complete Excel Guide with AI Integration
Master formulas, pivot tables, data analysis, and charts — with AI integration.
Learn moreIf you only remember one thing from this guide, remember this: use PivotTables for fast exploration, and lean towards GROUPBY when the summary needs to live inside a formula-driven model.
Quick answer
PivotTables are usually better for ad hoc analysis and drag-and-drop exploration. GROUPBY is usually better when the result must spill into a report, combine with other formulas, and remain visible as logic in the workbook.
- Use PivotTables for quick exploration and broad team familiarity.
- Use GROUPBY for dynamic, formula-led report tabs.
- Use both when exploration happens first and a cleaner formula report comes later.
The biggest practical difference
A PivotTable is an object with fields and layout controls. GROUPBY is a formula result. That changes how you maintain the workbook. Formula models are easier to inspect in-line, while PivotTables are easier for many casual Excel users to build from scratch.
When GROUPBY wins
GROUPBY wins when the summary has to sit in a precise place, feed another spill formula, or power a dashboard without extra manual positioning. It is also easier to version conceptually because the rule is visible in the formula bar.
When PivotTables still win
PivotTables still dominate when the question is open-ended. If you want to drag fields around, regroup dimensions quickly, or hand the workbook to someone who knows classic Excel but not modern array formulas, PivotTables remain the safer choice.
Worked example: two stages of the same report
An analyst receives a messy sales export and first uses a PivotTable to explore regional variance, seasonality, and product mix. Once the report question stabilises, they rebuild the final summary with GROUPBY so the output sits neatly inside a template sheet and updates automatically next month.
Common mistakes
- Choosing one tool as a matter of identity rather than workflow.
- Expecting GROUPBY to replace drag-and-drop exploration.
- Keeping a fragile manual report when a formula summary would be easier to maintain.
When to use something else
If you already know you want formula-led summaries, read the GROUPBY guide. If you need cross-tab output rather than one grouped list, go to PIVOTBY.
Frequently asked questions
GROUPBY or PivotTable — which should I use?
PivotTable for ad-hoc exploration and drag-and-drop; GROUPBY when the result must spill into a report, combine with other formulas, and stay visible as logic. Known report question points to GROUPBY; open question points to a PivotTable.
What is the core difference?
A PivotTable is an object with layout controls; GROUPBY is a formula result. That changes maintenance: formulas are inspectable in the formula bar, while PivotTables are easier for casual users to build by dragging.
When does GROUPBY clearly win?
When the summary must land in a precise place, feed a spill formula or chart, refresh automatically with the data, and be reviewable as a visible rule rather than a hidden pivot cache.
When do PivotTables still win?
Open-ended analysis: dragging fields around, quick regrouping, drill-down, and handing the file to someone who knows classic Excel but not dynamic arrays.
Does GROUPBY refresh automatically?
Yes. Being a formula, it recalculates with the data. A PivotTable needs a manual or scheduled refresh, which is a common source of stale numbers.
Can I use both in one workbook?
Absolutely. Many models use PivotTables to explore and GROUPBY for the fixed reporting tabs that feed dashboards. Pick per task rather than per preference.
Related tutorials 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 — the hub page for all Excel tutorials.
- GROUPBY Function in Excel: Formula-Based Summaries Without a Pivot Table
- PIVOTBY Function in Excel: Build Pivot-Style Reports With a Formula
- Excel Tables Best Practices: Structured References, Growth, and Cleaner Models
- How to Fix #SPILL! Errors in Excel and Prevent Them in Dynamic Array Models
- How to Create a Pivot Table in Excel Step by Step
Official references
These official references are useful if you need the product or framework documentation alongside this guide.