Pivot tables and pivot charts

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

• .xlsx exercises
• .xlsx exercises (completed)

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 today
  • s05_exercises_final.xlsx is the end-point for the exercises today - it’s there to help if you get stuck or lost

Task

1. Open the sample spreadsheet s05_exercises.xlsx and have a look around
2. 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

1. Open the s05_exercises.xlsx file
2. Go to the weekend_sh worksheet
3. Select the weekend_service table (click inside it and Ctrl + a)
4. Press Alt, N, V, T (or from the ribbon Insert >> PivotTable), and click OK to insert a pivot table
   
5. 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

Improving our pivot table

New 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 group
  • Shift + Alt + ← (left arrow) to ungroup
• Add a new calculated field using Ctrl + Shift + = (equals)
• Delete an entire PivotTable using Ctrl + A then pressing Del

PivotTable pages

• we can split out parts of a PivotTable to separate worksheets

Task

1. Drag the Years item to the Filters field. This should update the PivotTable so that only one year’s data is present at once
2. 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

1. Click within one of your new annual PivotTables
2. Add a slicer from the insert menu (or ALT, N, S, F)
3. Select date to slice on
4. When the slicer appears, right click and select “Report Connections…” and add the other annual PivotTables
5. 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

1. Switch to the Conditional formatting worksheet
2. 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

1. Add a pre-packed conditional format to your table
2. 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

1. To find the max value in a row, use the formula = A2 = MAX($A2:$E2)
2. 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