Pivot table tutorial

Last Updated: Friday, October 5, 2012 by Johnathan Briggs

Pivoting data is one of the most feared data analysis techniques by many corporate managers. Often people do not understand the fundamental concepts and therefore spend many hours staring at data and randomly clicking the pivot table box in Excel in the hope that their data will start to look the way they want.

In this post I'm going to try to explain the benefits of pivot tables and how easy it is to make them. I hope at the end of this post like me, you will learn to love pivot tables.

Why do we need pivot tables?

To make any sense of pivot tables you really need to have data that is suitable for pivoting and a reporting or dashboard goal in mind. To make this clearer I'm going to use some examples to show you what type of charts that we can produce for your KPI online dashboards from unpivoted data, and then show you how pivoting can uncover even more valuable corporate information.

I'm going to use the well-worn example of 'sales'. So firstly take a look at this table below - it stores monthly sales figures.

To make sense of this data we want to visualise it, but because the data is so basic we only have a very limited number of charting options. The most obvious charts to use would be bar or line charts.

For example we could take this data and chart it to view the trend over time.

Whilst we could vary this chart in a number of ways, fundamentally all it can show us is our 'total sales' trended over time.

Now consider a slightly more detailed set of data. This time not only do we store the total sales, we break this down into some different product categories that we have. This type and format of data is very typical of KPI dashboard data that you would get from a database.

As you can see here our sales data is now more detailed, as we have added a new column which breaks the sales down into 'product' categories. You will also note that now instead of having just one row per month we now have many rows per month, effectively having a row per month, per product category. The 'value' column is simply a summed total of the sales in that category.

At first glance this date is less readable and is a little more challenging to work with, but once you become comfortable with this format you can easily use your dashboard data visualisation tools and dashboard software to uncover key pieces of information.

If we were to apply some basic, out-of-the-box, charting to this data then we're going to end up with some categorised or distribution charts like pie charts, funnels or bars.

This type of distribution chart simply breaks the data down into its categories, in this case product type, and displays the distribution of those quantities summed over a given time period. Using Microsoft Excel or Target Dashboard you will not need to do anything to your data to produce this type of chart.

However, what we can't see any more is the trend data, so we have no idea how our product sales change over time.

One approach to solving this is to filter our data. For example, let's say that we want to see the trend over time for sales of "consultancy". To achieve this we can simply create a line chart using all of the data and then filter the data by 'product=consultancy' - we would end up with the chart looking similar to this one below.

However, what if we want to compare the 'sales' over time but show several product categories?

The approach of using a filter no longer works and we need to pivot our data.

An important concept to understand is that any data is easy to chart in your KPI dashboards if it is in the format of our first simple example i.e. our data is laid out with a row for every month and a column for every metric that we want to chart.

To reshape our data into this format we need to pivot it. The effect of pivoting is like creating a new sub table from which it is much easier to chart everything that we want. To pivot data you generally need to pivot around two columns, a text based category column and a value column. By selecting these two columns in Microsoft Excel or in Target Dashboard you can create a pivot table that builds a table making a column for every single combination of product.

Once this is done charting becomes easier again as our metrics are laid out in columns so comparing the trend over time for our sales of the different products is simply a matter of charting each of our pivoted columns.

Chart this data and the result is a nice clear chart, perfect to put on to your KPI dashboard which shows much more information than we would otherwise have seen had we not pivoted our data.

See how to pivot data in Target Dashboard

Depending on the type of dashboard software or data processing software you are using you may well be presented with other options when you pivot your data. For example, in Target Dashboard we know people struggle understanding and implementing pivots, so with just two clicks you can pivot your data and do something that in Excel takes quite a bit of time and causes confusion.

When you produce a pivot table in Target Dashboard it not only creates columns for each of your categories but it also creates a totals column and an averages column which can be very useful in the analysis of your data and for charting on your KPI dashboards.

If you want to know how to pivot your data in Microsoft Excel this is a very useful tutorial.

Also, you can download our three-page PDF information Guide to Pivot Tables here.