Excel GROUPBY function: a pivot table alternative

A PivotTable does the job until the source range shifts. Add a row, rename a column, hand the workbook to a teammate who doesn’t know to hit Refresh — the summary quietly stops matching the data underneath. GROUPBY is Excel’s answer to that fragility: the same grouping, the same totals, as a single formula that recalculates the moment the source changes. It arrived in Microsoft 365 in late 2023, and a year of real workbooks later it’s clear when it beats a PivotTable and when it doesn’t.

This walkthrough sticks to the cases where GROUPBY earns its place: dashboards that read from a Table, models that get rebuilt nightly, reports that someone else opens. You’ll see the eight arguments, two filter patterns the docs barely mention, and the three error states that catch every new user.

When GROUPBY beats a pivot table

PivotTables still win for ad-hoc exploration — drag a field, slice it, drag it back. GROUPBY wins everywhere else: anywhere the summary needs to live next to a formula, refresh automatically, or be reproduced by someone who only opens the file once a month.

The version constraint is the one hard line. GROUPBY ships only in Microsoft 365 and Excel 2024; older builds return #NAME?. If half your team is on Excel 2019, the PivotTable still wins by default. Our walkthrough of building pivot tables in Excel covers the workflow that’s still the right choice in that environment.

The mental shift

A PivotTable is a tool you operate. GROUPBY is a formula you write. That changes who maintains the workbook: the person who knows the formula, not the person who remembers which checkbox to tick in the field list.

The eight arguments at a glance

The function signature looks intimidating, but only three arguments are required. The other five default to sensible behavior, and you’ll touch maybe two of them in practice.

=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])

What each one does:

• ✓ row_fields — the column (or columns) to group rows by
• ✓ values — the column to aggregate
• ✓ function — SUM, AVERAGE, COUNT, MAX, or any LAMBDA
• ✓ field_headers — 0 hides them, 3 shows them; default is auto
• ✓ total_depth — 0 none, 1 grand total, 2 grand + subtotals; negative values move totals on top
• ✓ sort_order — column index to sort by; negative sorts descending
• ✓ filter_array — a boolean array the same height as the source
• ✓ field_relationship — 0 for hierarchy (default), 1 for table

Skip the optional five the first time you write a formula. Get the grouping right, then layer on totals and sorting once the shape is correct.

A real example: sales by product

Picture a Table named Orders with three columns — order date, product, and amount. The goal is a summary by product, sorted by largest revenue, with a grand total at the bottom.

Drop this formula into any empty cell — E2 works:

=GROUPBY(Orders[Product], Orders[Amount], SUM, 3, 1, -2)

The arguments, left to right: group by Product, aggregate Amount, use SUM, show headers (3), include a grand total (1), and sort descending by the second column (-2). The result spills across two columns and as many rows as there are distinct products, plus one row for the total.

Multi-column grouping and totals

Pass two columns to row_fields and GROUPBY nests them — region above product, region above month, whichever order you stack. The total_depth argument decides whether subtotals appear under each parent.

=GROUPBY(Orders[[Region]:[Product]], Orders[Amount], SUM, 3, 2)

This formula groups by region first, then by product inside each region, and prints both subtotals (one per region) and a grand total. The 2 in total_depth is the difference between a flat list and a true hierarchical summary.

The filter_array argument no one demonstrates

Every top result lists filter_array in the syntax block and stops there. It’s the most useful argument once you know what shape it expects: a column-oriented boolean array the same height as the source. TRUE keeps a row, FALSE drops it — before grouping, not after.

Say the goal is summing only orders above $50. Write the filter inline:

=GROUPBY(Orders[Product], Orders[Amount], SUM, 3, 1, -2, Orders[Amount]>50)

The expression Orders[Amount]>50 evaluates to an array of TRUEs and FALSEs, one per row. GROUPBY consumes that array, ignores the FALSE rows, and groups what’s left. Stack two filters with multiplication for an AND, or with addition for an OR:

=GROUPBY(
  FILTER(Orders[Product], (Orders[Amount]>50)*(Orders[Region]="EU")),
  FILTER(Orders[Amount], (Orders[Amount]>50)*(Orders[Region]="EU")),
  SUM)

=GROUPBY(
  Orders[Product], Orders[Amount], SUM,
  3, 1, -2,
  (Orders[Amount]>50)*(Orders[Region]="EU"))

The right-hand form is shorter, easier to audit, and impossible to desync. Use it for any filter narrower than “all rows.”

When the formula breaks: three errors to know

Three error states cover almost every GROUPBY question on the support forums. They’re easy to fix once you know what each one is saying.

1. #SPILL! — the result needs more cells than are free below or to the right. Clear the cells, or move the formula to a roomier corner of the sheet.
2. #VALUE! — row_fields and values have different row counts. The two ranges must match height exactly; a stray header row in one but not the other will trigger this.
3. #CALC! — the function argument isn’t a recognized aggregator. SUM and AVERAGE work as bare names; a custom aggregator must be wrapped in LAMBDA.

The #SPILL! case is the one that catches everyone the first day. GROUPBY writes a dynamic array, so the cells below the formula must be empty — even a single space character in the next row blocks the spill.

Migrating a PivotTable to GROUPBY

If a PivotTable already exists and you want the formula equivalent, work backward from the field list. Map the boxes you’ve already filled — Rows, Values, the aggregation choice — straight to the first three arguments. Filters become the filter_array. Subtotals become total_depth.

1. Note the Rows field — that’s row_fields.
2. Note the Values field and its aggregation — that’s values and function.
3. Note any filter applied at the top — that becomes filter_array as a boolean expression.
4. If subtotals show, set total_depth to 2; if only a grand total, 1; if none, 0.
5. Drop the formula in any empty cell and check the two outputs side by side for a few minutes before deleting the original PivotTable.

One caveat: a PivotTable’s row labels show the source value with original casing and spacing. GROUPBY treats "Keyboard" and "keyboard " as different keys, trailing space and all. Run TRIM and PROPER over the source column once before relying on the summary. Our notes on cleaning data before a pivot apply word-for-word here.

Where GROUPBY fits in a modern workbook

Treat GROUPBY as the default for any summary that lives inside a formula chain, gets recomputed automatically, or has to survive someone else opening the file. Keep PivotTables for the moments when you’re still deciding what the summary should be. Once the answer is settled, the formula version is what you ship.

• ✓ Everyone on the team runs Microsoft 365 or Excel 2024
• ✓ The source range is a Table, not a fixed reference
• ✓ The summary feeds a chart or a downstream formula
• ✓ The workbook is opened by someone who won’t remember to hit Refresh

Start with one summary you already maintain in a PivotTable. Rewrite it as a GROUPBY formula, place the two outputs side by side, and watch them stay in sync when you append a row. After the second or third conversion the function stops feeling like a new feature — it just becomes the way you build summaries. For the next level up, our walkthrough on pivot table patterns covers the slicer and timeline workflows that still belong inside a real PivotTable.