The PivotTable is one of the simplest and most effective tools in Microsoft Excel.

In this article you will learn how to conduct basic analysis on a dataset by creating a PivotTable from scratch.

For the purposes of this example I will be using Microsoft Excel 2013. But please bear in mind at Filtered we offer Microsoft courses that also cover Excel 2016 and various Mac versions.

To download the same dataset used in this post and follow along:

1. Log in to your Filtered Excel course and download the dataset or
2. Sign up for a free trial to Filtered and download the dataset 

Step one: organise and clean your data

PivotTables rely on the data they are running off being consistent and error free. Below is an example of the type of issues:

The errors in this data are circled in red. As you can see Venezuela is spelt incorrectly and two of the prices listed in column G have letters in front of them, so they are not registering as numbers. Excel right aligns anything it classifies as a number. This makes it immediately clear when it thinks an input isn’t a number. In this case these two stand out from the rest of the column so it’s easy to spot the error.

If you have a data set with errors you’ll need to clean it up before you create a PivotTable. A PivotTable will recognise the incorrectly spelt ‘Venezeula’ as a different input to the correctly spelt ‘Venezuela’ data, and so will exclude it if you are sorting, counting or planning to sum data off this line. The same goes for the incorrect numerical inputs. If you wish to add all of Nancy Davolio’s sales together in this example, the figures not recognised as numbers will be excluded.

Step two: give your column row headings and create a table

PivotTables work off your column headings so make sure each column has one. Also ensure you name them something that relates to the data in them, as you will be using these headings to manipulate data in your PivotTable.

It’s best practice to convert the data you are using into table format. You don’t have to do this to create a PivotTable but doing so means that if you add any more rows of data later on you can simply refresh your workbook and these new rows will be included in your PivotTable.

To turn a set of data into a table, click anywhere in the data and select Ctrl-A to select all the data, followed by Ctrl-T to create a table. Make sure you have the ‘My table has headers’ box checked to include those headers you just added.

Step three: create your PivotTable

This is the easy bit. If your data is in table form, just click in any cell in the table. If you are using data not in a table, you need to select all the data you wish to include in your PivotTable.

Next go to ‘Insert’ on the ribbon and select the far left option ‘PivotTable’. You should see the below displayed:

You now have the choice to insert your PivotTable somewhere into an existing worksheet or onto a new worksheet. Select whichever one you wish and hit OK.

Step four: build your PivotTable

This is where the fun really starts. PivotTables have four fields that you can summarise data with. I advise to start dragging and dropping data into these fields to see what they look like. Let’s add data into each section to build a table that analyses our data set.

Say we wanted to analyse this data by salesperson but also by country, we might drag and drop ‘Countries’ into the ROWS field and ‘Salesperson’ into the COLUMNS field to see all the countries and sales people (see below). We could also drop ‘Salesperson’ in the ROWS box under country to see each salesperson sub-categorised by country.

Next we drag the ‘Extended Price’ data into the VALUES field. As ‘Extended Price’ is a numerical column of data, Excel recognises this and will automatically Sum the values according to the data in the ROWS and COLUMNS.

To use a different mathematical function to Sum, simply click the little black arrow next to ‘Extended Price’ and it will bring up a few options.

Select ‘Value Field Settings’. You can pick from a number of options including:

• Count – if you wanted to know how many sales went through rather than their value
• Average – to return the average sale value
• Min/Max – to display the minimum or maximum values in each case

There’s so much more that you could do with a PivotTable but hopefully it’s clear from this example that it is pretty simple and effective.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.