Pivot Tables

Who this is for

Data Analysts and learners who already work with spreadsheets and want to summarize messy datasets fast. You will build pivots that answer business questions in minutes.

Prerequisites

• Comfort entering data in a spreadsheet (Excel or Google Sheets)
• Basic formulas (SUM, COUNT, AVERAGE)
• Clean headers in row 1 and no completely blank rows

Why this matters

Real analyst tasks that pivots solve in minutes:

• Summarize sales by month, region, product, and rep
• Spot top/bottom performers and trends
• Calculate contribution to totals and growth
• Build interactive slices of data for stakeholders

Concept explained simply

A pivot table groups rows and aggregates numbers so you can see totals and patterns without writing formulas. You drag fields into Rows, Columns, Values, and Filters to reshape the same data in different ways.

Mental model

• Rows: Categories down the side (e.g., Region, Product)
• Columns: Categories across the top (e.g., Month, Channel)
• Values: What you compute (e.g., Sum of Sales, Count of Orders)
• Filters/Slicers: What to include or exclude (e.g., Year=2025)

Core pivot moves you will use daily

• Group Date by Month/Quarter/Year
• Sort largest to smallest by value
• Filter and Slicer to focus the view
• Value Field Settings to change Sum/Count/Avg and number format
• Show Values As to show % of total or period-over-period change
• Calculated Field to derive new metrics (e.g., Profit = Sales * 0.3)

Worked examples

Example 1 — Sales by Region and Month

1. Insert Pivot Table from your data range.
2. Drag Region to Rows.
3. Drag Date to Columns and group by Months.
4. Drag Sales to Values, set to Sum and currency format.
5. Sort Regions by Grand Total (descending).

Example 2 — Top 5 Products by Revenue

1. Rows: Product
2. Values: Sum of Sales
3. Sort by Sum of Sales (Largest to Smallest)
4. Value Filter on Product: Top 5 by Sum of Sales

Example 3 — Contribution and Month-over-Month Change

1. Rows: Region
2. Columns: Month (group Date)
3. Values: Sum of Sales
4. Duplicate the Values field and set the second to Show Values As → % of Column Total
5. Duplicate the Values field again and set to Show Values As → Difference From (Base field Month, Base item Previous)

Exercises (hands-on)

Use the same dataset for both exercises. Copy the CSV below into a new sheet starting at A1. Ensure headers are in row 1.

OrderID,Date,Region,Product,Rep,Units,Unit_Price,Sales
1001,2025-01-05,East,Keyboard,Alex,10,20,200
1002,2025-01-08,West,Mouse,Ben,15,10,150
1003,2025-01-12,North,Monitor,Cara,5,100,500
1004,2025-01-15,South,Keyboard,Dan,8,20,160
1005,2025-02-02,East,Mouse,Alex,20,10,200
1006,2025-02-06,West,Monitor,Ben,3,100,300
1007,2025-02-11,North,Keyboard,Cara,12,20,240
1008,2025-02-20,South,Mouse,Dan,25,10,250
1009,2025-03-03,East,Monitor,Alex,2,100,200
1010,2025-03-07,West,Keyboard,Ben,6,20,120
1011,2025-03-15,North,Mouse,Cara,10,10,100
1012,2025-03-19,South,Monitor,Dan,4,100,400

• Data is pasted to A1 with headers exactly as shown
• No blank rows, numeric columns are true numbers
• Date column recognized as date (not text)

Exercise 1 — Region by Month with % of Column Total

1. Insert a pivot table from the full range.
2. Rows: Region
3. Columns: Date (group by Month)
4. Values: Sum of Sales (currency format)
5. Sort Regions by Grand Total (descending)
6. Duplicate the Values field and set it to Show Values As → % of Column Total (percentage format)

Exercise 2 — Average Unit Price by Product and Region + Calculated Profit

1. New pivot from the same range.
2. Rows: Product
3. Columns: Region
4. Values: Average of Unit_Price (2 decimals)
5. Add Calculated Field Profit = Sales * 0.3 (assume 30% margin), Values: Sum of Profit
6. Optional: Filter Date to Feb and Mar only to compare in-season performance

Common mistakes and self-check

• Using Average when rows are line items per order: prefer calculated field SUM(Sales)/Distinct Count(OrderID)
• Date not grouping: the Date column is text; convert to a real date
• Percent of total confusion: choose % of Column Total when columns are periods; % of Grand Total for overall share
• Dirty headers: merged cells or duplicate header names break pivots; keep one clean header row
• Forgetting number formats: always set currency/percent formats for clarity

Practical projects

• Monthly Sales Dashboard: Region x Month pivots, a pivot chart, and slicers for Product and Rep
• Product Health Report: Top N products, % contribution, and MoM change with conditional formatting
• Support Tickets Snapshot: Count by Category and Priority, % of total, and trend by week

Learning path

1. Refresh data hygiene: headers, types, no blank rows
2. Pivot basics: Rows/Columns/Values/Filters and number formats
3. Date grouping and sorting
4. Show Values As: % of total, Difference From, Running Total
5. Calculated Fields and Distinct Count
6. Pivot charts and slicers for interactive dashboards
7. Extend to SQL for larger datasets and repeatability

Mini challenge

Using the provided dataset:

• Which region has the highest sales in March?
• What is West region’s change from Feb to Mar (absolute and %)?
• Which product has the highest total profit (30% margin)?

Next steps

• Build a pivot chart from your best pivot
• Add slicers for Month and Region
• Practice calculated fields for KPIs like margin, AOV, and conversion rates

Quick Test info

The quick test is available to everyone for free. If you log in, your progress and score will be saved.