This is a general post for anyone out there not using Pivot Tables. Do yourself a favor, you need to! I am also going to do a similar post shortly for anyone not using Tables as well. I think both tools are just so very powerful and if you want to be an advanced user and more efficient in your job, you simply need to make a conscious effort to start using them.
Essentially Tables allow you to better handle the simple fact that in the real world, business is dynamic and so formulas for Tables because they refer to the Table column (called Structured Formulas) not a cell and are more robust to a changing number of data rows. I don’t want to digress to Tables too much in this post, but I love structured formulas because I can get the autocomplete to show me what columns are available in a table to use in my formula and not have to remember column letters from some far flung place in the spreadsheet.
So Pivot Tables and Pivot Charts also handle changing rows of data and also allow for easier (and instant) summarizing, filtering, reporting and slicing and dicing of data that any good analyst needs to be fast and accurate with. Both tools save time and allow you to be more efficient so there is more time for the analyzing, interpreting and finding insights. Plus, if you want to be able to use the latest BI tools such as Power Query and Power Pivot, guess what the output of these tools is, yep Pivot Tables (Power Pivot) and Tables (Power Query). So these tools are not a passing fad.
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 time frame 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 savior – 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 the data data they draw from. 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 e.g. 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 summarized 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 spreadsheets 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 Analyze \ Change Data Source. 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. Tables are dynamic, so will then shrink and grow as your data changes!
Once my spreadsheets were in this format most times everything was easier and suddenly pivot tables were making sense and becoming useful.
Pivot Tables are not so bad to get used to. In fact the Pivot Table eventually becomes your best friend and can be a life saver