Course / PivotTables

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:

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

  1. 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.

  2. In I10, rank them instead: add sort_order as the sixth argument. -2 means "by the 2nd column, descending": =GROUPBY(B2:B25, F2:F25, SUM, 0, 1, -2)

  3. In I18, show each region’s share of all sales: use PERCENTOF as the function, =GROUPBY(B2:B25, F2:F25, PERCENTOF). The cells are already formatted as percentages.

  4. Finally, a cross-tab in L2: regions down the side and products across the top, =PIVOTBY(B2:B25, C2:C25, F2:F25, SUM)

Remember

In the function reference: GROUPBY, PIVOTBY

Previous: PivotTables: dates by month and quarter · Next: Your first chart