By Hiran de Silva
There’s widespread misunderstanding of what pivot tables are actually for. The clue is in the name: pivot.
To pivot is to change from something to another. And maybe continue to change. It is therefore a dynamic concept.
A Little History
By the early 1990s, spreadsheets and databases like Microsoft Access were producing long lists of data. Analysts needed ways to mine that data:
- Summarise it by category
- Filter it by criteria
- Crosstab it (laying out categories as both rows and columns)
In 1993, Excel introduced the PivotTable Report—not “pivot table” as we casually call it today. The word “report” is important: it was designed as a tabular report that you can pivot dynamically.
To pivot meant to drag and drop fields—rows become columns, columns become rows. It was dynamic, exploratory, interactive.
Where the Misunderstanding Crept In
Over time, Microsoft changed the default behaviour. Instead of opening in the old “pivot view” (which highlighted its dynamic nature), users were presented with something that looked like a static report.
That subtle shift in interface trained generations of users to see pivot tables as static crosstabs. Many don’t even realise that the pivoting part still exists.
Today, “pivot” has become shorthand for “crosstab.” But strictly speaking:
- Crosstab = static report view
- Pivot = dynamic exploration
Why This Matters Now
This historical misunderstanding is resurfacing in current debates. On social media, some influencers claim that Excel’s new GROUPBY and PIVOTBY functions will replace pivot tables.
That’s only true if you think pivot tables are nothing more than static crosstab reports. But the original purpose—the ability to pivot dynamically, to mine data from different angles—remains untouched.
And here’s the irony: many of the same people making this claim are also evangelists for Power Query. Yet most Power Query tutorials end up by… producing a pivot table!
So the real question is:
👉 If GROUPBY and PIVOTBY “kill” pivot tables, do they also kill Power Query?
The Bigger Picture
- Most users want static summaries, not interactive analysis.
- The demographic of Excel users has shifted toward basic tasks.
- But for those who do want to mine data, explore patterns, and interrogate gaps, the pivot table remains unmatched.
The pivot table was never just a reporting tool. It was always meant to be a data mining tool hiding in plain sight.
Add comment