The pivot table is one of the single most powerful tools in the Excel 2013 repertoire. It is frequently used for large data analysis.
You can use the pivot table as an interactive data summarisation tool to automatically condense large datasets into a separate, concise table. You can use it to create an informative summary of a large dataset or make regional comparisons between brand sales.
Pivot tables do not have to be intimidating – far from it – and we are here to help you unlock one of the real hidden power tools of Excel.
To demonstrate the power of pivot tables, we will be using the UK Government dataset 2013 UK Fleet Landings by ICES Rectangle which can be downloaded via the linked page, if you want to play along at home.
Summarise All Your Data – Small Vs. Large Datasets
How you organise and group your data can make vital differences in uncovering trends. In a small sample, as in our example below, it is easy to spot trends:
- Directors are paid more
- Creative Directors have lowest average wage
- Post-grads earn, on average, £9,000 more than individuals with a college education
Scaling up to a government dataset demands a different approach. What appears to be a colossal list of raw, unlinked data can actually hold the key to business success or failure. Pivot tables are suited to larger datasets and to work really well, they need to hit a few basic criteria:
- Numeric Information: Your subtotals require this data.
- Duplicates: At least one column with duplicate data ranges. Duplicate data ranges allow us to more effectively filter and analyse our information, providing numerical data ranges for the duplicate sources.
Import Raw Data Into a Pivot Table
Luckily for us, Excel 2013 contains the most up-to-date pivot table tool, now even offering up suggested pivot tables across the full spectrum of your available dataset. However, where the presets do not fit the bill, you can always manually select the pivot table wizard to get you rolling:
Clicking ‘Pivot Table’ should then bring you to this menu:
Stick with the existing settings for now, though as you can see there are options to connect an external data source (i.e. MS Access), or place the pivot table within the existing worksheet. For the latter option be sure to check that your new pivot table will not obscure or break your existing data (though you can always hit CTRL+Z if something terrible does happen!).
So, clicking OK should present you with an entirely new worksheet for your brand spanking new pivot table. Excited yet? You should be: this is where the data magic happens!
If you are still with me, and I truly hope you are, your page should look like what is shown in the screenshot above, or something very similar if you chose a different Worksheet. Excel has very kindly sorted our Pivot Table fields so as we can work in two ways:
- Drag and Drop: The pivot table fields listed on the right-hand side can be dragged into the 4 areas below (Filters, Columns, Rows and Values). You can select specific data items that you wish to cross-reference against any number of differentials.
- Add to Report: Clicking individual dataset fields will add them directly to the waiting report table (PivotTable2 in screenshot above). This method can be used to rapidly build, analyse, collate and modify the available data.
Whilst both methods deliver similar outcomes, Add to Report allows you to simply select your preferred data field and marvel in wonder as your report table is transformed. As a bonus, each time we click a new data field it is automatically added to the area Excel deems correct, whilst reminding us that we cannot add a data ‘field with more than 16384 items to the column area.’ Thanks, Excel, always looking out for the business person, aren’t you?
Pick & Compare Data Fields
Now, click Vessel Nationality, Species Name, Landed Weight (tonnes) and Value (£). Your data subtotals should now look like this:
Whilst your pivot table fields should match this:
We can now rapidly glance at our custom pivot table data fields and analyse the available data to ascertain any trends. As a result of our actions, we now know that England fishes the most Bass throughout the UK, though the fish itself is worth more in Wales. Insight gained!
Filtering Your Data
Feel free at this point to play around with the different fields, learn which varieties of data produce concise correlation, which data fields need tweaking to reveal their secrets and to comprehend the value actions of filter settings:
- Hover over the desired data field, note the small black arrowhead to the right of the text
- Selecting the black arrowhead reveals a drop down ‘Filter’ menu – extremely useful for isolating data within a single field, or comparisons between similar data ranges across multiple data sources.
- Select the Vessel Nationality filter menu.
- By default, all data ranges are selected. Unselect all (by hitting select all), followed by reselecting England and Scotland. This isolates the data for these countries, filling our pivot table with concise, comparative data we are able to begin to analyse for potential trends.
Modifying the filters of each available field will almost always directly alter the data presented in the pivot table and is a sure fire method of unlocking the true potential of your data.
For this example, we used a small proportion of a relatively large dataset. Pivot tables work best when they have several thousand individual records, multiple data fields and a range of duplicate numerical figures. That is not to say their relevance is strictly associated with large datasets but, when dealing with a massive amount of raw data, nothing can beat the pivot table in ease of use, data filtering or its concise summarising facilities.
Has this ‘How To’ helped you, or have we missed something? We’d like to hear your pivot table experiences below!