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

Taught hands-on in Level 5: Expert Techniques & Workbook Architecture · Modern Functions module

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

ArgumentRequired?What it does
row_fieldsYesValues to group down the side.
col_fieldsYesValues to group across the top.
valuesYesThe data to aggregate.
functionYesHow to aggregate: SUM, AVERAGE, COUNTA, PERCENTOF, etc.
…OptionalHeaders, 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.

EFGH
1Q1Q2Total
2East9006401540
3West6257101335
4Total152513502875
The spilled result.
=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

Kyle Stott

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.