Pivot tables and pivot charts

excel
intermediate
Excel skill-builder
Published

June 13, 2024

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:

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

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
    Insert pivot table
  5. Switch to the new worksheet containing your new (blank) pivot table
    New 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
    Pivot table fields
  • 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
    Pivot table pages

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

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
    Conditional formatting rules

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) Row sum formula
  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