Data Analysis•3 min read
Excel GROUPBY and PIVOTBY: Deep Dive into New Aggregation Functions
Deep dive into Excel GROUPBY and PIVOTBY functions. Learn advanced usage, performance tips, and when to use them.
SheetHub••3 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
Recommended Next Reading
Excel
=EXCEL(...)Excel MODE.MULT: Find Multiple Modal Values in Data
Explore ↗
Excel
=EXCEL(...)Excel ROUND Function: MROUND, CEILING & FLOOR Guide
Explore ↗
Excel
=EXCEL(...)Excel LAMBDA Recursive Loops: Advanced Calculations
Explore ↗
Share this tutorial
Discussion & Community
Share questions, tips, or edge-cases about this spreadsheet formula.