Pivot tables and pivot charts

excel
intermediate
Published

June 13, 2024

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
  • I found it very helpful as I have just started using pivot tables. I found it quite daunting using pivot tables before but now feel more comfortable and confident about using them
  • Great having example data provided so to follow along, content is pitched in a way that I don’t feel out of my depth.
  • Was interesting, showed I knew more than I thought but still useful for little things that make life easier.

Welcome!

  • this session is for 🌶️🌶️intermediate Excel users
  • we’ll do an initial chat and run through, followed by a practical demo
    • you’re welcome to stay for all or part of the session
  • if you want to follow the practical part, you’ll need:
    • Excel, ideally M365 Desktop
    • previous Excel experience (particularly around references, tables, and formulas)
    • the data: guidance / download page / direct / short tinyurl.com/kinddata1
  • it also helps if you have access to the chat. You may need to add youself to the KIND Network Teams channel: tinyurl.com/kindnetwork

Session outline

  • Pivot tables
  • Pivot charts
  • Slicers
  • the session is totally informal
    • cameras on, please
    • chat/question away
    • don’t panic if things go wrong

Why pivot tables?

  • the key summary tool in Excel
  • we often want to summarise long/complex data
  • two key concerns:
    • we want to do that safely
    • we also want to do that effectively
  • pivot tables are safe and effective
    • they keep your analysis out of your data
    • they have lots of powerful tools built-in

Some data

  1. on the 18 week referral target
  2. Copy the data link
  3. Open the Get Data From web tool
  4. Paste in the link

What questions might we ask about that data?

Insert a pivot table

  • on a new sheet
  • ALT, N, V, T

Adding data to a pivot table

  • we build pivot tables from the fields interface

Adding data to a pivot table

  • drag values to fields
  • dropdowns change settings

Improving our pivot table

  • that gives us a simple pivot table
  • 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 warnings

  • this isn’t Excel!
  • warnings about coherence

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

Add board names

Years and months

  • our Month column is a pain
  • we can turn that back into a proper date
  • =date(left([@Month], 4), right([@Month], 2), 24)
  • refresh the pivot table, and update the fields

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