Learning to use Pivot Tables
I don’t mind admitting I used to avoid using Pivot Tables years ago. In my busy corporate jobs, I found every time I tried them there was some problem, I just didn’t get them to work the way I wanted in the 2 minute timeframe I had unfairly given them to work. Plus, they seemed to count things by default not sum things and the formatting was rubbish, what are these caches, calculated fields vs calculated items how confusing was all that – and if you did fix the formatting, it didn’t stick.
In a way it was a blessing, as it meant I became really strong in complex formulas SUMIF, SUMIFS, SUMPRODUCT, INDEX you name it. I didn’t see other people in the large companies using them, except I remember when working for BHP in Singapore my wife worked at the National Dental Centre there and someone she knew in finance used them day in, day out. That got me thinking am I missing out on something here? So I few years back now, I made a conscious decision that I need to get into using these things. I have found incorporating them into my solutions improved my financial modelling quality exponentially. Plus I wasn’t burning all my energy on unnecessary, complex formulas. I have done consulting jobs now where the Pivot Table output and reports are the primary focus. In fact, on a few occasions they have been my saviour – see my ‘war story’ on using Pivot Tables in my own Dental Practice.
Introduced way back in Excel 2000, I think it’s fair to say Pivot Tables are still not widely embraced. Yet this (ok sometimes frustrating) tool can be incredibly powerful for analysts or anyone working with data to make sense of raw data or thousands of transactions. Using Pivot Tables teaches you how to best organize your data that and then gives you the capability to summarize, filter (slice & dice) and visualize your data in Pivot Tables and the Pivot Charts they spawn quickly. You also have the ability to re-use that data for other tasks you haven’t thought of yet with more Pivot Tables.
For me, the breakthrough in using them was simple in the end. Pivot Tables demand some basic order to your data. You have to ensure your data is in a database format and each column heading has to be unique and static i.e. not the result of a formula. That’s it, not that hard. Take what I call a ‘data approach‘ to your spreadsheets always. So this means transactions in rows down the page and columns for each dimension of that data. So say it’s a contract administration summary you need – each contracts goes down the page and across the page are customer, market segment, contract number, contract year, delivery from, delivery to, tonnage, product, price etc. I was an accountant who started using spreadsheets when spreadsheets first started so tended to have data in the final report format eg. months across the page and report elements down the page for a long time. My advice to anyone like this is get out of that habit. Transactions (ideally not summarised but in the granularity that you need to report) go down the page in rows and across the page in columns the different dimensions of the transactions such as date, value, reference, customer etc Then let the Pivot Table do it’s magic in filtering, summing or detailing that you need. If you start setting up every workbook in a database format and also separating data from intermediate calculations or manipulation and this in turn being separated from reports, you will take your spreadsheeting to whole new stratosphere!
I am not going to attempt to explain the nitty gritty on how you use Pivot Tables – there are millions of posts, videos, books and articles on this. But I hope I inspire you to start mastering Pivot Tables and guide you on where to start…
Two fantastic resources for videos, posts and articles on Pivot Tables I highly recommend are:
- www.contextures.com – MVP Debra Dalgleish amongst all other things Excel Debra has a lot of fantastic Pivot Table content plus a couple of books that is a great way to get started or stronger on this topic
- You Tube channel Excel is Fun Mike Girvan has an incredible (actually mind boggling) amount of fantastic Excel videos for beginner, intermediate and advanced Excel users and so search for Pivot Tables. I love his videos – there is a joy in his delivery that is contagious.
Then just start using them and persevere.
Now a final tip. Once you start using them you will see how to set the data source Pivot Table tools Analyse \ Change Data Souce. But if that data source is static you will have to remember to amend it for when data changes – perhaps someone has added some extra columns in the data or there are more rows than before. In the past I would simply add more rows at least beyond what I thought I would ever need. But the best way is to make your data a Table and define this as your data source for your Pivot Table. It will then shrink and grow as your data changes!
So Pivot Tables are not so bad. Actually the Pivot Table is your friend!
Once my spreadsheets were in this format everything was easier and suddenly pivot tables were making sense and becoming useful.