Hi everybody and welcome to this week’s blog coming to you from sunny Aberystwyth!!
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 thought that in this week’s blog I would 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 PivotCharts 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.
There are a few downers, the main one being that if you amend or append records in your source data table the pivot table is not dynamic and has to be refreshed (recalculated) to show the changes.
3 Golden Rules
Keep to these 3 Golden Rules before converting a raw Data List to an Excel Pivot Table. Starting with the source Data Table:
Format the Top Row of the list as Excel will usually recognise it as a Heading Row and so the column headings will become field names in the Pivot Table.
NO Blank rows (columns) in the DataBase
(Else it will prevent the whole list from being picked up for the new report.)
Select ONE CELL inside the DataBase List so that Excel creates the Pivot Table properly