---
title: "Pivot Tables Are Still Great - But GROUPBY Is What You Should Be Using for New Work"
date: 2026-06-17T00:00:00Z
updated: 2026-06-27T11:15:25Z
tags: ["Dynamic Arrays", "Excel", "Formulas", "Microsoft 365"]
canonical: https://bianca.codes/blog/pivot-tables-still-great-use-groupby-for-new-work/
---

# Pivot Tables Are Still Great - But GROUPBY Is What You Should Be Using for New Work

_Pivot tables aren't obsolete, but reaching for one by reflex on every new analysis is a quiet tax GROUPBY does not charge._

## For anything you build from a blank sheet in Excel 365, GROUPBY should be the default and the pivot table the deliberate exception

Not because pivot tables got worse. They are exactly as good as they have always been. But the moment you are starting fresh, the reasons to reach for a pivot table by reflex have mostly evaporated, and the reasons to write a formula instead have quietly stacked up. The pivot table is still the right call sometimes. It just should not be the automatic one.

I once compared pivot tables to Crocs. I understand the market appeal, I can see exactly where they deliver value, and I still would not be seen dead in a pair. New analysis work is the same call: the pivot table works, the appeal is real, and I reach for the formula anyway.

## Why the pivot table is still everyone's first move

Because it has been the answer for twenty-five years, and muscle memory is a powerful thing. Insert, PivotTable, drag a field into Rows, drag a number into Values, done. No syntax to remember, no arguments to get in the right order, and a surface that shows you the result as you build it. For a one-off "what does this add up to by region" question, it is genuinely hard to beat.

It is also what every course, every colleague, and half the internet taught you. When the task is "summarise this," the pivot table is the path of least resistance, and least resistance wins almost every time you are not actively thinking about it.

## Where the reflex costs you on new work

The pivot table is a cached snapshot, and that cache is the source of every pivot table annoyance you have ever had.

**It goes stale.** A pivot reads from a copy of the data taken at refresh time. Add a row, change a number, and the pivot keeps showing yesterday's answer until someone remembers to right-click and refresh. On a report you touch daily, that is a recurring manual chore with a wrong-number failure mode baked in.

**It does not compose.** A pivot table is a terminal object. You cannot cleanly feed its output into another formula, because its shape and position shift as the data changes. The cell you wanted to reference moves, and the reference breaks.

**It drifts from the layout around it.** Pivots want their own space, expand unpredictably, and overwrite whatever sits below them. Building anything structured around a pivot means fighting its footprint instead of designing your sheet.

None of these are dealbreakers for a quick look. All of them are a slow tax on anything you intend to keep and maintain.

## The better way for the blank-sheet case

`=GROUPBY(Table[Region], Table[Amount], SUM)` does the same split-apply-combine a pivot does, written as a formula. It reads the live range on every recalculation, so it never goes stale and never needs refreshing. It spills a clean array you can reference, sort, filter, and nest like any other formula. And it follows a structured table reference as the data grows, with no footprint surprises.

There is a quieter benefit too. Writing the formula makes you name the grouping column, the value column, and the aggregation out loud, so you end up knowing your data backwards and forwards instead of dragging fields until the total looks about right. A pivot table lets you get an answer without ever understanding the shape of what you are summarising. The formula does not give you that option, and that is a feature.

For the mental model and the full argument list, the Jun 15 Magic Monday takes it apart properly, and the Jun 13 Click Bait is the ninety-second version if you only want to swap one pivot and feel the difference.

The rule is not "never use a pivot table again." The rule is: when you sit down to build something new, write the formula first, and reach for the pivot table only when you actually need what only it gives you - an interactive surface someone is going to drag fields around in.

## Frequently Asked Questions

### **Does this mean pivot tables are obsolete?**

No. Pivot tables are still the right tool when the deliverable is interactive - something a user explores by dragging fields, expanding groups, and slicing on the fly. GROUPBY produces a fixed output, not a surface to poke at. The argument is about the default for new work, not a ban.

### **Do I need to rebuild all my existing pivot tables as GROUPBY?**

No, and you probably shouldn't. A pivot table that already works and that nobody complains about is not worth the migration. This is about what you reach for next time you start from a blank sheet, not a weekend spent rewriting things that are already fine.

### **Which versions of Excel have GROUPBY?**

Microsoft 365 on a current channel, and Excel for the web. It is not in Excel 2021, 2019, or the perpetual Excel 2024, where it returns `#NAME?`. If your organisation is on a perpetual licence, the pivot table stays your default by necessity, not by choice.

### **What about really large datasets?**

GROUPBY is an in-grid array function and has the same scale ceiling as the rest of them. On a few thousand rows it is instant; on hundreds of thousands it will make the sheet think, and that work belongs in Power Query or the data model. A pivot table, reading from a cache, handles a large source range more comfortably.

### **Can GROUPBY do a two-way cross-tab like a pivot table?**

Not on its own - GROUPBY groups into rows. Its sibling PIVOTBY adds a column-fields argument for a true matrix, with one set of groups down the side and another across the top. Same family, same syntax, one more dimension.

_A pivot table is something you operate. A formula is something you write once and stop thinking about. For new work, pick the one that does not need you standing over it._