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.
You are then presented with the pivot options below: –
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.
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
By dragging the “Location” field to the Column Labels option the data is displayed as
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.
The data is then presented in a familiar table style with a matrix system used to display and analyse the data.
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.
The pivot table now looks slightly different due to the addition of the Filter box above the displayed data.
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.
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.
Selecting the location as the filter type produces a slicer and pivot per the image below.
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.