In this guide, Nick Brown of Brighton-based accountancy firm Plummer Parsons looks at the enduring power of the PivotTable.
If you’re already an accomplished PivotTable user, you may find this article too basic.
Pivot tables are probably one of the most talked-up yet the least understood features of Excel with accountants. Their power comes about in the way that they can represent a single dataset in a multitude of ways, without the use of formulas.
Unlike tables built using types of lookup formulas like VLOOKUP, SUMIFS and COUNTIFS, PivotTables can be rearranged in seconds by simply dragging and dropping fields into one of four different areas.
To better understand how a PivotTable works, it makes sense to actually look at how to create one and the various ways we can manipulate the data within it.
How to create a PivotTable
Here’s a basic data table in Excel to demonstrate how to use pivot tables. This consists of various garden products, all of which come in various colours. This has further been broken down by date of sale, unit cost, unit number sold and total sale value.
To create a PivotTable, simply select Insert on the ribbon and then select Pivot Table. You will be presented with a box asking you to define the area of your PivotTable. Simply highlight your table, including the field row (see image below).
You will be presented with a field list, listing all the fields in your dataset, along with four areas into which you can drag these fields (see image below).
The areas you can drop your fields into are broken down as follows:
- REPORT FILTER: This allows you to apply a filter to your PivotTable.
- ROWS: Dragging fields to this area will break them down into rows in your pivot table. You can drag multiple fields to this area to create sub-rows.
- COLUMNS: Dragging fields to this area will break them down as columns in your pivot table. You can drag multiple fields to this area to create sub-columns.
- VALUES: This area represents the values your PivotTable will display.
Let’s say we wanted to see the total sales figures broken down by product. To do this we simply drag the product field into the Row Label area to create our rows. We then need to tell the pivot table what values to show, so we drag the total cost field into the Values area (see image below).
Let’s now say we want to break this data down by product colour. There are two ways to do this, which will give you two different results. The first, and most sensible, way to do it is to break the product colour down into columns (see image below).
As you can see, our table is now showing us the total cost of all the products but these are now broken down by product colour. An alternative would have been to drag the colour field into the row labels area below the product field. This will give us a slightly different view, but with similar results (see image below).
Using these principles, you can create any number of variations in how you represent this data. In this case, I have added an additional sub-row using the date field (see image below).
This data is fairly limited, but with larger tables that have ten or more fields, with thousands or even tens of thousands of rows, the power of PivotTable becomes clear.
Adding a filter to a PivotTable
Data in a PivotTable can be further manipulated with filters. Let’s say we wanted to maintain the total sale view broken down by product and product colour, but just wanted to see all sales made before 10 January.
In the case of our dataset, I have simply dragged the date field to the Report Filter to create a filter. I then go into the filter, tick ‘select multiple items’ and click on the dates that fall before 10 January (see image below).
Filters allow you to keep the structure of your PivotTable whilst filtering all the data within it. You can add as many fields as you want to the Report Filter area, but you cannot then add those same fields to your Row Labels, Column Labels or Values areas.
How to customise your PivotTable
There are myriad ways to customise the way your PivotTable looks and the data output. By right-clicking anywhere on the table itself you can bring up a number of options. These are too numerous to go into here in depth, but personally, I like to turn off the ‘Autofit columns widths on Update’ option so I can keep my column widths fixed.
You can also change how your PivotTable formats and even calculates the data it displays. You might want to simply count instances of a certain occurrence for example. To do this, simply click on the tiny arrow in the field you’ve dropped into the Values area (in our case Total Cost) and select ‘Count’ over ‘Sum’ (see image below). You can also change the number formatting here.
A final note of caution is that PivotTables do not automatically update themselves, so if you change anything within your source data, you must refresh your table. You can do this in the Excel Ribbon under the Options section of the PivotTable Tools (remember to select your PivotTable first).
We’ve explored PivotTables using a very simple and small dataset, so it should be noted that PivotTables really start to come into their own with much larger and more complex datasets. This is because they can quickly and effortlessly allow you to manipulate large volumes of data into different views without slowing your spreadsheet down.
In this sense, compared to traditional formula-based tables, pivots really can open up your ability to report financial data in Excel. Why not give it a go?