Save content
Have you found this content useful? Use the button above to save it to your profile.
Excel
iStock_G0d4ather_Excel

EZ guide to PivotTables – Why PivotTables?

by
13th Jan 2017
Save content
Have you found this content useful? Use the button above to save it to your profile.

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.

Objective

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.

The data

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:

Simon Hurst

Here is our data in Excel:

Simon Hurst

As you can see, for the earliest dates, not all figures are available. Column C contains the average price and figures are published monthly.

(17) clicks

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):

Simon Hurst

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):

Simon Hurst

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.

Number format

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):

Simon Hurst

Summary type

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):

Simon Hurst

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):

Simon Hurst

Top 10

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):

Simon Hurst

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:

Simon Hurst

Few surprises there then…

Conclusion

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:

Simon Hurst

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.

 

Tags:

Replies (17)

Please login or register to join the discussion.

avatar
By KABcraigmore
13th Jan 2017 16:26

That was clear as mud.

Incidentally, my "mouse clicks " are carried out on the key board so bit of vapid claim to be able to do this without touching the keyboard

In benefits of pivot tables you said they don't update immediately like ordinary spread sheets. Thats a bit of a drawback surely. I want to monitor my spread sheet is working as I want it to and to follow the result of changes to underlying data.

"Insert Ribbon tab" I have no idea what this means. There's no button for it

"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." I think County?

Perhaps it will all mean something if I actually try to do what you demonstrate. Actually I expect I will get horrbly muddled.

Definitely not convinced of any benefit in "pivot tables" yet

Thanks (0)
Replying to KABcraigmore:
Simon Hurst
By Simon Hurst
13th Jan 2017 17:10

KABcraigmore: thanks for your comments. Of course, there are many substances more opaque than mud so it could be a stage on the way to enlightenment. I used mouse clicks to try and demonstrate the simplicity of this particular project as they require no prior knowledge. You can look at the screen and find what you need rather than needing to know what it is you need to type on the keyboard.

I agree that PivotTables and many other related features in Excel require a different way of thinking and working, which is why I stressed this point at the beginning. It's just a different way of thinking about how your spreadsheet works that most people get used to relatively quickly, but you are absolutely right that for some people it rules out all the other benefits of PivotTables.

You make an excellent point regarding the Insert Ribbon tab: I have stopped including references to versions of Excel prior to Excel 2007 so anyone using Excel 2003 or earlier will not be able to follow the instructions step-by-step.

In this post-Brexit world regional sensibilities are heightened enough without describing Wales and Scotland as counties...

Thanks (2)
Replying to shurst:
avatar
By frankscotreid
13th Jan 2017 17:50

KAB should get a life.
I'm sure if one was serious about learning this you would use the actual data as an exercise then much of it would I'm sure become clear. I regard myself as a fairly clued up Excel user but Pivot tables have so far remained slightly mysterious. I think choosing an example with loads of data clearly demonstrates how useful Pivot tables can be so thanks for that.

Thanks (1)
Replying to frankscotreid:
Simon Hurst
By Simon Hurst
13th Jan 2017 18:46

Well, not every Excel user needs to use PivotTables, but there are probably lots who don't that would find them useful. I'm glad the large data set helped, but PivotTables can also be very useful for much smaller data sets. As an example, one of my support clients wanted to summarise a few dozen bank transactions by month. A PivotTable with the date field grouped by month proved to be a quick and easy answer.

Thanks (0)
avatar
By JAISRIRAM
13th Jan 2017 16:57

The example is good to start. That was very simple to use.tks

Thanks (1)
Replying to JAISRIRAM:
Simon Hurst
By Simon Hurst
13th Jan 2017 17:11

Many thanks - glad someone found it useful.

Thanks (1)
avatar
By Anita14
14th Jan 2017 08:35

The explanations and method were pitched just right - reinforced what I'd already stumbled through myself and gave a flavour of what more can be achieved. Really smart to use a dataset that we can all access and try out. I look forward to the next instalment. Thanks.

Thanks (1)
Replying to Anita14:
Simon Hurst
By Simon Hurst
14th Jan 2017 13:58

Thanks Anita - I'm glad you found it useful. It took me a while to find a suitable public dataset, so I'm pleased that it proved to be worthwhile.

Thanks (0)
avatar
By maxmillion
15th Jan 2017 16:15

Thank you Simon for your helpful working and practical example. I look forward to learning more about pivot tables in your subsequent discussions. I particular liked your step by step approach with lots of screen prints.

Thanks (1)
Replying to maxmillion:
Simon Hurst
By Simon Hurst
16th Jan 2017 08:58

Thanks maxmillion - I hope you will continue to find the series useful.

Thanks (0)
avatar
By Lichinga
16th Jan 2017 09:55

I found this really helpful, taking my limited knowledge of pivot tables to the next level. I look forward to future instalments.

Thanks (1)
Simon Hurst
By Simon Hurst
04th May 2018 09:47

PivotTables - Excel's Marmite

Thanks for all the comments - supportive and critical. The different reactions seem to confirm my long-held belief that PivotTabls are like Marmite - you either love them or loathe them.

If you've never understood the appeal of PivotTables it might be worth sticking with the series for an episode or two more - at the worst it might just confirm that PivotTables are not helpful for the sort of things you use Excel for.

Thanks (0)
avatar
By DMBAcc
19th Jan 2017 09:14

Thank you Simon for this. I do not use pivot table but a colleague and my son both insist I should get a grasp so more of the same please. I am more interested at the moment in WHY I should use them rather than how but HOW will be important. Again many thanks

Thanks (0)
avatar
By DavidMartin16
15th Feb 2017 12:55

Hello Simon, I have attended your ICAEW courses in the past & the AccountingWeb articles are helpful thank you. Occasionally I get caught out with following examples, I use Excel version 2010, & all was well until the Filter & Sort dropdown to select England, Scotland & Wales as groupings (could see how to filter specific counties)- could not see how to do that...is it a feature of later excel versions? Many thanks, David

Thanks (0)
Replying to DavidMartin16:
Simon Hurst
By Simon Hurst
15th Feb 2017 13:19

Hi David - thanks for coming to the courses and reading the articles. The England, Scotland and Wales 'regions' should be included in the Region_Name dropdown in alphabetical order with all the counties. It's slightly odd, because you would, as you say, expect this to be an additional grouping level rather than just an item within Region_Name. Please let me know if this doesn't resolve the issue.

Thanks (0)
avatar
By swiftman
20th Mar 2017 10:40

I'm a long term "simple" Excel user and haven't tackled pivot tables. Although this is a useful sets of mouse clicks to see "an example" of a pivot table, from the heading "Why PivotTables?" I was looking forward to being given a few examples of "Why PivotTables?".

I think there are probably a lot of users like me who would make the effort to use pivot table IF we knew that was the best approach, but because they seem so intimidating, I think we don't even start. I know I don't.

So please could you try to describe some situations when we should consider "Why PivotTables?" beyond this one example. And perhaps this article could point out that the Top 10 figures arrived at when using this particular data set aren't of any great significance, they just from a handy, publicly available data set and so useful to go through the motions described in this Part I.

Thanks (0)
Replying to swiftman:
Simon Hurst
By Simon Hurst
20th Mar 2017 11:33

Thanks for your comment Swiftman.

The intention of the article was to demonstrate that, if you are faced with a set of values, categorised in any way, PivotTables provide a simple and straightforward way to summarise the data without needing to use any Excel formulae or functions.

Because PivotTables allow you to summarise and present data from any structured set of data, the underlying assumption is that you have some sort of coherent list or table of data that contains too many values to allow you to assess the significance of different aspects of the data without summarising it in some way. To give some other practical business examples, using a PivotTable with a list of sales invoices might allow you to see:

Which customers spend the most with you
Which products generate the most revenue
How seasonal your sales are
The trend of overall sales over time
The trend of particular customers' sales over time
The trend of particular product sales over time
Which products particular customers buy
Which products are popular in which regions
Which month generated your highest sales last year
Which product's revenue is declining most rapidly

For those interested in such things, this use of PivotTables can form the calculation 'engine' behind creating an interactive dashboard to help make key management decisions, but PivotTables are equally useful in just creating quick summaries to explain data.

You could extend these ideas to any set of data, from any set of business values or transactions, through to lists of the music tracks you have downloaded or ripped, or your fantasy football league players' records.

I think the problem might be, as you say, that people see PivotTables as intimidating. That was why this first article in the series tried to show just how easy it is to summarise a set of data and turn it into something meaningful.

I'm sorry if the top ten highest regional house sales value was not of any significance to you, but hopefully you can extrapolate the idea to data that is of greater personal relevance. If you have any data of your own that you'd be happy to make publicly available I would be delighted to try and demonstrate a practical use of PivotTables that would be relevant to you.

In any case, hopefully the other articles in the series will demonstrate many of the ways that PivotTables can be useful:

https://www.accountingweb.co.uk/tech/excel/ez-guide-to-pivottables-2-fro...

https://www.accountingweb.co.uk/tech/excel/ez-guide-to-pivottables-slice...

Thanks (0)