What are Excel PIVOT tables and how to create one

If you have struggled with PIVOT tables in the past, or if you have never seen how they can be used then read on to gain a good understanding of the basic concepts of an Excel PIVOT table…





What is a PIVOT Table?

A PIVOT table in Excel is a tool that allows you to summarise your dataset into a more appealing, user-friendly, and importantly interactive view.

In simple terms this means you can take a large dataset, for example a sales transaction dataset, and quickly turn it into a table which summarises the sales by date, staff member or any other category, or combination of categories, that is held in your original dataset.

So this….

Can look like this:

The quick summarisation of data is a key feature of the PIVOT table but the fact it turns your raw data into something that is more meaningful is another huge benefit.  For example 1,000 sales transactions line by line will not mean much to anyone but if you use a PIVOT table to display the amount of sales by month or name then we can start to analyse the data more effectively.

Finally the interactive function is a powerful feature of a PIVOT table.  Interactive in this case means that while summarising the data the raw data remains behind the scenes, so if you summarise the sales by staff member and you then want to see all the individual transactions that make up the summary total a simple double click will return all of those transactions on a fresh worksheet for you.

When should you use a PIVOT Table?

A PIVOT table works for any situation where you have a dataset that needs summarising, especially if you need to summarise the data quickly.

The PIVOT table will work for all sizes of datasets although on the very large datasets you can sometimes deal with in the work-place, i.e. 100,000+ records you may find you run into file size issues.  This is predominately an issue if you need to email files around and is because in order for a PIVOT table to remain interactive the raw data needs to stay in the file, hence increasing the size.

How to create your first PIVOT table

Creating a PIVOT table is easy and it’s best to jump straight in and create one, we can then look at the key features in more detail.  To follow along with the example you can download the sample dataset used here.

Step one – Decide on what you are summarising.

The first step is to decide what you are trying to summarise from your dataset.  This could be from a management request or if you are just analysing data to produce a report then the best tactic is to eyeball the data and use your common-sense.  Fields that group together, like dates, names, teams are the most common ways to summarise your data, basically anything that will group together nicely.

In this example we will produce a summary of sales by staff member.

Step Two – Define the PIVOT table dataset area and insert a PIVOT table

To start with navigate to your dataset and then in the Ribbon click on

1)      INSERT

2)      PivotTable, then

3)      PivotTable

This will bring up the “Create PivotTable” box like below:

The first step is to make sure that the Table/Range (highlighted in the above image) is correctly picking up all our data.

The next step is to decide where you want to put the PIVOT table, as standard “New Worksheet” will be selected so we’ll work with that for now but if you want to specify a different worksheet just change the radio button by selecting the one below for “Existing Worksheet” then specify the location.

When done click OK and you will be faced with a new worksheet that looks like this:

Let’s review this sheet in more detail as it is useful to know what is going on.

First of all you have the PIVOT table, this is the area on the worksheet to the left which is currently blank.  It is blank because we haven’t told Excel what to create the PIVOT table using yet, we’ve just told it we want to create a PIVOT table and here is where our dataset is.

The second part is on the right hand side and this is the PIVOT Table field list.  This is where all the fields in our dataset are displayed, then below you are presented with 4 different boxes where you can drag those fields into (drag by left clicking on the field and holding down the left mouse button while you move the field into the relevant box).

These 4 boxes are:

Report Filter: This is for any filters that you might want to set up on your PIVOT table.  An example would be having a PIVOT table showing sales by month with a filter on staff member, that way the user could see the overall sales by month by then using a drop-down box select individual, or multiple, staff members to filter the sales by month on.

Column Labels: This is the field(s) you want displayed across the top of your PIVOT table as column headings, so if you wanted the sales by month example to be split by staff members across the top then you would put the staff member field in here.

Row Labels: As with column labels but this is where you place the field(s) that you want as a row heading.

Values: These are the middle part of our PIVOT Table and the values we want to show, these are typically volumes, i.e. number of sales or amount of sales.

Standard Layout of a PIVOT Table

Step Three – Populate the PIVOT Table

We now have a blank PIVOT table so it is time to populate it with some data.  For this example we will produce the sales by staff member so we simply drag the field Staff Name field to the row labels box and the sales amount field to the Values box, an important point here is that you do not need to use all of the boxes, you always need a value and either a row or a column label but sometimes that is all you need to summarise.

And hey presto you have completed a PIVOT table.

Check the interactivity (for your own learning)

To look at one of the powerful features of a PIVOT table you can see the interactivity in action.  From the PIVOT table summary we can see that staff member John Smith has total sales of 20,358 but if you want to see what sales made up that 20,358 total all you need to do is double left-click on John Smith or on the 20,358, a new worksheet will open up with those sales on it:

As you can see John Smith had 17 unique sales transactions for his total of 20,358.

 

Final Thoughts

PIVOT tables are a critical part of any reputable Excel analysts armory, they are very effective in summarising complex datasets and allowing the end-user to gain meaningful insight into the data.

PIVOT tables are interactive so an advantage is while you can summarise the data you can also easily drilldown to a lower level of detail, i.e. all the transactions for a particular staff member, simply by double clicking on the relevant value in the PIVOT table.

Further Learning

This is just an overview of a basic PIVOT table and how to create one, as you can appreciate there are lots more options and features that need to be explored in order to get yourself to expert level with them.

In future posts I will cover off advanced PIVOT tables, formatting PIVOT tables and also take a look at PIVOT charts and how you can use them in Excel dashboards and reports.

Please feel free to leave a comment if this has helped you or you would like to see a future post on PIVOT table specifics.

If you want to see the end file of the example in this post please click here.

Keep Excelling,