You must have heard of PivotTables when using Excel… but have you ever used them? And do you actually know what they do?
PivotTables summarise large amounts of Excel data from a database that is formatted where the first row contains headings and the other rows contain categories or values. PivotTables can be extremely useful but also complicated to understand – so let’s take a look together.
How PivotTables work
PivotTables take tables of data, usually with hundreds or thousands of rows, and help you make sense of those tables of data by summarising them by any of the column headings.
For a PivotTable to work correctly, the data, whether it is an Excel table or from an external source, needs to be set up in a particular way. Each column must contain the same type of data and the data must be as ‘raw’ and unprocessed as possible – for instance, one column holding a date rather than separate columns for the monthly totals.
An example of the sort of task a PivotTable can help with would be taking a large table of business invoices and working out which products are selling best or which sales people are making the most sales, or even which customers have the fastest growing sales figures.
Advantages to using PivotTables
I know, some of you might be thinking – I already know of several Excel functions that can do all the above. True. However, the advantages of using PivotTables instead of other functions are massive:
- Simplicity – PivotTables use a graphical approach to create summaries. You just drag a column heading to the area of a PivotTable where you want to see it, rather than having to construct complicated summary formulae. It’s that easy to display data graphically, that a PivotChart can be created from a PivotTable with a single click.
- Multiple dimensions – You can summarise data utilising both row and column headings, including using multiple fields in each area. You can also use a series of criteria fields that filter all the data in the table. Excel 2010 introduced ‘Slicers’: interactive criteria controls that are linked to each other and can be linked to multiple PivotTables.
- Speed – PivotTables are quick to produce, it only takes a few clicks of the mouse.
- Interactive analysis – Once you’ve arranged your data in the PivotTable, if you think of other useful information the data could provide, you can rearrange the data simply by dragging fields to different areas.
- Automation – Although even the very simplest PivotTable can be very useful in its own right, PivotTables can also be used as the ‘data engine’ behind a set of management reports or an interactive business intelligence dashboard. In particular, when linked directly to external data, PivotTables can be automatically refreshed to include new and amended data.
These are the four key areas in a PivotTable: FILTERS, COLUMNS, ROWS and VALUES.
MS Excel Online Training
PivotTables are so important that we dedicate three entire units to them in our Filtered course. Filtered is an online learning platform that only teaches the features of Excel that are useful to users, saving you precious time and providing a truly tailored learning experience.
AAT members have free access to this and many other Excel tips through their MyAAT account. PivotTables are mentioned multiple times in the Filtered Microsoft Excel course as they are extremely useful.
Paolo Lenotti is the Head of Marketing & PR at Filtered.com.