Analysing data with Excel Pivot Tables

In the past couple of tips, we’ve looked at how to create Tables & use the tools that then become available (such as Filters and Slicers) to begin to extract information from the data that we hold. We’re now going to look at how to take this much further by looking at Pivot Tables.

What is a Pivot Table?

As the lovely people at Microsoft put it: A PivotTable is a powerful tool to calculate, summarize, and analyse data that lets you see comparisons, patterns, and trends in your data.

The great thing about them is that, whilst they’re massively powerful & therefore you’d expect them to be really complicated to create, they’re actually quite easy to set up initially

Creating a Pivot Table

It makes sense to have your data already set up in a table before creating a Pivot Table from it. The reason for this is simple, because your table will have a name & the range that this name refers to will automatically expand as you add more rows and columns, if your Pivot Table is linked to it, then it will be able to reflect those changes at the click of a button, you’ll not need to re-connect to the table (or risk having a massive workbook by linking your Pivot Table to full column ranges)

So, using the same data as we looked at when we were creating Tables and Slicers, from a single cell within the table, we select the Table Tools>Design Tab & from that, Summarize with Pivot Table

This takes us to the Pivot Table Wizard which has the table’s name pre-populated & is prompting you to insert the Pivot Table in a new sheet

It’s very rare that I make different selections to these, so now, clicking the OK button, we have…

  1. The Pivot Table has been created for us
  2. The fields that we can use to do our analysis in the Pivot Table are ready to use (the fields are the headings from our original table)
  3. A new PivotTable Tools toolbar has appeared
  4. With the Analyse sub-tab being automatically selected.

I’m using the latest version of Excel within Office 365; if you’re using an earlier version, then whilst the process will be the same, the toolbars are slightly different… here’s how it looks in Excel 2013

And in Excel 2010…

Populating the Pivot Table with data

We now need to work with the Pivot Table Fields TaskPane over to the right-hand side of our screen (we can move & resize it if needed)

As shown above, to populate the Pivot table, you simply drag the field you want into the area you’d like to see it… so at it’s simplest, it could be…

So here, I’ve simply dragged the total sales field into the Values part of the grid & now my Pivot Table shows the total sales value of all of my data. Because Excel recognises that there are only numerical values in my data, it automatically uses the SUM function

I might, however, require additional analysis… for example by branch & by salesperson, so using the rows & columns parts of the grid, we can quickly get to…

… and the typing of the explanation took far longer than the actual process! And remember, we’re analysing over 18,000 rows of data in this Pivot Table!

Of course, we could have had both sets of data in rows…

… or in columns…

At the end of the day, it’s important to select a view that helps illustrate the point that you’re trying to make & as compactly as possible, so having one set of data in Rows & the other in columns (as we initially had, probably makes most sense with the field that has most entries going down the Pivot Table…

Adding more data to your Pivot Table

Once you’ve started analysing your data, you’ll begin to realise what opportunities there are to really dig down & you might wish to add in additional information.

To do this, in the first instance, simply bring the data into either the rows or columns of your Pivot Table. As you do this, in order to see the data, you may need to reorganise which quadrant of the grid you ned to use…

So, in the example above, we’ve moved the salesperson to the columns, the store name to the rows & then added the category below that (which shows the source of the sale)

At the moment, we’ve not made use of the Filters quadrant, this allows you to add an additional layer, for example filtering by Product Code or by Year (or both). When you do this, initially you’re presented with “all” & then you select the items that you want to see..

Selecting the dropdown provides the opportunity to filter how you want…

However, when multiple items are selected, it’s not easy (without reselecting the dropdown) which items are being filtered

So, rather than using the Filter Quadrant, I now use Slicers as these are far more visual. Below, we have the same information, but it’s clear from the shaded items in the Slicers which items have been filtered

If you’re using Slicers, you don’t need to have anything in the Filters quadrant for them to work…

You can also choose to have additional data in the Values quadrant…

In this case, I’ve included a text field (Category) so Excel changes the summation method automatically to “Count”. Occasionally it will do this if you have numerical data but with some blank values (or even incorrectly input data), in this case you can use the little arrow to the right of the entry to change the summation value; this is really useful if, in addition to the (e.g.) total sales value, you also want to see average sales

Changing the summation method

First, I drag the Total Sales field in a second time

Now, I can change the summation method to Average by selecting Value Field Settings…

And then changing the summation method…

I can also use this option to change the name that I see by giving it a Custom Name, the key thing to remember is that it must be different any existing field name

Making it look pretty

As you can see from the above, at the moment there’s no consistency with the numbers; some have no decimal places, others 8, so first of all, let’s sort those out…

Starting with the Value Field Settings of the Sum of Total Sales, first…

We can access the standard Number Format options & I’ve chosen Number Format, no decimal places & a comma separator; I’ve then repeated that for the Average values for a much cleaner look…

The next thing that we could do is, from the PivotTable>Design Tab…

… choose to apply a PivotTable Style