This is the first part of a series that will cover all aspects of PivotTables, from their use as a simple way of summarising data, to using PivotTables as the engine for the creation of an interactive dashboard.
In this first part of the series we will be concentrating on why rather than how and seeking to show that PivotTables can often be the quickest and simplest way to solve common Excel problems.
This part of the series is intended for those Excel users that haven't yet found a use for PivotTables or have experimented with PivotTables and been disappointed. We will go on to cover areas of more interest to experienced users of PivotTables in future parts of the series.
Recalculation and Refresh
One of the first points we covered when looking at the use of Excel power tools was the issue of immediate updating. There is a difference between how Excel formula cells react to changes and how PivotTables react.
The Excel calculation mode is usually set to automatic which ensures that Excel will immediately recalculate all formulae that depend, directly or indirectly, on the value changed. PivotTables do not automatically recalculate when the data that they are based on changes. Instead, you need to refresh the PivotTable to ensure it reflects the latest data values. Depending on how your source data is set up, you might also need to change the data source to include any new rows or columns that are added.
PivotTables based on certain types of data source can be set up to refresh automatically after a certain time interval, but even this is not the same as recalculating whenever the underlying data changes.
Complexity is optional
There is a perception that PivotTables are complex. Although there are indeed many complex features available when working with PivotTables, you can ignore all the complexity and just use PivotTables as the simplest way of creating subtotals and summaries.
This is the way in which we are about to use a PivotTable to create a top ten league table of average house price sales values in England, Scotland and Wales using just 17 mouse clicks, and without touching the keyboard.
We will be using data publicly available at Gov.uk.
We are going to download the first CSV (comma separated value) file in the list - Average price.csv - and download it straight into an Excel workbook:
Here is our data in Excel:
As you can see, for the earliest dates, not all figures are available. Column C contains the average price and figures are published monthly.
Create the PivotTable
First, we need to turn our data into a PivotTable. Because there are no blank rows in the data, we can leave the cursor on cell A1 and choose Insert Ribbon tab (1), Tables group, PivotTable (2):
Since there are no blank rows (or completely blank columns) in our data, Excel will correctly work out the range of data that we want to use. In this case: $A$1:$F$110311. We want to create our PivotTable on a new worksheet so we don't need to change the default location and just need to click on OK (3).
Add the fields
Excel displays an empty PivotTable on the new worksheet and opens a pane on the right-hand side of the screen showing the PivotTable Fields together with a schematic of the four PivotTable areas.
Our fields are our different columns of data and the headings in row one are used as the names of these fields. Initially, we don't have to worry too much about the different PivotTable areas, but instead we can concentrate on the information that we want our PivotTable to display.
We want to find the 10 most expensive regions by average price. So we just click the Region_Name checkbox (4) and the Average_Price check box (5):
Because the Average_Price column/field contains only values, Excel assumes that we want to summarise the field and automatically adds it to the Values area. Because Region_Name includes text entries, Excel treats this as a 'label' and adds it to the Rows area.
After just five mouse clicks and not having had to touch the keyboard at all, we have created a summary of over 110,000 rows of data by region.
Wonderful as our summary might be, the current number format makes it difficult to interpret the figures. We can change this by right-clicking in any of the summary value cells (6) choosing the Number Format option (7) and then choosing an appropriate number format (8,9,10):
A better number format, leaving out the pence and including thousand separators, makes our figures clearer, but they currently show the sum of all the averages for all the months. It would probably make more sense to display this as an average. Again, we right-click on any of the values (11) and this time choose Summarize Values By, Average (12):
Sort by value
Next we want to sort our PivotTable by value rather than showing it in alphabetical order by region. Just for a change, we can right-click on any value (13) and this time choose Sort, Sort Largest to Smallest (14):
Finally, we will turn the PivotTable into our top 10 league table. This time, we right-click in a region cell (15) and choose Filter, Top 10 (16):
If we want to display the top 10 largest items we can accept the defaults for the filter screen and just click OK (17) to create our league table:
Few surprises there then…
The intention of part one was just to show the sort of thing that you can do using a PivotTable and to show how a simple PivotTable can be very straightforward to create.
As a taste of what we will be covering in future episodes, we will go on to use our date to create a line chart showing the change in house price values over time, by country.
To do this we have added the Date field to the Rows area (where it will be automatically grouped in the latest versions of Excel, or can be grouped manually in earlier versions by right-clicking on any date value within the PivotTable and choose Group).
We have dragged the Region_Name field from the Rows area to the columns area and used the Filter and Sort dropdown in the Column Labels header to just choose England, Scotland and Wales.
With any cell in our PivotTable selected, we then use PivotTables Tools, Analyze Ribbon tab, PivotChart to turn our PivotTable into whatever chart type we choose, in our case a simple line chart:
Same time, same place…
In the next part of the series we will be looking at the importance of the way in which underlying data is structured and also examining in much more detail the four different PivotTable areas and how they can be used to help you answer key questions about your data.