This article is not for the faint hearted!
As Md Ismail Hosen pointed out in a bit of a rant on LinkedIn just now, there are hundreds of new videos and posts on social media claiming that Pivot Tables are dead. They’re saying it’s been replaced by Group By and Pivot By. But let’s get one thing straight: Pivot Tables aren’t dead. And the fact that people are saying this shows just how little today’s Excel users understand what “pivot” in a Pivot Table actually means.
It turns out the present generation of Excel users has never really experienced what it means to pivot in a Pivot Table. And I’ve just realized why. For years, I thought Microsoft had re-engineered the user interface (UI) of Pivot Tables–turns out they haven’t. But what they *have* done is hidden the true dynamic power of Pivot Tables, leaving users stuck with a static version, and nobody seems to know, or have experienced, what a real Pivot Table is for.
As a result, hundreds of videos and YouTube posts are talking complete nonsense. It’s embarrassing. So before I show you how pivoting actually works, let me explain this with an illustration.
SUVs Replacing Airplanes? Sure, Why Not?
Imagine boarding a commercial aircraft. The plane backs out from the gate and starts taxiing toward the runway, right? We’re all familiar with this. But now imagine someone posts a “shocking” revelation saying that **SUVs** will replace aircraft. Yes, SUVs! They do it better! They can drive faster than an Airbus A380 taxiing, turn more easily, and drive you home right to your garage. Amazing, right? So clearly, SUVs are better than airplanes. Airbus A380? Obsolete. Boeing 787? Forget it. We don’t need airplanes anymore because SUVs are more flexible, more affordable, and can do things that airplanes can’t, like parking in your driveway. (If the penny hasn’t dropped, airplanes are for something a lot more than taxiing to the runway)
Ridiculous, isn’t it? Yet this is exactly what’s happening in the Excel world. Social media influencers are convincing people that Group By, Power Query, or whatever the new thing is, has “replaced” Pivot Tables–because they never understood what Pivot Tables were designed for in the first place.
BTW, airplanes fly. Ok?
Getting the Wrong End of the Stick
We have a bad habit in the Excel ecosystem, maybe in other industries too, where we collectively misunderstand something, get the wrong end of the stick, and run with it. Terminology gets misused, concepts get twisted, and suddenly, you have masses of people using the wrong features for the wrong reasons. It’s like a game of Chinese whispers–one person misunderstands something and passes it on, and before you know it, you’ve got thousands of people talking complete bollox.
And Pivot Tables are one of those things that have been caught in the crossfire. The term “pivot” has been misused for so long that no one even remembers what it really means anymore. So let me clear this up for you.
Pivot Is Dynamic–Not Static!
Pivoting is an action. Think of a tennis player or a ballet dancer–they pivot on the balls of their feet to change direction. It’s a movement, not something static. But ask most Excel users today, and they’ll tell you that a Pivot Table is just a static cross-tab report. That’s because they’ve never experienced the real, dynamic power of a Pivot Table. Microsoft, for reasons I can only guess, relegated the classic Pivot Table UI to a forgotten option in the settings, and users never venture into changing the UI to actually pivot their data dynamically.
Which is what it is for!
The whole point of a Pivot Table is to spin your data around, explore different views, and discover patterns and insights. It’s like an early form of data mining. But you can’t do that unless your Pivot Table is dynamic. Unfortunately, most users today think of Pivot Tables as static reports because that’s all they’ve ever known.
Misusing Terms: From Pivot to Cross Tab
Somewhere along the line, people started confusing “pivot” with “cross tab.” Cross tabulation, or Cross Tab for short, is a static way of summarizing data that’s been around for decades. This used to be called a ‘tabulation’. But in Excel, the term “pivot” got misapplied to these static reports, and the misuse stuck. That’s why today, when people say they’re “pivoting,” they’re really just cross-tabulating. Static.
It’s like saying an SUV can replace a plane. Yes, an SUV can drive you from point A to point B with much more agility than a massive A380, but it’s not a replacement for a plane when you need to fly across an ocean. Just like static cross-tabs are not replacements for the real dynamic Pivot Table.
And, what is Power Query, exactly?
Now, let’s talk about Power Query. Power Query is fantastic for ETL (Extract, Transform, Load). But here’s the thing: social media influencers have gone crazy, using Power Query for everything under the sun. Microsoft even tried renaming it to “Get and Transform” to clarify its purpose, but there was a massive backlash. Influencers had built entire courses and content around the name “Power Query,” and they weren’t about to let Microsoft mess with their branding.
The choice of the name Power Query is obvious. Microsoft were calling everything new ‘power-this’, power-that’ as the new generation of tools. Power Pivot, Power Automate, Power Apps, Power BI. So the earlier MS Query is now a much more comprehensive powerhouse they called Power Query. That makes sense. Until people who never knew MS Query started doing stuff it wasn’t meant for. It’s an ETL tool, dammit!
So here we are, with Power Query being used and misused in all sorts of ways that it was never intended for. Don’t believe that? Just look up YouTube, social media.
The Brylcreem Story
This reminds me of an old story. In the 1930/40/50s, Brylcreem was a popular hair gel for men in Western countries. It was for holding down hair that was light and that fly in the wind. Right? Surprisingly, this British company found that it sold like hotcakes in Africa–but not for the reasons you’d think. People in Africa don’t usually have light hair that flies in the wind. Ok? Turns out, people in Africa were using it as cooking oil because it was promoted as cooking oil in Africa. Excel users today are treating Power Query like Brylcreem. They’re using it for all kinds of bizarre purposes because they’ve been sold on the idea that it’s the solution to every problem. After all, the term ‘Power Query’ had gained high social media kudos, so anything could be sold easier if you simply threw it in in the headline. Meanwhile, they’ve completely forgotten–or never learned–what an ETL tool is.
The Rise of the Social Media “Experts”
Here’s the real issue: (not all but) many of the people promoting this nonsense have never actually used Excel in a professional environment in a sizable company. They’ve built entire careers around creating YouTube videos and training courses. Neither of which requires actually solving real business problems. So their real-world experience? Zero. Their success is measured in likes and subscribes, not in solving real business problems.
Sadly, likes and subscribes don’t solve business problems, and employers don’t pay for likes and subscribes. So the next time you see a 10-minute video on how to do something convoluted that could be solved in 10 seconds with simply Excel and some creative thinking, remember: you’re watching the Excel equivalent of replacing an airplane with an SUV. At the end of the day, real Excel work is about results–not likes, not subscribes, and certainly not static cross-tabs pretending to be dynamic Pivot Tables.
Add comment