By popular demand we examine the pivot table, one of Excel’s most useful features. By Liam Bastick, associate director with BPM Analytical Empowerment.
Query
I work with large tables of data which I need to interrogate on a regular basis. A work colleague suggested I set up a pivot table, but I’m not sure how they work. Could you give me guidance please?
Advice
A pivot table is a semi dynamic, tabular summary of data. It is one of Excel’s most flexible tools and can give results that would take time to reconstruct with sophisticated uses of functions such as SUMIF, SUMPRODUCT, etc.
Excel 2007 has increased pivot table flexibility significantly from earlier incarnations. However, as with many pre-Excel 2007 features, the location of various tools and options needs to be re-learned. This article gives a basic overview for both Excel 2003 (and earlier) users as well as Excel 2007 users.
To describe the basics of a pivot table, I will use the attached Excel examples:
In this illustration, imagine we are a head office analyst reviewing the electrical sales of four stores – imaginatively called North, South, East and West.
Creating a pivot table on the same worksheet is fairly straightforward, albeit a little different between the Excel versions. If the database is in one block (a ‘contiguous’ range), simply select any cell within the database and then:
In Excel 2003 and earlier
- Load up the Pivot Table Wizard, Data -> PivotTable and PivotChart Report… (ALT + D + P)
- In Step 1 of 3 of the Wizard, choose ‘Microsoft Office Excel list or database’ as the data that you want to analyse and also select PivotTable as the report you want to create
- In Step 2, confirm that the entire selection of data has been identified
- In Step 3, put the report in the ‘Existing Worksheet’ and, say, choose cell L9
- The layout can be detailed by selecting the ‘Layout’ button in the wizard, but for this example we will click on ‘Finish’ instead.
In Excel 2007
- The Pivot Table Wizard does not appear to exist in Excel 2007, but if you select the keystrokes (ALT + D + P), the dialog box will indeed pop up albeit with restricted functionality
- Consequently, it is probably easier to insert a Pivot Table from the Insert tab of the Ribbon, then click on the PivotTable icon, and then select PivotTable
- The Create PivotTable dialog box appears. Choose ‘Select a table or range’ and confirm the entire selection of data has been identified
- For placement, select ‘Existing Worksheet’ and choose, say, cell L9
- Click ‘OK’ (layout cannot be constructed in a dialog box)
Notice that cell L9 is not the top left of the pivot table, as you would expect. In fact, L7 (same column, two rows higher) is the first cell for the Page Field insertion (see Figure 1, below). When selecting where to place a pivot table, always remember to select a cell two rows further down than you might otherwise think.
Figure 1: Pivot Table Construct
The pivot table has four areas where data fields may be placed (a data field is simply any one of the columns of the database – eg Date, Store, Item in this example):
- Page: Values in this field appear as page items in the pivot table
- Row: Values in this field appear as row items in the pivot table
- Column: Values in this field appear as column items in the pivot table
- Data: The field to be summarised – if this data is numerical in nature, SUM (how much) will be the default operation, otherwise COUNT (how many) is used. They are easily changed, by double clicking on the dragged field and changing SUM to AVERAGE or COUNT, etc.
Figure 2: Pivot Table Layout
More than one field can be placed in any of these locations – the effect is a hierarchical grouping depending upon the order of placement.
In essence, a pivot table is a three-dimensional summary of a database. Consider it as a book: each page has a summary table of a selection of the data (for instance, in our example, each page could show the sales of each electrical item for each store on a particular date, so Page 1 is 1 April, Page 2 is 2 April, etc.).
To populate the layout, it is simply a case of dragging and dropping. How this is done exactly depends on which version of Excel you have:
In Excel 2003 and earlier
- Once the pivot table has been inserted, the PivotTable toolbar should appear (if not, from the dropdown menu, select View -> Toolbars -> PivotTable)
- The PivotTable Field List dialog box should also appear (if not, right click on the layout and select ‘Show Field List’ from the shortcut menu)
- The fields can be dragged on to the layout using the mouse, or simply select a field in the dialog box window and then make a choice from the drop down box and click ‘Add To’
- In this example, Date is to be placed in the Page Area
In Excel 2007
- Instead of a toolbar, two additional tabs are added to the end of the Ribbon – when the pivot table is selected – Options and Design
- The PivotTable Field List window pane should also appear (if not, click on the Field List icon in the Show / Hide section of the PivotTable Tools Options tab)
Using the Field List, drag the Date field into the Page Area, the Store field into the Row Area, the Item field into the Column Area and the Amount Invoiced field into the Data Area (Values in Excel 2007), viz.
Figure 3: Example Pivot Table (1)
The power of the pivot table should already be apparent. From the humble origins of a list of data, we can now see which store is leading in sales, enquire why there are no stereo sales in the South and West and so on. Using the drop down arrows in the table (you may need to make them visible in Excel 2007, PivotTable Tools -> Options -> Field Headers icon) or in the Field List pane in Excel 2007, we can filter for certain selections – for example, North’s sales on 2 April:
Figure 4: Example Pivot Table (2)
But let’s not stop there. Resetting the pivot table, one key report would be to determine the amounts outstanding. To do this, we need to construct a calculated field.
In Excel 2003 and earlier
- In the PivotTable toolbar, select PivotTable -> Formulas -> Calculated Field… (ALT + P + M + F)
- Type ‘Amount Outstanding’ in the Name Field (note it can be modified later)
- Clear the Formula box and type ‘=’ then select ‘Amount Invoiced’ from the Fields window and then click ‘Insert Field’
- Type ‘-’ then select ‘Amount Paid’ from the Fields window and then click ‘Insert Field’
In Excel 2007
- In the Ribbon, select PivotTable Tools, Options tab, then click on the Formulas icon in the Tools section and click on Calculated Field
- Type ‘Amount Outstanding’ in the Name Field (note it can be modified later)
- Clear the Formula box and type ‘=’ then select ‘Amount Invoiced’ from the Fields window and then click ‘Insert Field’
- Type ‘-’ then select ‘Amount Paid’ from the Fields window and then click ‘Insert Field’
Figure 5: Insert Calculated Field Dialog Box
- Click the ‘Add’ button and then ‘OK’.
As a consequence of doing this, Excel may ask whether you want the destination cells to be replaced (the software thinks you want to replace or add to the Data Field). Rather than worry about choosing ‘Yes’ or ‘No’ (obviously, choose one!), simply go to the Field List and de-select the Amount Invoiced field (or drag it off the layout from the top left hand corner) and choose the ‘Amount Outstanding’ calculated field instead:
Figure 6: Example Pivot Table (3)
One more thing we’ll do while we’re here: the business has a North West division and a South East division. To see how these divisions have performed, we need to group the stores:
- Highlight the North and West rows of the table (hold the CTRL key down to select non-contiguous ranges)
- Right click and select ‘Group…’ in Excel 2007 or ‘Group and Show Detail’ -> ‘Group’ in earlier versions
- The items have been grouped and we have a new, second data field in the Row Area, ‘Store2’ and a ‘Group 1’ grouping:
Figure 7: Example Pivot Table (4)
- Right clicking on ‘Store2’, we can click on ‘Field Settings…’ and change the name in the dialog box to ‘Division’ and click ‘OK’
- Left click on the cell containing ‘Group1’
- Go to the Formula bar and change the label to ‘North West Division’
- Repeat the process for the South East Division
Figure 8: Example Pivot Table (5)
- This can be collapsed by double clicking on the cell containing the grouping description in all versions or selecting the ‘+’ or ‘-’ symbol in Excel 2007:
Figure 9: Example Pivot Table (6)
Just an introduction…
This article was intended only as an introduction to pivot tables – there is plenty more you can do with them.
The Pivot Table options are well worth playing with (Excel 2007, on the Ribbon. Earlier versions: from the PivotTable toolbar, choose PivotTable -> Table Options…). It doesn’t take long to spot:
- how to give the pivot table a name
- adding / removing totals
- preserving formatting
- merge labels
- Data can be brought in from more than one source
- The idea can be extended to pivot charts
- Excel 2007 allows even greater functionality, such as 64 levels of sorting rather than 3, more rows and columns and colouring the pivot table more prettily.
Handle with care
Earlier I mentioned that pivot tables were semi dynamic. But they do not automatically update. That is, if you change the source data, the data in the pivot table does not automatically recalculate (F9 doesn’t work here). In earlier versions of Excel, if you have more than one pivot table, each pivot table has to be selected and then the refresh icon clicked on the PivotTable toolbar 
, or PivotTable -> Refresh Data. This is quite cumbersome. However, in Excel 2007, you can refresh them all (in the Ribbon, on the PivotTable Tools Options tab, select the Refresh icon in the ‘Data’ section and choose ‘Refresh All’).
Pivot Tables can play havoc with underlying worksheet formats (for example, the underlying grey background keeps disappearing)
The Calculated Fields calculations do not always work as intended, especially with ‘*’ and ‘/’: it is a case of suck it and see
Get it right though and a pivot table can provide useful analyses that would take half a day to write as a formula (and about the same time for Excel to calculate…).
Look out for more on pivot tables in future editions.
