Pivot tables and pivot charts
excel
intermediate
Excel skill-builder
This is a session in our intermediate-level Excel skill builder course. This consists of five practical training sessions, designed to be taken together, that are aimed at helping users with some prior Excel experience build and consolidate their skills. The sessions are:
- Excel foundations 1
- Excel foundations 2
- Lookups in Excel
- Excel programming
- Pivot tables and pivot charts (this session)
Session materials
Previous attendees have said…
- 21 previous attendees have left feedback
- 100% would recommend this session to a colleague
- 95% said that this session was pitched correctly
Three random comments from previous attendees
- Enjoyable and informative
- Seemed rushed - make the sessions longer perhaps or breakdown into more manageable chunks. Session order seems strange i.e. beginner sessions after intermediate - perhaps that’s an issue of when I found out about the KIND Network.
- Was interesting, showed I knew more than I thought but still useful for little things that make life easier.
Session outline
- Pivot tables
- Pivot charts
- Slicers
- Conditional formatting
Getting started
- files for today:
s05_exercises.xlsx
is a starting-point for the exercises todays05_exercises_final.xlsx
is the end-point for the exercises today - it’s there to help if you get stuck or lost
Task
- Open the sample spreadsheet
s05_exercises.xlsx
and have a look around - Switch to the service use data in the
weekend_sh
sheet
What’s this all for?
- this data is complicated
- we want to find out when our service is busiest
- that’s hard to do manually - we can’t just inspect it by eye
- we’ll need to summarise our data
- two key concerns
- we want to do that safely
- we also want to do that efficiently
- this session (and the previous ones) give some key methods for effectively summarising Excel data
- most importantly: be clear about where your data lives, and where your summaries will live
Pivot tables
- the key summary tool in Excel
- making a new pivot table is easy
- and will give us an answer to our summary question quickly
Task
- Open the
s05_exercises.xlsx
file - Go to the
weekend_sh
worksheet - Select the
weekend_service
table (click inside it andCtrl
+a
) - Press
Alt
,N
,V
,T
(or from the ribbonInsert >> PivotTable
), and click OK to insert a pivot table - Switch to the new worksheet containing your new (blank) pivot table
Adding data to a pivot table
- next, we need to tell the pivot table which data we want to summarise
- we’ll use the PivotTable Fields interface to control this
- we want to find the busiest days for each quarter for each site, so we:
- drag the three
site_
columns to the Values field - then, using the dropdown, change the Value Field Settings to Max
- (the Columns field should automatically populate with Values)
- drag the date column to the Rows field
- drag the three
Improving our pivot table
- that gives us a simple pivot table, showing us the peak values for our service
- we can also add a PivotChart instantly - click on your pivot table, and press
Alt + F1
- play with the expansion buttons - the PivotChart should update to reflect the way that your PivotTable is currently arranged
PivotTable tips
Note
- Double-click any cell in a PivotTable to see the underlying data
- Group PivotTable items using:
Shift
+Alt
+→
(right arrow) to add items to a groupShift
+Alt
+←
(left arrow) to ungroup
- Add a new calculated field using
Ctrl
+Shift
+=
(equals) - Delete an entire PivotTable using
Ctrl + A
then pressingDel
PivotTable pages
- we can split out parts of a PivotTable to separate worksheets
Task
- Drag the Years item to the Filters field. This should update the PivotTable so that only one year’s data is present at once
- We can also split each year into its own worksheet: find the option in the PivotTable Analyze section of the ribbon - or via
Alt
,J
,T
,T
,P
Slicing
- one slicer can control many PivotTables
Task
- Click within one of your new annual PivotTables
- Add a slicer from the insert menu (or
ALT
,N
,S
,F
) - Select date to slice on
- When the slicer appears, right click and select “Report Connections…” and add the other annual PivotTables
- Now select a couple of months from your slicer, and see the effect on your PivotTables
- if you’re working with dates, you can also use the timeline, which works in exactly the same way. Insert with
ALT
,N
,S
,T
, update the connections, and you can get fine-grained control over which date-ranges contribute to your PivotTable
Conditional formatting
- generally useful for shorter data and simpler summary queries than PivotTables
- duplicated values
- max values
- loads of pre-packed options for conditional formatting
Task
- Switch to the
Conditional formatting
worksheet - From the ribbon, explore some of the pre-packed conditional formats
Conditional formatting rules
- for more sophisticated conditional formatting, formatting rules can be specified and edited using the conditional formatting rules manager
Task
- Add a pre-packed conditional format to your table
- Bring up the conditional formatting rules manager using
ALT
,H
,L
,R
Custom conditional formatting
- again, there are pre-set formatting rules available
- we can also write a custom formula
Task
- To find the max value in a row, use the formula
= A2 = MAX($A2:$E2)
- Try adding extra rules and experimenting with how they interact
Thank yous
I’m grateful to Jennifer Watt, John Mackintosh, Duncan Sage, David Coigach, Michael Robb, Angela Godfrey, and other members of the KIND network for their valuable suggestions and corrections to these training materials