Talking to people who use Excel (but not to its fullest) I’m amused quite often when they speak about pivot tables in hushed tones as if there is some sort of mystery about them!
A pivot table is such an amazing tool that I want to outline a few pros and cons and try to set you at ease about using them.
At its simplest, a pivot table is just a way of summarising a long stream of data into a tally chart. But, of course, Excel being Excel you can go much further than that. For example, it might not just be a simple count up that you’re after but totals or averages of amounts. You can also group the data differently to the way it’s presented in your data table so that daily dated records can be grouped into monthly, quarterly, or yearly totals depending on what you’re after.
Regrouping for manager or area responsibility or remit is also a possibility. You can also create pivot charts, which are great visual summaries of the data source. Converting data to a pivot table gives you lots more reporting power and allows you to be more creative with the data.
Beforehand, if you can, you need to decide how the data is to be summarised. What field will generate each of the row labels and column labels? What field will be used for calculations; and if you need grouping of rows or columns?
Many people think that creating a pivot table is challenging and time-consuming, but once you know what you are doing, you can pull one together in under a minute. They key to creating a quick pivot table is having well-ordered data. You’ll then find that, compared with building a similar report manually, pivot tables are super quick to create.
To ensure you have good quality source data for your table, it’s best to avoid blank rows and columns, and don’t have subtotals. Give each column a unique name, and keep all column names in the same row. Ensure that every field has a value in every row. All of this will minimise problems further down the line.
You can use pivot tables to count things, such as the number of sales of a particular product, but you can also get it to show a percentage rather than a count. You might want to do this if you want to show sales of one product as a percentage of total sales. This is simple to do.
BizSmart aims to help SME and micro-business owners scale their businesses and create value through sound practical business support. We aim to give you insight and clarity and fire up your determination to succeed. You can access blogs like this and more besides through our free SmartRoom service here.