Study smart | Excel super-stars
Pivot tables
Create and master pivot tables to transform the way you use spreadsheets and generate reports
Words Traci Williams, excelace.co.uk
Pivot tables are the perfect solution to summarising data in a spreadsheet. As the name suggests, the data can be ‘pivoted’ at the touch of a button, making pivot tables an extremely fast, reliable and versatile option.
Creating a pivot table
With data such as this pictured below, we can see totals at the top of each column, which is perfect for connecting to a pivot table to summarise it:
To create a pivot table, start by selecting all of the data that the pivot table should be based on. I highly recommend using a named range to use with pivot tables.
Note: It is essential that none of the cells in the first row of the selected range are empty.
From the ‘Insert’ ribbon, select ‘Pivot Table’:
This screen will appear, and it requires two inputs:
- Range (as selected, or press F3 to select a named range)
- Where to place the pivot table. This defaults to ‘New Worksheet’ or you can select the required sheet and cell.
- Make the required selections, then press OK.
The pivot table will be created as follows:
On the left is the (empty) pivot table, and on the right is the pivot table fields list.
At the top of the fields list are the column headers from the raw data (this is why they cannot be blank). The fields can now be simply dragged and dropped into one of the four boxes at the bottom, and the report (on the left) will instantly update:
- Filter – will provide a filter for the pivot table
- Column – will include a column for each field
- Row – will include a row for each field
- Values – items included here must be calculated (e.g. sum, count, average etc).
There are no set definitions as to which box to use; it all depends on the data you have and the summary you need to generate. However, it is a very simple drag-and-drop exercise until the pivot table looks right.
In the pivot table below, I have summarised the quantity by area and sales person:
The area is in the ‘Rows’ field, so there is a row for each area.
The sales person is in the ‘Columns’ field, and that provides a column for each person.
The ‘Values’ field includes the quantity and this has formed a matrix (using the calculation ‘sum’), and includes the sum of the quantity column, where the area and sales person is the same.
The grand total can also be perfectly reconciled to the quantity column in the raw data, so it is very simple to check it is working correctly. As the pivot table does not rely on individual formulae, there is very little need for reconciling, making this function a safer and more robust solution.
This pivot table can very easily be amended to show a completely different summary, simply by changing the fields as follows:
The above pivot table is now summarising the quantity by product and country, as you can see from the amended fields, in the fields list.
The grand total is also identical to the first pivot table and the raw data, so we know it is working correctly.
The real beauty of a pivot table is that if (or when) the raw data changes, we can simply right click anywhere on the pivot table and select ‘Refresh’. Any changes to the raw data will then be updated, as follows:
In the raw data, I amended ‘Scotland’ on the first row to ’USA’, so the pivot table has now included a new column to include the new country. Of course, the grand total remains unchanged.
Formatting a pivot table
If you need to format the values that appear in a pivot table, it is better to format the field as opposed to manually formatting the physical cells because the formatting will always be applied to the field, no matter how the layout is amended.
Start by left-clicking the relevant field within the values box of the field list:
Right-click and choose ‘Value Field Settings’ and this screen will appear:
Here you can amend how the name appears in the pivot table (1), the calculation that is applied (2) and the number format (3).
Two additional toolbars appear when you have selected a pivot table:
In the ‘Design’ toolbar, there are lots of different styles that can be applied to the pivot table to make it look professional and easier to read:
The designs will look different depending on the layout of the table, too.
You can also adjust column widths and sort the order of data as required.
Other key things to note
There is no limit to the number of pivot tables that can be created in a spreadsheet, and they can also be created in a different file to the raw data. This can be extremely useful if there are large complex working files containing lots of data and calculations, as a new file can be created containing a summarised pivot table and this can be much easier to send or share.
However, be aware that it may still be possible to drill down on such pivot tables (by using filters or changing fields), so take care that sensitive information is not disclosed or accessible here. Avoid this by either ‘protecting’ the worksheet so that the pivot table cannot be edited or, to be extra cautious, save the pivot table in PDF format.
Conclusion
Pivot tables provide a simple and robust way to analyse data, in multiple ways, at the touch of a button. Of course, these summaries could also be produced by way of ‘sumif’ or ‘countif’ formulae, but the dynamic nature of pivot tables make them a much more efficient option.
At first pivot tables can appear complicated, but the simplicity of dragging and dropping the fields makes them easy to play about with, without fear of touching the raw data.
QUIZ TIME
Test your knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
What is the primary purpose of a pivot table in Excel?
2
Which of the following fields is NOT a component of a pivot table?
3
How can you update data in a pivot table after the source data changes?
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).