Pivot Tables

Pivot tables are probably the most powerful tool in MS Excel however they are surprisingly easy to make and use. Pivot tables allow large quantities of data to be analysed and presented in a variety of ways.

Creating pivot tables

In order to create a pivot table highlight the data table and navigate to the Pivot table option in the Insert ribbon. Let us look at some simple sales data from a chain of pizza shops.

pivot basics

pivot basics 2

You are then presented with the pivot options below: –

pivot basics 3

All of the columns from the data table will be displayed in the “Choose field” tab of the pivot options.  These can then be dropped into any of the four options below, Row label, report filter, column label and Values.

pivot basics 4

In the above example the “Location” field has been selected for the Row labels and the sales data has been selected for the Values to display.

Report filter – Any column within the data source can be used to filter the data.

Column labels – Use a selected field as vertical headings for columns

Row labels – use a selected field as a horizontal heading for rows.

Values – Select the data to be returned from the data.

The above pivot had “Location” as the Row label and “Sum of Sales” as the Values field and displayed as this

pivot basics 5

By dragging the “Location” field to the Column Labels option the data is displayed as

pivot basics 6

As you can see it is exactly the same data just presented in a very different way.  This basic data can be further manipulated by changing the field setting to have both a row and column label, as per below.

pivot basics 7

The data is then presented in a familiar table style with a matrix system used to display and analyse the data.

pivot basics 8

Filtering pivot tables

If we wanted to have the Pizza toppings as the Row labels but wanted to toggle between the different locations we could choose to produce a Matrix table as above or we could filter by location.

pivot basics 9

The pivot table now looks slightly different due to the addition of the Filter box above the displayed data.

pivot basics 10

 

pivot basics 11

The slicer tool

A more elegant and user friendly way to filter a pivot table is through the use of the slicer tool.  Slicers make it faster and easier to filter the data within the pivot table, add a slicer by clicking the following option in the PivotTable tools.

pivot basics 12

As per the filter option it is then necessary to select which area of the data you wish to be used to filter the data.

pivot basics 13

Selecting the location as the filter type produces a slicer and pivot per the image below.

pivot basics 14

The values of the pivot table can be amended from absolute figures to a variety of different options using the “Show value as” option.  For example the values above can be displayed as a percentage of the Grand total as per below.

pivot basics 15