The PivotTable is one of the simplest yet most effective tools available in Microsoft Excel. It is by far the quickest and easiest way to analyse a dataset. It is a function that sounds complex but in actual fact is very simple if you follow a few simple steps. In this blog post I will show you how to conduct some basic analysis on a dataset by creating a PivotTable. For the purposes of this example I will be using Excel 2013.
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, very usefully 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 is easy to spot the error.
If you have a data set with errors in like this you 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 2 – Give your column row headings and create a table
PivotTables work off your column headings so make sure each column has one! Also make sure 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 is also best practice to convert the data you are using into table format. You don’t have to do this to create a PivotTable but the advantage of creating a table is that if you add any more rows of data at a later date 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 3 – Create your PivotTable
This really is the easy bit. If your data is in table form you can 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 4 – Build your PivotTable
This is where the fun really starts. PivotTables have 4 fields that you can summarise data with. I always feel it is best to actually start dragging and dropping data into these fields to see what they look like. Let’s add data into each section to build out 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 (depicted below). We could also drop ‘Salesperson’ in the ROWS box under country to see each salesperson subcategorised 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 the data in the ROWS and COLUMNS.
If you want use a different mathematical function to Sum then simply click the little black arrow next to ‘Extended Price’ and it will bring up a few options. Select ‘Value Field Settings’ and the below options will show. You can select 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
This is just scratching the surface of what is possible with a PivotTable, but hopefully you can see from the example used that it is pretty simple. Creating a PivotTable is such a quick and easy way to draw out the analysis you want from a large dataset. The next stage is to display this data in an attractive way using PivotCharts, which might well be my next blog.