Pivot Tables: Creating and Analyzing Data

The Problem: You have a massive list of every sale made at a school fair, but you need to know the total profit per house and the most popular item without writing fifty different SUMIF formulas.

The Solution: A Pivot Table—a tool that "pivots" (rotates) data to look at it from different angles. It summarizes, analyzes, explores, and presents your data.

Clean Data Rules

• Every column must have a unique header - no duplicates or blank headers
• No empty rows or columns within the data set - Excel might think your data ends at the blank row
• No "totals" at the bottom - the Pivot Table will calculate totals for you
• Consistent data types - don't mix numbers and text in the same column

The Location

Select your data range and go to Insert > Pivot Table. Excel will automatically detect your data range if your cursor is inside it.

The Destination

Choose whether to put the report on a New Worksheet (recommended for SBAs) or the Existing Worksheet (if you want it next to your data).

The Pivot Table Fields Pane

Understanding the four "Drop Zones":

• Filters: Limits the data shown (e.g., only show "Term 1")
• Columns: Spreads data horizontally
• Rows: Lists data vertically (usually your main categories)
• Values: The numbers you want to calculate (Sum, Count, Average)

The Magic of Drag-and-Drop

Moving a field from "Rows" to "Columns" instantly changes the entire report layout. Want to see products as columns instead of rows? Just drag and drop!

Changing Calculations

Right-click on a value field to change its calculation:

• SUM - Total sales amount
• COUNT - Number of sales transactions
• AVERAGE - Average price per sale
• MAX/MIN - Highest/lowest sale value

The Concept

A "Slicer" is a big, clickable button that acts as a filter for your Pivot Table. It's more user-friendly than the tiny drop-down arrows.

Why Use Them

• Easy to use - Just click to filter
• Visual feedback - Shows what's currently selected
• Professional look - Makes your spreadsheet look like a dashboard
• Multiple selections - Ctrl+Click to select multiple items

The "Snapshot" Rule

Pivot Tables do not update automatically when you change the original data. They're a snapshot of your data at the moment you created them.

The Solution

• Right-click the Pivot Table and select Refresh
• Use the Refresh All button in the Data tab for multiple tables
• Set up Data Connections for automatic refreshing (advanced)

Syllabus Objective

"Use Pivot Tables to summarize and analyze data." This includes creating, modifying, and interpreting Pivot Tables for different scenarios.

Practice Question

"Which Pivot Table 'zone' would you drag a field to if you wanted to see a list of student names appearing vertically?"

Terminology Check