---
title: "3-Minute Win: Replace Your Most-Used Pivot Table With a Single GROUPBY Formula"
date: 2026-06-13T00:00:00Z
updated: 2026-06-14T05:03:06Z
tags: ["Excel", "GROUPBY", "Dynamic Arrays", "Modern Excel Functions", "Structured Tables", "Pivot Tables"]
canonical: https://bianca.codes/blog/replace-pivot-table-with-groupby/
---

# 3-Minute Win: Replace Your Most-Used Pivot Table With a Single GROUPBY Formula

_A pivot table is a snapshot that needs refreshing. GROUPBY is a formula that recalculates itself. Swapping one for the other takes about 90 seconds and removes a recurring chore._

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.

1. Click an empty cell with room to spill below it.
2. Type: `=GROUPBY(Transactions[Region], Transactions[Amount], SUM)`
3. 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).