Good Practice with Pivot Tables

Spreadsheets:

Good practice with

pivot tables

The small print

Prerequisites

Time in the workshop is precious – it is an opportunity for you to interact with the workshop leader

and other participants through questions and discussions and to share your experiences and

concerns. To make the most of this time we sometimes ask you to carry out learning activities ahead

of the workshop so that everyone comes into the class with the same basic knowledge. We keep this

prior learning to a minimum and often make use of online videos. Online videos provided through

‘Molly’ can be accessed by University members anytime, anywhere, through a browser or app.

Your course booking will tell you if any prior learning activity is required. If you don’t have an

environment where you can do this learning, you can come along to one of our ‘quiet’ sessions.

These are scheduled every week in normal term-time, and are a quiet space where you can work

through ‘Molly’ videos or other workshop resources.

If you turn up for a workshop without having done the prior learning, the workshop leader may

suggest that you come back on another session.

Graham Addis makes this booklet and the accompanying slides available under a Creative Commons

licence (BY-NC-SA: Attribution-NonCommercial-ShareAlike).

The Oxford University crest and logo and IT Services logo are copyright of the University of Oxford

and may only be used by members of the University in accordance with the University’s branding

guidelines.

About the workshop designer

Graham Addis started his first technology role in 1978 and has gathered decades of practical

experience in industry. He has always been passionate about passing on his knowledge and

undertook his first formal teaching position as a Customer Training Specialist for Intel back in 1984.

Since that time his career has combined extensive real world experience with teaching and

mentoring. In 2017 he joined the academic world at the University of Oxford and currently

specialises in teaching spreadsheets, databases and programming.

Revision history

Version

Date

Author

Comments

3.2

October 2021

Graham Addis

Update for accessibility

2.0

May 2020

Graham Addis

Convert to online format.

1.2

October 2019

Graham Addis

Update workbook references

1.1

August 2019

Duncan Young

Small print updates

1.0

September 2016

Traci Huggins

About this workshop

A PivotTable allows you to summarise large amounts of data and perform calculations quickly and in

a meaningful way. They allow you to create an interactive view of your data set. This is called a

PivotTable report. With a PivotTable report, you can summarise and perform various calculations on

large amounts of data, categorise your data into groups and organize your data into a format that is

easy to chart. But the real power of a PivotTable report is that you can interactively drag and drop

fields within your report, dynamically changing your perspective and recalculating totals to fit your

current view.

Use a PivotTable when you need to

➢ Find relationships and groupings within your data.

➢ Find a list of unique values for one field in your data.

➢ Find data trends using various time periods.

➢ Anticipate frequent requests for changes to your data analysis.

➢ Create subtotals that frequently include new additions.

➢ Organize your data so it is easy to chart.

We will include pointers to other workshops and further resources that will help you go on later to

analyse and organise your data.

What you will learn

At the end of this session you will have learned how to create pivot tables from different lists of data.

You will learn to create Pivot Charts from tables.

You will learn how to create calculated fields and calculated items. You will also learn to create

calculated items by position and dynamically adjust your pivot tables using slicers.

What you need to know

The ideas and techniques covered in this workshop will apply to a range of tools. We will

demonstrate using Excel for Windows, which is widely available. However, the concepts will be the

same, whatever spreadsheet software you decide to use.

I will assume that you are reasonably confident in using the tool you have chosen to use to create

your spreadsheets. With your chosen tool, you will need to be able to:

• open and navigate around a workbook using the mouse and scrollbars, save a workbook

• add data to cells, and select and amend such data

• create a formula that calculates using values found in other cells

• Navigate the commands and menus, using Help as necessary

If you need to review these activities, Molly is a great place to get guidance. There is an activity with

relevant Molly videos in the IT Learning Portfolio: visit skills.it.ox.ac.uk/it-learning-portfolio and

search for “Spreadsheets: Good Practice with Pivot Tables (Activity)”.

The resources you need

Sample documents that you can use to experiment with will be made available, but you may like to

bring along your own.

Unless you have been told otherwise, in classroom workshops there will be a computer available for

you to use with Excel for Windows installed.

You can use your own computer with your preferred app installed if you want to – just bear in mind

that I am not an expert in every app (although I am sure that between us we will be able to sort out

most problems!).

Learning Objectives

This workshop has the following learning objectives:

Learning Objective One - Create PivotTables from different lists of data

Learning Objective Two - Group data

Learning Objective Three - Creating Report Filters

Learning Objective Four - Use Slicers to filter data

Learning Objective Five - Create calculated fields and calculated items

Learning Objective Six - Create and work with Pivot Charts

Learning Objective Seven - Creating PivotTables using external data

Learning Objective One - Create PivotTables from different lists of data

The fields in the PivotTable Field List appear as checkboxes in the task pane dialog box (see diagram 4

above). To add fields to the PivotTable simply drag the field name to the appropriate PivotTable area of

the PivotTable task pane. The PivotTable has four areas

Report Filter: Allows you to view a subset of data and focus on a specific area such as a particular period

of time or person.

Row Labels: The unique values within the field selected appear as row items in the PivotTable.

Column Labels: The unique values in the field selected appear as column items in the PivotTable

Values: The field is summarised in the PivotTable by using a function. When you drag a field checkbox to

the Values area, Excel will automatically apply the SUM function to fields which contains numeric data

and the COUNT function to fields which contain non-numeric values.

You can drag as many fields as you want to any of these locations. You do not have to use all the fields.

If you drag a field to the wrong location, just drag it off the table diagram to remove it or uncheck the

checkbox in the field list. The order in which you list the fields in the data area will affect how the data

is sorted. It will sort by the first item followed by the second item and so on.

Data

Field List

PivotTable

Areas

PivotTable

Ribbon

To Do: ….

Using the workbook Transactions.xlsx worksheet “Data” (a large data set comprising of information on

details of orders sold by a DVD wholesaler) you need to create a one dimensional PivotTable to find

information out about sales by Genre and how many units were sold by each Employee.

In the new PivotTable, tick the following fields; Genre, Qty and Total (in that order) – you will then

notice that you have already discovered the answer to the Genre sales from above – but what if you

wanted a breakdown of the Total for each Genre?

Whenever Excel shows a total it is possible to “drill down” to enable you to see the transactions that

were used to calculate the total – to do this you need to double click on a total. A new worksheet then

opens showing the source transactions – it will list everything that has made up that total – this extra

sheet then stays there and will have to be manually deleted if it is not needed.

Now make some changes to the PivotTable;

• Show the values in Sum of Total to display as 2 decimal places

• Change the title Row Labels to display a new title of Genre

Extra

• Now look at the other data provided, choose one of them and create a PivotTable

• Decide from the newly created PivotTable data the best way to filter this data

Note;

A PivotTable does not automatically update when the data source changes until you click PivotTable

Tools – Analyze – Data – Refresh. A PivotTable will not update to reflect changes to the source data

(whether this source data is a worksheet or an external data source). By default Excel invisibly stores a

local copy of data from an external data source. This means that you can still use the PivotTable even if

the source database is down.

Learning Objective Two - Group data

Grouping PivotTable items is a handy feature that enables you to group specific items in a field. A group

field provides a higher level of summary detail than that provided by the original source data. For

example, companies can be grouped into regional areas, numbers grouped into ranges and dates

grouped into months or quarters.

In the row and column label areas of a PivotTable report, you can group the items in a field in a custom

way. Grouping the data can help you to isolate a subset of data that satisfies your specific needs and

that cannot be grouped in other ways such as sorting and filtering.

When you group items by selection you create a new field based on the field whose items you are

grouping. For example if you group the SalesPerson field by specific names, you create a new field

SalesPerson1, which is added to the PivotTable Field List.

You can work with this new field in many ways for example; move it to different areas of the PivotTable

report, rearrange it with other fields in an area, rename the field by using the Field Settings dialog box,

and filter the field it is based on by using the new group name and values. However please note that

you cannot add a calculated item to a grouped field. You must first ungroup the items, add the

calculated item and then regroup the items.

Rows and columns of data in a PivotTable can easily be grouped and it is also possible to group non-

adjacent field items in a row or column.

To group data within a field, select either adjacent or non-adjacent data by holding the <Ctrl> key and

clicking on non-adjacent data or holding the <Shift> key down to select adjacent data. Select PivotTable

Analyse->Group->Group Selection.

To Do: ….

Grouping is quite important to understand and use when working with PivotTables. There are

many different ways in which you can group data in a PivotTable. Using the PivotTable created in the

workbook Transactions.xlsx, you will now have some practise at using grouping by doing the following;

Group Selected Items;

• Select two or more items in the PivotTable report that you want to group, either by clicking and

dragging or by holding down CTRL or SHIFT while you click

• Select PivotTable Analyse->Group->Group Selection; give that selection you’ve just grouped a

name

• Repeat this process to group other sections

Group by Date/times;

• Date or time field in the PivotTable report that you want to group

• Select PivotTable Analyse->Group->Group Field

• Enter the first date or time to group in the Starting at box and enter the last date or time to

group in the Ending at box.

• In the By box, click one or more time periods for the groups (to group items by days in the By

box make sure that Days is the only time period selected and then click 7 in the Number of days

box. You can then click additional time periods to group by such as month.)

Group by Numerical items;

• Select the numeric field in the PivotTable Report that you want to group

• Select PivotTable Analyse->Group->Group Field

• In the Starting at box enter the first item to group

• In the Ending at box enter the last item to group

• In the By box, type a number that represents the interval included in each group

Learning Objective Three - Creating Report Filters

So far we have added rows, columns and values to a PivotTable. However there is one area of the

PivotTable field list screen we still need to look at; this is the Report Filter section.

Report filters allow you to;

• Display a different set of values in a report based on a report filter item

• Display each set of values in a report based on a report filter item on a separate worksheet

• To add a field to the Report Filters area, select it from the field list and drag and drop it to the

Report Filter area.

The filter appears at the top left of the pivot table, the

report filter is ‘COMPANY NAME (ALL) ’ which means all

Company Names are visible.

Once you have a report filter added to your PivotTable, you can then click into your PivotTable, select

PivotTable Analyse->PivotTable->Options from your ribbon. In the PivotTable area on the ribbon, click

on the arrow to the right of Options and select Show Report Filter Pages, the dialogue box will now

appear. Select the name of the report filter you want your report pages to contain and select OK.

To Do: ….

Using the PivotTable created in the workbook Transactions.xlsx, use the Report Filter facility to

create separate worksheets for the genre.

To gain more practice at using Report Filters and Filtering out data in your PivotTable try Exercises 3-8,

which will give you the opportunity to try lots of different filtering examples.

Learning Objective Four - Use Slicers to filter data

Slicers are a different way to enable you to filter your PivotTable, similar to the way Filter fields work.

The difference is that slicers offer a more user-friendly interface that enables you to see the current

filter state.

To understand the concept behind slicers, place your cursor anywhere inside

your PivotTable then go up to the Ribbon and select the “PivotTable Analyze”

Tab. Once on the Tab click the Insert Slicer icon

This activates the Insert slicers dialog box, as shown opposite. The idea is to

select the dimensions you want to filter. In this case the Region and Market

slicers are created when you select these dimensions.

After the slicers are created you will see the filtered version

as shown opposite. You can click the filter values to filter

your PivotTable. You can also select multiple values by

pressing the Ctrl key while selecting the other filters

required.

Another advantage with user’s slicers is that each slicer can

be tied to more than one pivot table. In other words any

filter you apply to your slicer can be applied to multiple

pivot tables

To connect your slicer to more than one PivotTable, right click the

slicer and then select PivotTable Connections. This activates the

PivotTable connections dialog box as shown.

Next select the check box next to any pivot table that you want to

filter using the current slicer.

At this point any filter you apply to the Slicer is applied to all the

connected PivotTables. Again slicers have a unique advantage over

Filter Fields in that they can control the filter state of multiple

PivotTables. Filter fields can only control the PivotTable in which they

live

To Do: ….

• Create a variety of Slicers using one of your PivotTables.

• Change the column and width size of your slicer.

• Alter the colour of your slicer so that each slicer looks different.

• Test your slicers work by filtering out the information.

Learning Objective Five - Create calculated fields and calculated items

When you create a PivotTable the fields are taken from those in your range or table of data. Once your

PivotTable is created you may decide that a new field is required and this can be created using a

calculation

When a calculated field is created it will insert a new data field into the PivotTable. This data field does

not exist in the source data but obtains its value from a formula. The new formula can include existing

fields, numbers and other arithmetical operators.

For example, if the PivotTable’s underlying data consists of only three fields, “Week date”, “Sales” and

“Expenses” you will only be able to use those three fields and place them in either the report filter, row,

column or value fields. You may want to calculate the “Profit” (Sales – Expenses) in your pivot table. A

calculated field allows you to do this by creating a customised field which acts as a combination of one

or more fields already existing in the pivot table.

To create a calculated field for profit, the PivotTable below has been created

using the week date field as the row field and the sales and expenses as the

value field. The PivotTable has then been grouped by months on the week

date.

To create a calculated field click into the PivotTable and select PivotTable

Analyse->Fields, Items, & Sets->Calculated Field from the ribbon.

You would then complete the calculated field dialog with the formula you require for the calculation to

take place.

To Do: ….

• Using the workbook Staff Finance.xlsx create a PivotTable ensuring that you have the following

Name, Sales and Expenses.

• Now create a calculated field to work out the amount of profit per member of staff.

• Look at other ways you can use calculations, choose one of your other PivotTables and try out

some calculations

• For additional practice use Exercise 11

Learning Objective Six - Create and work with Pivot Charts

A chart can easily be created from your PivotTable data. When you create a chart from a PivotTable it is

worth remembering that the chart will always match the data shown in the PivotTable.

A pivot chart can be created on the same worksheet or a separate chart sheet. To create a chart from a

PivotTable filter your data to display the data you require in your chart.

Click within your PivotTable and select PivotTable Analyse->Tools->PivotChart. The Insert Chart dialog

will appear.

To move the chart to a different worksheet select the chart then select PivotChart Tools, Design,

Location and then Move Chart. Select New Worksheet from the Move Chart dialog box.

On the newly created chart use the dropdown arrows next to field names, these will filter your chart

data or you can filter the data in your PivotTable and the chart will immediately update.

To Do: ….

Now have a look at this exercise; Exercise 9 – Creating Charts, is a good starting point to practice

working with Pivot Charts.

Try using some of the data from one of the other PivotTables you have created and work with Charts to

see what kind of results you get.

Also use the filter options on the Pivot Charts to see how the whole dimensions of the chart and data

change.

Learning Objective Seven - Creating PivotTables using external data

In the previous examples the PivotTables were created from an existing spreadsheet, populated with

data. It is also possible to create and link a PivotTable or Pivot Chart to an external data source, for

example a database file.

When data in the external source changes, the changes are

reflected in the spreadsheet PivotTable when the data is

subsequently refreshed.

To create a PivotTable from an external data source you will

first require to open a blank worksheet. Select Insert,

PivotTable and the Create PivotTable dialog box will appear.

See opposite;

In the Create PivotTable window select Use an external data

source. Choose Existing Worksheet to place the new

PivotTable in your new worksheet, cell A1, and click on Choose

Connection.

In the Existing Connections window, click the Browse for

More… button.

The Select Data Source dialog box will appear. Navigate to the

external database file and select Open.

The Select Table dialog will appear. This is where you select

either a table or a query from the list and select OK.

The spreadsheet will now link to the

external data source and will display a

blank PivotTable, ready for fields to be

inserted and a PivotTable created.

When the source data changes it needs

to be refreshed manually. To refresh the

source data when it changes, Select

PivotTable Analyse->Data->Refresh on

the ribbon to update the data in your

PivotTable.

To Do: ….

Exercise 14 – Create a PivotTable using an Access Query. Use this exercise to enable you to practice

how to create a PivotTable from external sources (this would work with another Excel Spreadsheet,

Access Table…)

Additional Exercises;

Both Exercises 16 & 17 are consolidation tasks, using multiple PivotTables and multiple Slicers, have a go

at these exercises and see the results that you get.

Further information

Getting extra help

Course Clinics

The IT Learning Centre offers bookable clinics where you can get pre- or post-course advice. Contact

us using [email protected]x.ac.uk.

Study Videos from Molly

Molly is our collection of self-service courses and resources. This includes providing LinkedIn Learning

video-based courses free to all members of the University. Visit skills.it.ox.ac.uk/molly and sign in

with your Single Sign-On (SSO) credentials.

Some courses recommend pre- and/or post-course activities to support your learning. You can watch

these online videos anywhere, anytime, and even download them onto a tablet or smartphone for

off-line viewing.

If you need a quiet place to work through learning activities away from distractions, the IT Learning

Centre offers ‘quiet’ sessions where you can book a place. These are scheduled frequently during

normal term times.

About the IT Learning Portfolio online

Many of the resources used in the IT Learning Centre courses and workshops are made available as

Open Educational Resources (OER) via our Portfolio website at skills.it.ox.ac.uk/it-learning-portfolio.

Find the pre-course activity for this course in the IT Learning Portfolio: visit

skills.it.ox.ac.uk/it-learning-portfolio and search for “Spreadsheets: Good practice with pivot tables

(Activity)”.

About the IT Learning Centre

The IT Learning Centre delivers over 100 IT-related teacher-led courses, which are provided in our

teaching rooms and online, and we give you access to thousands of on-line self-service courses

through Molly (powered by LinkedIn Learning).

Our team of teachers have backgrounds in academia, research, business and education and are

supported by other experts from around the University and beyond.

Our courses are open to all members of the University at a small charge. Where resources allow, we

can deliver closed courses to departments and colleges, which can be more cost-effective than

signing up individually. We can also customize courses to suit your needs.

Our fully equipped suite of seven teaching and training rooms are usually available for hire for your

own events and courses.

For more information, contact us at [email protected]c.uk

About IT Customer Services

The IT Learning Centre is part of the Customer Services Group. The group provides the main user

support services for the department, assisting all staff and students within the University as well as

retired staff and other users of University IT services. It supports all the services offered by IT Services

plus general IT support queries from any user, working in collaboration with local IT support units.

The Customer Services Group also offers a data back-up service; an online shop; and a

PC maintenance scheme. Customer Services is further responsible for desktop computing services –

for staff and in public/shared areas – throughout UAS and the Bodleian Libraries.

Spreadsheets:

Good practice with Pivot Tables

Graham Addis

[email protected]k

Resources for your learning

• Activities for you to practice today

In the course handbook

Work at your own pace!

Be selective

• Videos with today’s topics in Molly

• Follow-up work

Continue with exercises after the session

Bookable Course Clinics later

Course Outline..

 What are Pivot Tables

 Create a Pivot Table

 Formatting

 Expand/Collapse data

 Filter, Sort, Group

 Report Filters

 Using Slicers

 Pivot Charts

 Calculated Fields

 Linked Data (Access)

 Multiple Pivot Tables

and Slicers

Introduction to PivotTables

What is a PivotTable..

How to create a PivotTable ..

You first need to select a cell in your table

of data, the click on Insert, PivotTable

You then need to decide whether you

want you PivotTable in the existing

worksheet or in a New Worksheet

PivotTable Screen ..

PivotTable

Ribbon

Field List

PivotTable

Areas

Data

Formatting a PivotTable..

 Formatting values in a Pivot Table

 Change the name of row and column

headings

 Expand and collapse Pivot Table data

 Filter, Sort and Group Pivot Table data

 Clear filters

Grouping data in a PivotTable

Group by;

 Text

 Date

 Numerical Value

Report Filters ..

By using the Report Filter Pages – you can

create multiple reports depending on the

filters you choose to add to your Pivot Table

Find the resources for the workshop

in our IT Learning Portfolio

Download the files (and more)

from the IT Learning Portfolio at

https://skills.it.ox.ac.uk/

it-learning-portfolio

Find the resources for the workshop

in our IT Learning Portfolio

Download the files (and more)

from the IT Learning Portfolio at

https://skills.it.ox.ac.uk/

it-learning-portfolio

Practical Session 1

Learning

Objective

Workbook Worksheet

One Transactions.xlsx Data

Two Transactions.xlsx (PivotTable created in Learning

Objective One)

Three Transactions.xlsx (PivotTable created in Learning

Objective Two)

Extra Exercises are located in a separate handout.

Creating and using Slicers ..

Multiple PivotTables and Slicers ..

Creating a Pivot Chart ..

Using Calculated Fields ..

Working with linked data ..

Practical Session 2

Learning

Objective

Workbook Worksheet

Four Transactions.xlsx (PivotTable created in Learning

Objective Three)

Five Staff Finances.xlsx Staff Sales

Six SalesEx6 Completed.xlsx Pivot Sales

Seven (New workbook)

Data source: OUCSWorks.accdb

(Blank Worksheet)

Extra Exercises are located in a separate handout.

Find the resources for the workshop

in our IT Learning Portfolio

Download the files (and more)

from the IT Learning Portfolio at

https://skills.it.ox.ac.uk/

it-learning-portfolio

This presentation is made available by Graham Addis

under a Creative Commons licence:

Attribution-NonCommercial-ShareAlike

CC BY-NC-SA

[email protected]k