This may come as a surprise. There is no doubting the supreme analytical power of pivot tables. Simply select the data, drop some headings into boxes and there you have it: powerful tabulated analysis of your data.
And there’s more: combined with visual features such as pivot charts and slicers, pivot tables allow you to create cool Excel dashboards without too much trouble.
That’s great … if you can put up with the downside.
I love doing real-world Excel consultancy work. It reminds me of the limiting factor in Excel development, the one thing that, for me, determines your long-term impact as a programmer:
Can other people easily use the files you develop?
Me and pivot tables go back a long way. I delivered a solution for a big sports organisation in the UK a few years ago using pivot tables, charts and slicers. It looked awesome and it was a big breakthrough in my Excel learning. The customer loved it! In fact, they still use the file today to evidence how they use time effectively to train staff.
So what happened?
A similar enquiry came in just a year later. This was about financial analysis, but the requirements were similar: to quickly ‘slice-and-dice’ transaction data and display the results in a dashboard.
“Too easy!” I thought. The same approach using pivot tables would do the job. Who doesn’t like an easy payday?
Then my client dropped into the briefing “Oh, we don’t want to use pivot tables – we’ve had too many bad experiences with them!”
Of course, I offered constructive challenge: it would be easy to put together training videos to built staff confidence with pivot tables, for example …
They weren’t interested.
Real client work real brings you down to earth. Just when you think you have Excel sorted, a new and unfamiliar set of requirements come about that need a different approach. If you haven’t experienced this, you simply haven’t done enough Excel work … It teaches a certain humility.
As I’ve learned, different ways work in different situations.
Through necessity, I set about learning Excel’s data analysis formulae, including AVERAGEIFS and its variants, and DSUM. Sure, there were teething problems, as there always are with Excel formulae. But, step-by-step, I could access the power of a pivot table … without using a pivot table at all. Just with formulae. Yes, that’s how powerful Excel’s data analysis formulae are, and MAX and MINIFS are helpful new additions in Excel 365.
The client implementation was a success, which led me to re-evaluate the role of pivot tables in my practice. Is it about time you did, too?
So, what are the downsides of pivot tables? I’ve come to resent the extra ‘junk’ that comes with a pivot table – its intrusive appearance and the fact it usually needs a whole sheet to itself. Then there’s the interface which requires a confident user to navigate: yes, slicers mitigate this to a good extent … but only with more infrastructure in the file. Then there’s pivot tables’ tendency to change size and shape without much warning …
Yes, all of the above is tolerable and, you might argue, worth it for the benefits. If you’re creating files for customers (and perhaps their customers), however – the downside is simply too much: I find it difficult these days to confidently hand over a file that runs off pivot tables since I’m not absolutely sure the average Excel user can easily use it.
The formula-based solution is lean, mean and, in my view, more controllable, as I demonstrate in the video. Why not give it a go yourself? Work along with the example in the video, and check out the link in the pinned comment on YouTube for our Data Analysis Formulae For Beginners series.
So, what do you think – are you giving up pivot tables?
