Excel GROUPBY and PIVOTBY: Deep Dive into New Aggregation Functions
SheetHub3 min
GROUPBY and PIVOTBY are now generally available in Excel for Microsoft 365. These functions let you create aggregated reports with formulas — no Pivot Table drag-and-drop needed.
This article goes beyond the basics. Here is how they work under the hood, performance considerations, and patterns for real-world use.
GROUPBY creates an in-memory hash table of your data, groups by the specified columns, and applies the aggregation function. Unlike SUMIFS (which scans data once per condition), GROUPBY scans once and groups everything in a single pass.
This makes GROUPBY significantly faster than multiple SUMIFS formulas for the same task.
Multiple aggregation functions:
This returns total and average sales by region — two aggregations, one formula.
Custom aggregation with LAMBDA:
Median sales by region — impossible with Pivot Tables without a workaround.
Sorting by aggregated result:
Sorts by the aggregation column (column 2) in descending order.
PIVOTBY creates cross-tabulations — one column per unique value:
This creates a table with regions as rows and product categories as columns. Total sales in each cell.
With multiple metrics:
Shows both total sales and order count in each cell.
Verdict: For datasets under 50,000 rows, GROUPBY is faster in practice because there is no refresh step. For larger datasets, Pivot Tables retain a performance advantage.
Use Excel Tables as source data. GROUPBY and PIVOTBY work best with structured references from Excel Tables (Ctrl+T). They expand automatically when you add rows.
Combine with SORT for control. GROUPBY's built-in sorting is limited. Wrap it with SORT for more options:
Use PIVOTBY for dynamic dashboards. Unlike Pivot Tables, PIVOTBY formulas can be referenced by charts and other formulas directly.
How They Work Under the Hood
Advanced GROUPBY Patterns
=GROUPBY(tblSales[Region], {tblSales[Amount], tblSales[Amount]}, {SUM, AVERAGE})=GROUPBY(tblSales[Region], tblSales[Amount], LAMBDA(x, MEDIAN(x)))=GROUPBY(tblSales[Region], tblSales[Amount], SUM, , , -2)PIVOTBY for Cross-Tabulation
=PIVOTBY(tblSales[Region], tblSales[Category], tblSales[Amount], SUM)=PIVOTBY(tblSales[Region], tblSales[Category], {tblSales[Amount], tblSales[Amount]}, {SUM, COUNT})Performance: GROUPBY vs Pivot Table
| Scenario | GROUPBY | Pivot Table |
|---|---|---|
| 10,000 rows, 5 groups | Instant | Instant |
| 100,000 rows, 20 groups | 2-3 seconds | <1 second |
| 500,000 rows, 50 groups | 8-10 seconds | 2-3 seconds |
| Dynamic (data changes often) | ✅ Auto-updates | ❌ Needs manual refresh |
| Formula reference | ✅ Can be used in other formulas | ❌ Requires GETPIVOTDATA |
Pro Tips
=SORT(GROUPBY(tblSales[Region], tblSales[Amount], SUM), 2, -1)Related Articles
- GROUPBY Function Guide — Beginner-friendly introduction
- Excel June 2026 Update — All new features in the latest update
- Pivot Table Beginner Guide — When to use Pivot Tables instead
Topics
Topics in this article
Explore related topics and continue reading similar content.
Share this article
Discussion
Preparing the comments area...