Pivot tables
A pivot table (or pivot table report) is an Excel tool that that makes large amounts of data much more manageable. Pivot tables automatically summarise the data and provide several ways of manipulating the data to aid analysis.
Manipulating the data provides different views which can help you to spot patterns and relationships that are not visible from the raw data alone.
Excel will arrange the data from each column that exists in the source data into fields. These fields can then be added to form the columns and or rows of the pivot table.
Inserting a pivot table
Before inserting a pivot table you need to ensure each column of data has a heading as Excel uses these to generate the field names of the pivot table.
To insert a pivot table:
• Select the first cell of your data (normally cell A1)
• Click the insert tab on the ribbon
• Click the pivot table button – the create pivot table dialogue box appears
• Ensure the select table or range tick box is marked
• All the data should be selected in the table/range field – if it isn’t, click and drag to select the correct range of cells
• Choose where the pivot table will be created by selecting new worksheet or existing worksheet
• If you have chosen existing worksheet you will need enter the cell reference for where you want the pivot table to be placed
• Click OK to create the pivot table
Select the first cell of the data to be included in the pivot table
From the insert tab click the pivot table button from the tables group
The pivot table will now be created:
Excel should automatically select all the data to include
Create the pivot table on a new
worksheet or on the existing worksheet
Click OK to create the pivot table
The pivot table has been inserted on a new worksheet
and is ready to be configured The pivot table field list is now visible at the right of the screen
Adding data to a pivot table
The blank pivot table now needs to be populated with data using the pivot table field list. Fields are added to one of four areas of the field list:
Column labels fields placed here will form the columns of the pivot table Row labels fields placed here will form the rows of the pivot table
Values fields placed here will form the data that is summarised in the pivot table Report filter fields placed here can be used to filter the pivot table
To add a field to a pivot table:
• Click the field name in the pivot table field list
• While holding the left mouse button down, drag the field to the relevant box (column labels, row labels, values, report filter)
• Fields can be dragged to other field list areas, or removed by clicking the field name and clicking remove field
Note: A key benefit of using pivot tables and charts is that data can be rotated or ‘pivoted’. Move the fields around within the field list areas to explore your data.
Click and hold a field from the list
Drag the field to one of the four field list areas
As you add fields to the pivot able the pivot table will be automatically updated:
In the example above:
• Cost has been added to the values area – the pivot table will summarise the cost of orders
• Rep has been added to the row labels – each row represents the orders that a rep has processed
• Campus has been added to column labels – each column represents the orders for each campus
• Order date has been added to report filter – the pivot table can be filtered by order date
NOTE: There is no definitive method of placing fields into the ‘correct’ field list area. It depends on how you want the data to appear in the pivot table and the type of data you are working with
If you do not want to update the pivot table automatically, tick the defer layout update box then click update when you are ready
Pivot table number formatting
As with any data in Excel, you should apply the correct formatting to value fields to ensure the data is displayed in the correct context. For example monetary values should have the relevant currency formatting applied.
To change the number formatting of a field within a pivot table:
• Click the field name in the values area or right-click the field in the pivot table
• Click value field settings
• Click the number format button
• Select the required format from the category list
• Complete any additional settings such as decimal point placement
• Click OK
From the pivot table field list area:
The value field settings dialogue box appears:
Click on the field you want to change
Click value field settings from the menu
Click the number format button
Select the required formatting category
Complete any additional settings (such as decimal places or symbols)
Click OK
Changing how values are summarised
By default Excel uses the SUM function to summarise numerical data (each individual item within a field is added together) and the COUNT function to summarise non-numerical data (Excel counts how many times a particular item occurs within the field).
Changing how data is summarised within a pivot table can provide alternative views of data that can make it easier to see trends and allow further exploration of data that would not be possible with the default summary functions.
To change how data is summarised:
• Click the field name in the pivot table
• Click summarise values by from the calculations group of the pivot table tools tab on the ribbon
• Select a function from the list From the pivot table display:
From the ribbon:
Click into the values area of the pivot table
Click the summarise values by button from
the calculations group Select a new
function to use to summarise values
The pivot table is updated:
Available summary functions
This table explains the summary functions you can use in a pivot table:
Function Description Sum (default
function for numerical data)
Sum of the values within the field – this is the same as the COUNTA worksheet function
Count (default function for non- numerical data)
Counts the number of times each word or phrase occurs within the selected field
Average Average of values within the selected field Max Largest value within the selected field Min Smallest value within the selected field Product The product of the values within the field
Count Nums Counts the number of values that are numerical – this is the same as the normal COUNT worksheet function
StDev Returns an estimate of the standard deviation of a population containing just part of your data
StDevp Returns the standard deviation of the whole of your data
Var Return an estimate of the variance of a population containing just part of your data Varp Returns the variance of the whole of your data
In this example the pivot table is now summarising the cost field using the AVERAGE function
You will see in the top left-hand corner how your data is being summarised
Custom calculations
The types of calculations that can be performed on data within a pivot table are limited. However, it is possible to display the results of calculations of other fields or items within the pivot table. This provides additional analysis and further insight into data.
In the example below the cost field has been calculated to return the values are shown as a percentage of the column totals.
To display values using a custom calculation:
• Click the field name in the pivot table field list
• Click value field settings from the menu
• Click the show values as tab
• Select a calculation from the list*
• Click OK
*if the %of calculation is selected you will also need to select the base field and base item
From the pivot table field list area:
Click the field you want to change
Click value field settings from the menu
The value field settings dialogue box appears:
NOTE: don’t forget to change the number formatting and decimal places etc.
Base fields and items
When using the percentage of (% of) calculation, it is possible to calculate a field where the result is a percentage of a separate item. To do this you need to specify the item on which this is calculated – this is called a base item – the field which contains this item is called a base field.
In the example below the cost of orders belonging to each person is being calculated as a percentage of the cost of orders belonging to Andrews (one of the sales reps). The result of this calculation is that the cost of the items sold by each rep will be summarised as a percentage of Andrews’ sales.
To use the percentage of calculation, you need to specify the item which will be used in the comparison
First choose the base field which contains the item you want to compare with other items to form the percentage
When you have selected a base field, select the particular item which will form part of the percentage calculation Click the show values as tab
Select the required calculation from the drop down list
Click OK
Using formulae in pivot tables
If the default or calculated summary functions cannot give you the analysis you need for your data, it is possible to create a new calculated field or item.
In the example below we have the same pivot table used in previous examples showing a summary of the cost of stationary orders within the university. We can create new field and apply a formulae to show the projected costs for the different departments if there is a 6% increase in stationary prices.
Example:
Inserting a calculated field
To add a calculated field to your pivot table:
• Click the pivot table
• From the pivot table tools menu on the ribbon click the options tab
• From the calculations group click the fields, items and sets button
• Click calculated fields, the insert calculated field dialogue box appears
• Enter a name for the new field
• Enter the formula by typing field names or selecting from the list and clicking the insert field button to add field names to your formula
• Click Add if you want add another calculated field or click OK
• The new field will be added to the values area of the field list
NOTE: don’t forget to change the number format of the new field if required.
Original summary of data
New field added and formula applied which calculates the original costs with a 6% price increase
Click anywhere on the pivot table report to show the pivot table tools menu on the ribbon:
The insert calculated field dialogue box appears:
Enter a name for the new field
Enter the
required formula
You can select field names and use the insert field button to add field names to the formula
Click OK to add the field and close the dialogue box
Click calculated field Click the fields, items
and sets button Click the
options tab
The new calculated field is added to the field list:
NOTE: To delete a calculated field select it from the drop down menu in the dialogue box then click the delete button
Inserting a calculated item
It may be necessary to insert a calculated item within a field rather than inserting a complete field. In this way the calculation is only applied to a specific item rather than a whole field.
• Click the field to which you want to add a calculated item
• Click the options tab from the pivot table tools menu
• From the calculations group click the fields, items and sets button
• Click calculated item from the menu – the insert calculated item dialogue box appears
• Enter a name for the new calculated item
• Enter the formula (you can type item names or select from the list and click the insert item button to add item names to your formula)
• Click OK, the item is added to the field
Click anywhere on the pivot table report to show the pivot table tools menu on the ribbon:
The new field can now be added to any area of the field list
Click calculated item Click the fields, items
and sets button Click the
options tab
The insert calculated item dialogue box appears:
NOTE: To delete a calculated item select it from the drop down menu in the dialogue box then click the delete button.
Applying filers to pivot tables
Filtering your data allows you to pick out items based on their characteristics. For instance, you might want to select a group of students according to which courses or modules they are studying. This can be
achieved using filters.
Basic filters
It is possible to filter pivot tables in a number of different ways the first method is to insert any fields you want to filter the pivot table by into the report filters area of the field list. The benefit of this method is that it allows you to filter using a field without having to display a summary of the results of that field. It is also possible to filter a pivot table using any field that has been added to the column or row areas.
To filter using a field that is already included in the pivot table:
• Click the down arrow to the right of the row or column label header
• Use the tick boxes to select which items from the field you want to filter by, use the select all tick box to automatically select all items
• To clear a filter click clear filters from ‘field name’ from the same menu
Note: If there is more than one field in the row or column you will need to click an item within the field you want to filter
Enter a name for the item field
Enter the
required formula
You can select item names and use the insert item button to add field names to the formula
Click OK to add the item and close the dialogue box
To filter using a field that is not included in the pivot table:
• Drag the field you want to use as a filter from the field list to the report filters area
• The field will now be visible at the top of the pivot table
• Click the down arrow to the right of the field
• Use the tick boxes to select which items from the field you want to filter by
• Use the select all tick box to automatically select all items which effectively clears the filter In the pivot table field list:
Click the arrow to right of the row or column header
Use the tick boxes to filter items in the pivot table
Click OK to apply the filter
Drag the field you want to use as a filter to the report filter area
Label filters and value filters
As well as the filtering options detailed above it is possible to perform more advanced filtering on row or column labels or using the values contained within the pivot table.
Label and value filters are accessed from the same drop down menu in the examples above.
The filter will appear above the pivot table
Click the down arrow
Use the tick boxes to select an item – to select more than one item click the select multiple items tick box
Click OK to apply the filter
Click label filters to filter based on the row or column labels
Click value filters to filter based on the summarised values
These are the available options to filter a pivot table based on row or column labels:
These are the available options to filter a pivot table based the values contained within the pivot table:
In this example the begins with filter would show all rep names starting with ‘S’
In this example the top 10 filter would show the top 10 reps based on the sum of cost
Slicers
Slicers are a new feature in Excel 2010 that provides a graphical interface to filter pivot tables and charts.
You can add as many slicers as necessary which makes filtering your data based on multiple fields much more straightforward than other filtering methods.
The example below shows a slicer for an order date field, clicking one of the date buttons will filter the pivot table and or chart so that only orders for that date are displayed.
Inserting a slicer:
• Click the insert slicer button from the sort and filter group of the pivot table tools option tab
• The insert slicers dialogue box appears
• Select the fields you want to add a slicer for by ticking the relevant box
• Click OK
Click insert slicer from the sort and filter group
Hold the ctrl key while clicking on items to select more than one at a time
Click the clear button to clear all selections
Select the fields you want to add a slicer for
Click OK
The slicer is inserted on the same sheet as the pivot table
Sorting pivot tables
Excel will display data in the pivot table in the same order that it appears in the associated raw data. The pivot table data can be sorted in two main ways. The most basic way of sorting data is to sort a row or column into ascending or descending order. The second way is to sort a row or column based on the values within the table.
To sort a column or row of data within a pivot table:
• Click the filter button within the column or row you wish to sort
• Click sort A-Z to sort in ascending alphabetical, numerical or date order
• Click sort Z-A to sort in descending alphabetical, numerical or date order
Click the filter button
Click sort A to Z or sort Z to A
Click OK
To sort a column or row of data based on values:
• Click a cell in the data area that contains the values you want to sort by
• Click sort from the sort and filter group of the pivot table options tab
• Select smallest to largest or largest to smallest
• Select top to bottom or left to right
• Click OK
Click a cell in the in the data area you want to sort by
Click sort
Select additional options
Click OK
Custom sort order
If a custom sort order is required within a field it is possible to manually sort items by dragging them into place within the pivot table.
To sort items into a custom order:
• Click the filter button within the column or row you wish to sort
• Click more sort options
• Click manual (you can drag items to rearrange them)
• Click OK
• Hover the cursor over the cell border of the item you wish to move until the cursor changes to a four way arrow
• Drag the item to the desired position
• Repeat until desired sort order is achieved
Click the filter button
Click more sort options
Select manual
Click OK
Hover the cursor over the cell border of the item until the cursor changes to the four-way arrow
Drag the item to the desired location
Grouping items
As mentioned earlier in this guide a pivot table summarises the items in each row and column. It is possible to group items together within a field. This means that you can view summarised values for a group of items as well as an individual summary for each item.
In the example below a number of sales reps have been placed into the ‘new starters’ group this allows us to see a comparison of the reps that have recently started to the existing reps.
To group items within a pivot table:
• Holding the ctrl key, click to select the items you wish to group
• From the pivot table tools options tab click group selection
• Click the new ‘group1’ label and type in a new name
• Click collapse entire field from the active field group of the ribbon
• Click the plus icon next to the new group to expand
If necessary, change how subtotals are displayed for the group by going to the design tab of the pivot table tools on the ribbon. Click the subtotals button, and select whether you want the subtotal to display at top of group, bottom of group or not at all.
Group and individual subtotals are now visible
Click group selection Select the items
you want to group by holding the ctrl key and clicking on them
Click group1 and start typing to overwrite the name with a new one
Click collapse entire field
From the design tab, click the subtotals button and
choose how subtotals are displayed
A summary of values is now available for the new starters group as well as individual reps
Updating pivot tables
As pivot tables provide a summary of raw data, if any changes are made to the raw data or new data is added the pivot table will need to be refreshed or updated.
To update a pivot table:
• If changes are made to the pivot table source data click the refresh button from the data group of the pivot table tools options tab
• If new rows or columns are added to the pivot table source data click the change data source button from the data group of the pivot table tools options tab, then select the source data to be included in the pivot table
Click the refresh button to add altered source data into your pivot table
Click change data source to change the source data selected to be included if new rows or columns have been added
