Excel like a finance pro.
Dynamic array function
PIVOTBY
Cross-tabulates data by rows and columns in one formula.
When to use it
GROUPBY with columns too: rows by one field, columns by another, values aggregated in the grid. A cross-tab that stays live.
The shape of it
- Syntax
=PIVOTBY(row_fields, col_fields, values, function, ...)
Worked examples
Two-way summary
=PIVOTBY(C2:C6,A2:A6,B2:B6,SUM) → regions down, products across
Price by region and product.
Calculated columns
=PIVOTBY(A2:A100,YEAR(B2:B100),G2:G100,SUM) → customers down, years across
Column headers can be a calculation.
No totals
=PIVOTBY(C2:C6,A2:A6,B2:B6,SUM,,0,,0) → grid without totals
Row and column totals turned off.
Worth knowing
- Use MAX or a LAMBDA when the cells should show something other than sums.
- Combine with FILTER on the inputs for a filtered pivot.
- Empty combinations show 0 by default.
Where it goes wrong
- #NAME? before Excel 2024 / Microsoft 365.
- All input ranges must be the same height.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.