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 
 
 
 

Pivot Table Example 

How to turn a Data List into a Pivot Table 
Beforehand, if you can, decide how the data is to be summarised. 
What field will generate each of the Row labels; Column labels; What field will be used for calculations; and if you need grouping of rows or columns 
Make sure the Data List adheres to the 3 Golden Rules above then click on ONE Cell in list 
By the way, if your data list has previously been converted to an Excel Table there is a massive benefit: your data range becomes "dynamic" and so it will automatically expand and shrink as you add or remove data meaning that it will always contain the latest data. 
Take a look at an earlier blog for information upon Excel Tables especially make sure that you NAME the table ... 
Click Insert tab, Pivot Table (Report) and on the dialog that appears, check the target cell range (reselect if necessary) and where the new table is to live then click OK 
If you want, work in Pivot Table Classic View
Either right click Click Pivot Table Tools, Pivot Table Options, Options, click Display tab and tick Classic Pivot Table Layout 
Either drag the fields from the Field List onto the intended “hot spots” on the Field List Pane or onto the Pivot Table in Classic View 
 

Classic View & Pivot Table Field List 

Changing your mind about the PivotTable design 
You can drag and drop a field to a new position on the table or field list pane 
You can remove a field - drag and drop the field outside the table or field list pane 
You can overtype some of the titles Excel makes by default try not to use an actual field names otherwise Excel will complain eg if Excel puts Sum of Sales in a cell then you might want to change it to Sales Totals 
If you want to change the presentation then experiment with the formatting options available on the PivotTable Tools, Design tab 
 
Refreshing a PivotTable 
Remember changes in the source data won’t show immediately in the pivot table - they are not dynamic and need to be refreshed (recalculated) to show the changes 
Go to the data table and make your amendments 
Return to the Pivot Table, click one cell in the Pivot Table then click the PivotTable Tools Options/Analyse tab, Refresh (or RH click …) 
 
Grouping Data in a PivotTable 
Select columns / rows with the intention of grouping them under a new summarising heading (Via thick black down arrow) 
Click PivotTable Tools Options tab, Group Selection (or RH click the selected area on the pivot table and take Group option) 
Change the Group temporary title to suit the data 
For date fields you might want to group to give monthly, quarterly, or yearly totals (Eg click Month AND Year on the dialog) 
To remove grouping, select all columns / rows in the group then click the Ungroup button (or RH click selection …) 
 
To see some worked table examples Download my Excel Pivot Table Example file - you will need to look at cell contents to discover more ... 
 
So why don't you set up your own pivotal moments? 
Have fun using Pivot Tables and experiment!! 
 
See you next time. 
Les is more! 
 
 
 

Pivot Tables Tips 

Tip 
What to do and why?? 
PivotTables based on ExcelTables 
Using an Excel Table as source data for a Pivot Table has a massive advantage as any new rows/columns added to the table will automatically be included in the pivot table. 
Click the list and press Ctrl+T ... 
PivotCharts 
PivotCharts are great visual summaries of the data in the Data Source or PivotTable.  
Build them just like ordinary charts, choosing the best chart type, layout, and style for the data 
Click 1 cell in table then click Insert tab, PivotChart etc ... 
Filters 
To focus on certain subsets of your data, use Filters to narrow data in the PivotTable. 
Drag a field from the Field List to the Filters “hot spot” on the Field List Pane or onto the Pivot Table in Classic View 
Slicers 
Slicers are easier and faster filters. 
Click 1 cell in the PivotTable then click PivotCharts Tools, Analyze tab, then Insert Slicer. 
Select the desired field on the next dialog then click OK. 
The slicer appears next to the PivotTable, any selected blue items will be reflected as a change in the PivotTable. 
Experiment! 
Keystoke 
Effect 
Ctrl+T 
Convert a raw Data List to an Excel Table 
Ctrl+A 
Select the table - click on ONE Cell in table first 
Share this post:

Leave a comment: