PIVOTBY Function
Builds a full PivotTable-style cross-tab with a single formula.
Category
Lookup & Reference
Available in
Microsoft 365
Returns
A spilled cross-tab table
Overview
PIVOTBY groups by rows and columns at once — for example, regions down the side and months across the top — and aggregates the values where they meet.
It's the formula version of a PivotTable: always up to date, no refresh needed.
Syntax
=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])
Arguments
| Argument | Required? | What it does |
|---|---|---|
| row_fields | Yes | Values to group down the side. |
| col_fields | Yes | Values to group across the top. |
| values | Yes | The data to aggregate. |
| function | Yes | How to aggregate: SUM, AVERAGE, COUNTA, PERCENTOF, etc. |
| … | Optional | Headers, totals, sorting, filtering and percent-of settings, as in GROUPBY. |
Examples
Example 1 — Sales by region and quarter
Column A holds region, B quarter, C sales.
| E | F | G | H | |
|---|---|---|---|---|
| 1 | Q1 | Q2 | Total | |
| 2 | East | 900 | 640 | 1540 |
| 3 | West | 625 | 710 | 1335 |
| 4 | Total | 1525 | 1350 | 2875 |
=PIVOTBY(A2:A100, B2:B100, C2:C100, SUM) Explain
Example 2 — Share of total
Show each cell as a percentage of the grand total.
=PIVOTBY(A2:A100, B2:B100, C2:C100, PERCENTOF) Explain
Format the result as a percentage to make PERCENTOF readable.
Common mistakes
- Mismatched sizes. row_fields, col_fields and values must all have the same number of rows.
Notes
- Related functions: GROUPBY, GETPIVOTDATA, SUMIFS.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.