Pick the pivot table you refresh most often. The one you right-click and refresh three times a day because the source keeps changing. A pivot table is a cached snapshot, so it goes stale the moment the data moves underneath it. GROUPBY is a formula, so it never does. Swapping one for the other takes about 90 seconds, and then it is just gone from your list of chores.
How to do it
You have a transactions table with a Region column and an Amount column, and a pivot table that sums amount by region.
- Click an empty cell with room to spill below it.
- Type:
=GROUPBY(Transactions[Region], Transactions[Amount], SUM) - Press Enter.
The grouped totals spill straight down: one row per region, each with its summed amount, sorted and ready. Add a second aggregation by stacking the value and function arguments: =GROUPBY(Transactions[Region], HSTACK(Transactions[Amount], Transactions[Amount]), HSTACK(SUM, AVERAGE)) gives you sum and average side by side. When a new transaction lands in the table, the result updates on recalculation. No refresh, no right-click.
Why it works
A pivot table reads from a pivot cache, a frozen copy of the source taken at refresh time. That is why it needs refreshing at all. GROUPBY reads the live range every time the sheet recalculates, the same as any other formula. The three required arguments map cleanly onto what a pivot does for you: row_fields is the rows area, values is the values area, and function is the summarisation (SUM, AVERAGE, COUNT, MAX, and the rest). Point it at a structured table reference rather than a fixed range and it follows the data as the table grows.
When not to use it
If the pivot table is something people poke at - dragging fields around, expanding and collapsing groups, slicing interactively - leave it alone. GROUPBY produces a fixed output shape, not an interactive surface. And it only exists in current Microsoft 365; on Excel 2021 or earlier the function is not there, so the pivot stays.
The full version
For the mental model behind GROUPBY, the full argument list, and the patterns it unlocks beyond a straight swap, see Magic Monday: GROUPBY (15 June).