Pivot Tables in Microsoft Excel
I told my wife that I wanted to write a blog post about pivot tables in Microsoft Excel (despite the odds, wanting to write a blog post about pivot tables in Microsoft Excel and being married are not mutually exclusive), and that I was really struggling with how best to frame it. Not enough good can be said about pivot tables, but my impression is that they are viewed as complicated, ‘techy’ things. My wife, in her sagacity, told me that I should focus on addressing 6 questions : who, what, when, where, why, and how, like she was taught in primary school. So I’ll try and do that.
Who?
Anyone who uses Excel could benefit from the use of pivot tables. I would go so far as to say that any business which utilises Excel but doesn’t use pivot tables, is not making best use of the software.
What?
Pivot tables take your data and present it clearly and concisely in a familiar Excel table format, while allowing the results to be manipulated easily. It provides ‘headline’ information about your data, without affecting the data itself. If, for example, you log all of your expenditure in one spreadsheet with a date for each item, wouldn’t it be useful to automatically keep track of the total expenditure for the individual months? Or by type of expenditure? Pivot tables can do this and more.
When?
Pivot tables help to track information, and to give a neat summary of large data sets at a glance. Very useful for a snapshot of where your project is at, how much you have spent, who the top salesman is, or preparing a report or demonstration for someone unfamiliar with the information in your spreadsheet.
Where?
Although I think pivot tables are usually regarded as a work-place accounting tool for budget monitoring, they can be useful for almost any ‘like for like’ data that is stored in a spreadsheet. For example, if the results of football matches are logged in Excel, a pivot table could be used to track the number of goals scored by the individual teams over a season.
Why?
The idea to write a blogpost about pivot tables grew out of my complete inability to articulate what a ‘bespoke spreadsheet’ is, and why a business would want one, but in my experience many organisations benefit greatly from spreadsheets that are more tailored to their needs, and I have received more (undeserved) gratitude for spreadsheets that just ‘do what you need’ than for anything else I’ve done. This almost always involves pivot tables, and doesn’t necessarily mean any fancy backstage programming in VBA, or colourful charts or user-forms or graphics. Often it is just something as neat and helpful as a pivot table. Say what you like about Bill Gates (but say it quietly, because he’s listening), Microsoft Excel is a wonderful piece of intuitive software, and I believe that rather like that slightly confusing but oft quoted remark about the human brain, without pivot tables we are barely using 10% of Excel’s full capacity.
How?
I’m convinced that pivot tables are a victim of their reputation – insofar as it’s possible for a function of some computer software to have a reputation to be a victim of – and that most Excel users could learn how to use them effectively with just a minimum of coaching. I found this neat video on youtube which explains how to create a pivot table in around 2 minutes, with nothing more complicated than pointing and clicking, dragging and dropping:
I would encourage anyone who is a regular user of Excel to experiment with creating pivot tables, you can always just delete them if they don’t work out (they won’t affect your original data, but if you are unsure I’d recommend experimenting with a copy of your file at first).
A wonderful Eddie Izzard quote: “I don’t have techno-fear, I have techno-JOY!”. Me too, Eddie.
