📎 This article includes 1 downloadable practice file ↓
In this article
PIVOTBY groups by rows and columns at once — regions down, months across — as a formula.
Syntax
=PIVOTBY(row_fields, col_fields, values, function, ...)| Argument | What it means |
|---|---|
col_fields |
Column(s) that become headers across. |
Examples
Example 1
=PIVOTBY(B2:B500, TEXT(A2:A500,"mmm"), E2:E500, SUM)Region × month sales grid.
PIVOTBY in practice
Where you will use it
- Region × month sales grid in one formula
- Live cross-tabs
- Replace PivotTables for simple reports
Worked example
Data: North Apr 12000, South Apr 8000, North May 15000, South May 9000, North Apr 3000.
| Aggregation | Formula | Result |
|---|---|---|
| SUM | =PIVOTBY(Region,Month,Sales,SUM) | North: Apr 15000, May 15000; South: Apr 8000, May 9000; totals Apr 23000, May 24000, all 47000 |
| COUNT | =PIVOTBY(Region,Month,Sales,COUNT) | North: Apr 2, May 1; South: Apr 1, May 1; total 5 |
| MAX | =PIVOTBY(Region,Month,Sales,MAX) | North: Apr 12000, May 15000; South: Apr 8000, May 9000; overall 15000 |
Worked out step by step, not calculated here: PIVOTBY needs the latest Microsoft 365.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Older Excel | Needs the latest Microsoft 365 |
| Too many columns | Choose a column field with few distinct values |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Dynamic arrays18 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong