GROUPBY and PIVOTBY: PivotTables as formulas
Build the same summaries with Excel 365's GROUPBY and PIVOTBY functions, which update by themselves. Advanced · 6 min · GROUPBY, PIVOTBY, PERCENTOF, Dynamic arrays
Microsoft 365 has two functions that do what a PivotTable does, as formulas that spill:
=GROUPBY(row_fields, values, function)groups rows and aggregates each group, with a Total row.=PIVOTBY(row_fields, col_fields, values, function)adds a field across the top: a cross-tab.
The function is written by name, without brackets: SUM, AVERAGE, COUNTA, MAX, MIN or PERCENTOF.
Unlike a PivotTable, they never need refreshing, and other formulas can refer to their results. In older versions of Excel they show #NAME?.
Steps
In
I2, total the sales by region:=GROUPBY(B2:B25, F2:F25, SUM). The regions are the row field and the sales are the values.In
I10, rank them instead: add sort_order as the sixth argument.-2means "by the 2nd column, descending":=GROUPBY(B2:B25, F2:F25, SUM, 0, 1, -2)In
I18, show each region’s share of all sales: usePERCENTOFas the function,=GROUPBY(B2:B25, F2:F25, PERCENTOF). The cells are already formatted as percentages.Finally, a cross-tab in
L2: regions down the side and products across the top,=PIVOTBY(B2:B25, C2:C25, F2:F25, SUM)
Remember
=GROUPBY(rows, values, SUM)groups and totals; the 6th argument sorts (negative for descending).=PIVOTBY(rows, columns, values, SUM)builds a cross-tab.- The function goes in by name: SUM, AVERAGE, COUNTA, MAX, MIN or PERCENTOF.
- They are live formulas: no Refresh, and other formulas can use their results. They need Microsoft 365.
In the function reference: GROUPBY, PIVOTBY
Previous: PivotTables: dates by month and quarter · Next: Your first chart