Lookup & Reference
Modern
PIVOTBY
Builds a pivot-table-style summary with a formula, grouping by row and column fields and aggregating with a chosen function.
Available in Microsoft 365 (GA Sept 2024, Current Channel). MS function page also lists Excel 2024/2021, but this conflicts with the rollout — treat as 365 and verify in your build. Not 2019/2016.
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])What it returns
A spilled array — an aggregated cross-table.
Arguments
| Argument | Required | Description |
|---|---|---|
row_fields |
Yes | |
col_fields |
Yes | |
values |
Yes | |
function |
Yes | |
field_headers |
No | |
row_total_depth |
No | |
row_sort_order |
No | |
col_total_depth |
No | |
col_sort_order |
No | |
filter_array |
No | |
relative_to |
No |
Example
=PIVOTBY(A2:A100, B2:B100, C2:C100, SUM)Important to know
Spills.
function is an aggregation reference (SUM, COUNT…) or a LAMBDA.
Recent (GA Sept 2024) — confirm it exists in the target build.
A live-formula alternative to a classic PivotTable.
Related learning
No MEE lesson covers this function yet.