Data Storytelling with Pivot Tables

Introduction

  • Lisa Vigneau, Ed. M. MASCA Conference, 2025

Agenda

  • Introduction
  • Brainstorm
  • Spreadsheet Basics
  • Pivot Table Basics
  • Examples in Action
  • Hacks and Timesavers

What is a Pivot Table?

  • A tool to:
    • Sort data
    • Reorganize data
    • Group data
    • Build lists of unique values
    • Summarize data (Count, Sum, Average)

Data Presentation

  • Data should be:
    • Presented Visually
    • Sorted
    • Arranged
    • Explained
    • Told with a Story

Brainstorming Data Stories

  • Examples of data stories:
    • Admissions outcomes and institutional scholarships given to applicants.
    • Students struggling midterm and in which classes.
    • How alumni are doing in the real world.

Data Sources

  • College admissions data (Scoir, Naviance).
  • School Information System data.
  • Collected data (Surveys, Pre- and Post-test data).
  • Data from outside organizations (College Board, ACT, or EWIS).

Identifying Stories

  • The data may reveal the story itself.

Spreadsheet Basics for Clean Sheets

Keyboard Shortcuts

  • Ctrl + C: Copy
  • Ctrl + V: Paste
  • Ctrl + X: Cut
  • Ctrl + Z: Undo
  • Ctrl + Y: Redo
  • Ctrl + A: Highlight all
  • Ctrl + Shift + V (Sheets)/Paste Special (Excel): Paste unformatted text (Use to move data without formatting).

Good to Know/Best Practices

  • Text to Columns
  • Freeze Panes
  • Unmerge cells
  • Filter
  • Sort
  • No blank rows or columns
  • No data outside of the list
  • Each column should have a header
  • Convert to table
  • See handout for more details

Pivot Table Basics

Pivot Table Components

  • Rows
  • Filters
  • Columns
  • Values (for calculations like Average, Sum, % of Whole)

Example Data Fields

  • Name
  • Gender
  • Grade
  • Ethnicity
  • Current Course
  • Current Teacher
  • Reading Score
  • Math Score
  • Percentile
  • Total Score

Examples in Action

Example 1: Alumni Survey from Google Form

  • Raw data source: Alumni responses linked directly into Google Sheets.
  • Data types: Quantitative and qualitative data.
  • Story: How our graduates were prepared.
  • Story: What kinds of schooling our highest earners achieved.
  • Story: What did we do well?

Example 2: School Management System Download

  • Raw data source: Google sheets download from school management system.
  • Story: Who is struggling, and where.
  • Story: Impact of attendance on midyear grades.
  • Data: Whole school midterm grades for term 1.

Example 3: Scoir Download in Excel

  • Raw data source: Scoir download in Excel.
  • Story: Where can I get in with my GPA?
  • Story: Which colleges gave out the most scholarships and to which GPAs.
  • Data: All outcome data from 2024 (decisions, test optional status, GPA, SAT, application type).

Hacks and Timesavers

Tips

  • Follow @thecheatsheets on Instagram
  • Data Visualization Tips
  • Use "Recommended Pivot Tables" when you start
  • Upload your data into an AI service like ChatGPT to create the pivot table you'd like

Troubleshooting

Common Issues

  • Missing data (blanks in rows or columns)
  • Numbers formatted as text, not numbers
  • Forgetting to customize Values for Sum, Average, or Count
  • Confusing column header names (e.g., “grade” for a course vs. grade in school)
  • Merged cells when importing data

Thank You!

  • Ivigneau@walpole.k12.ma.us