Microsoft’s latest Excel dynamic array functions, GROUPBY and PIVOTBY, have taken spreadsheet users by storm. These formula-based tools let you build summary tables that refresh automatically as source data updates, no manual refresh button required. They integrate cleanly with other formulas and spill results directly onto your worksheet. But despite all the hype, these fan-favorite new functions still cannot replicate everything a classic pivot table does. In this post, we break down their strengths, critical gaps, and when you still need to reach for a pivot table.
What makes GROUPBY and PIVOTBY so popular
For years, pivot tables were the go-to tool to group, summarize and cross-tabulate data. But pivot tables have one big annoyance: they do not refresh automatically. Every time your raw data changes, you have to right-click and hit Refresh. If you forget, your report shows outdated numbers.
GROUPBY and PIVOTBY solve that problem.
- GROUPBY: Groups rows by one or more categories and calculates aggregations like SUM, COUNT or AVERAGE.
- PIVOTBY: Creates two-dimensional cross-tab reports, with categories on rows and columns, just like a pivot table.
Since they are regular worksheet formulas, their output updates instantly when source data changes. You can nest them inside other functions, combine outputs with FILTER, SORT or HSTACK, and build automated dashboards without manual interaction. This is why data analysts and spreadsheet fans love them.
Where the new Excel functions fall short
While they mimic the math of pivot tables, they lack the interactive reporting infrastructure built into pivot tables. These are the key features formula functions cannot replicate:
1. Slicers and timelines for clickable filtering
Pivot tables natively work with slicers and timelines. You click a button, and your report instantly filters by region, product, date range, or category. This is perfect for dashboards and shared reports where non-technical users want to explore data without editing formulas. GROUPBY and PIVOTBY have no native slicer support. You can hack together filters, but it is clunky and not as clean as a pivot table slicer.
2. Drill-down with double-click
One of the most powerful pivot table tricks: double-click a summary cell, and Excel creates a new sheet showing all underlying raw records that make up that total. This is indispensable for auditing, investigating outliers and troubleshooting bad numbers. GROUPBY/PIVOTBY spill out static results; there is no built-in way to double-click and reveal source rows.
3. Flexible calculated fields and calculated items
Pivot tables let you add calculated fields directly inside the pivot object. You can create custom metrics that only exist within your summary report, without touching the source table. While GROUPBY/PIVOTBY support basic aggregations, complex calculated metrics are much harder to implement inside the formula itself.
4. Built-in report formatting and layout controls
Pivot tables remember their styling: banded rows, subtotals, grand totals, compact, outline or tabular layout. You can drag and drop fields to rearrange your report visually. Spilled array results from GROUPBY/PIVOTBY are just cells. They do not retain report formatting automatically when the output expands or shrinks as data changes. Conditional formatting can help, but it is not the same as pivot table native layout controls.
5. Rapid exploratory data analysis
Pivot tables are designed for fast experimentation. Drag fields between rows, columns, filters and values areas to test different views in seconds. You can quickly switch between SUM, COUNT, MIN, MAX, % of grand total, running totals and rank calculations with a few clicks. To make those same changes with GROUPBY/PIVOTBY, you need to edit your formula. That adds friction when you are exploring data and testing hypotheses.
6. Native date grouping
Pivot tables automatically group dates into years, quarters, months, weeks, or days with one click. With GROUPBY/PIVOTBY, you must manually wrap date columns in YEAR(), MONTH() or similar functions to achieve grouping.
When you should use GROUPBY / PIVOTBY
Pick these new formula functions when:
- Your source data updates often, and you want automatic recalculation
- You need to feed summary results into other formulas
- You are building automated reports and want to avoid manual refresh steps
- You prefer formula transparency and want to document calculations directly in cells
When pivot tables are still the better choice
Stick with pivot tables for these common scenarios:
- You need slicers, timelines or interactive dashboards for other users
- You want to double-click cells to drill into source data for audits
- You are doing exploratory analysis and want to drag-and-drop fields quickly
- You need custom calculated fields, percentage-of-total metrics or flexible subtotal controls
- You want polished, persistent report formatting that adjusts as your report grows and shrinks
- Your workbook will be opened by users on older Excel versions that do not support dynamic array functions
Side-by-side quick comparison
| Feature | GROUPBY / PIVOTBY | Pivot Table |
|---|---|---|
| Auto-update on data change | ✅ Yes | ❌ Requires manual refresh |
| Nestable inside other formulas | ✅ Yes | ❌ No |
| Slicers & timelines | ❌ Native support missing | ✅ Built-in |
| Double-click drill down | ❌ Not available | ✅ Built-in |
| Drag-and-drop field layout | ❌ Must edit formula | ✅ Very fast |
| Automatic date grouping | ❌ Manual formula required | ✅ One-click |
| Persistent report formatting | ❌ Needs conditional formatting | ✅ Native |
Final thoughts
GROUPBY and PIVOTBY are game-changing additions to Excel. They solve one of the biggest frustrations of pivot tables: manual refreshing. For formula-driven, self-updating summaries, they are excellent.
But they are not a full pivot table replacement. Pivot tables remain unmatched for interactive exploration, slicer dashboards, drill-down auditing, and rapid visual report building.
The smart workflow is not to pick one permanently. Use GROUPBY/PIVOTBY for automated formula summaries, and keep pivot tables ready for interactive dashboards and exploratory analysis.
