Partner Plummer Parsons
Share this content

An accountant's guide to Excel PivotTables

30th Apr 2018
excel
istock_g0d4ather_aw

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).

pt1

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).

pt2

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).

pt3

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).

pt4

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).

pt5

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). 

pt6

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).

pt7

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.

pt8

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).

Conclusion

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? 

Replies (3)

Please login or register to join the discussion.

avatar
By Brend201
03rd May 2018 18:41

The warning at the top was enough to make me read on! (If you’re already an accomplished PivotTable user, you may find this article too basic.)

Is there a facility to download a file with the sample data? That would be useful as the screenshots are a bit small.

Thanks (0)
avatar
By Paul Xledger
09th May 2018 15:37

A really useful guide to pivot tables! Out of interest, would anyone find it useful if Pivot Tables could be created directly from your Cloud Finance System?

We work a lot in the finance automation space, and it always seems that the last bastion of Excel for senior finance bods is the pivot table!

Thanks (0)
avatar
By EnglishRose
18th May 2018 17:30

Just a warning to readers in a recent case an organisation providing a pivot table did not realise data was included that was confidential (names of everyone in a London borough whose property in Kensington was empty)., The Guardian published it and the ICO fined the council and said they ought to have realised that there was this risk as warnings about "hidden" data in pivot tables had already been issued under data protection law.
I am not saying don't use them but do be careful who gets them and what data can be read and by whom.

Thanks (1)