The Excel PIVOTBY function builds a complete cross-tab — row groups down the side, column groups across the top, aggregated values in the grid, totals included — from a single formula. It’s GROUPBY’s two-dimensional sibling and the closest Excel has come to a PivotTable that recalculates live, never needs refreshing, and can’t go stale.
Syntax
| Argument | Description | |
|---|---|---|
row_fields | Required | Column(s) whose unique values become the row labels. Two adjacent columns give two grouping levels. |
col_fields | Required | Column(s) whose unique values run across the top. |
values | Required | The numbers (or text) to aggregate. |
function | Required | The aggregation: SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, MEDIAN, ARRAYTOTEXT, a custom LAMBDA, and more — written without parentheses. |
field_headers | Optional | 0–3: whether your ranges include headers and whether to show them in the result. |
row_total_depth / col_total_depth | Optional | How many levels of totals to show (0 = none, 1 = grand totals, 2 = grand + subtotals). Negative values put totals first. |
row_sort_order / col_sort_order | Optional | Which column/row to sort the groups by; negative = descending. |
filter_array | Optional | TRUE/FALSE array — rows where it’s FALSE are excluded before pivoting. |
Available in: Excel for Microsoft 365 only (Windows, Mac, and the web). Perpetual versions through Excel 2024 show #NAME?. The result is a dynamic array that spills.
A pivot table in one formula
Sales data sits in three columns: Region (A), Quarter (B), Amount (C). One formula in F1 produces the whole cross-tab below — labels, grid, and a totals row and column:
| F | G | H | I | |
|---|---|---|---|---|
| 1 | Q1 | Q2 | Total | |
| 2 | East | 3,400 | 4,100 | 7,500 |
| 3 | North | 5,900 | 6,300 | 12,200 |
| 4 | South | 4,700 | 5,200 | 9,900 |
| 5 | Total | 14,000 | 15,600 | 29,600 |
Note SUM is passed as a bare function name — this is an eta-reduced lambda, the same convention GROUPBY and BYROW use. Add a new sale to the source and the pivot updates on the spot; there is no Refresh button because none is needed.
Try it: interactive PIVOTBY demo
A 10-row sales table is pivoted live below. Switch the aggregation and the totals setting and watch the cross-tab rebuild.
Sorting, filtering, percentages, and custom lambdas
The optional arguments cover most PivotTable conveniences. Sort regions by their totals, descending, and pivot only the rows that pass a filter:
The function argument accepts PERCENTOF for share-of-total views, and full LAMBDAs when the built-ins aren’t enough:
Need just one dimension — row groups with no columns across the top? That’s GROUPBY, which shares every argument except col_fields.
PIVOTBY or a real PivotTable? PIVOTBY wins for live-updating reports that feed other formulas. Classic PivotTables still win for drag-and-drop exploration, drill-down, and very large source data.
Errors & common pitfalls
#NAME? — not on Microsoft 365. PIVOTBY ships only in Microsoft 365 (and Excel for the web). Excel 2024 and every earlier perpetual release don’t have it.
#VALUE! — mismatched range lengths. row_fields, col_fields, values, and filter_array must all cover the same number of rows. A range that’s one row short is the usual culprit.
#SPILL! — the landing zone isn’t empty. A pivot output is wide and tall, and it grows as new groups appear in the data. Give it generous empty space.
Pitfall: writing the function with parentheses. It’s SUM, not SUM() and not "SUM". You’re passing the function itself, not calling it.
Pitfall: headers swallowed into the groups. If your ranges include header cells, tell PIVOTBY via field_headers (3 = has headers, show them) — otherwise “Region” turns up as a group label.
Practice workbook
Frequently asked questions
What's the difference between PIVOTBY and GROUPBY?
col_fields, spreading a second field across the top to form a true cross-tab. The other arguments are essentially the same.Does PIVOTBY replace PivotTables?
Why do I write SUM without parentheses in PIVOTBY?
How do I turn off the totals row in PIVOTBY?
Which Excel versions have PIVOTBY?
Can I filter the data before pivoting?
filter_array argument (a TRUE/FALSE column), or wrap the source ranges in FILTER before they reach PIVOTBY.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class