Chapter 3 — Excel: Data Summary with Pivot Tables
📚 Chapter Overview
Turning thousands of rows into a single summary is the analyst's superpower. In this chapter, you will master Pivot Tables to calculate totals, averages, and percentages across different categories in seconds.
Learning Objectives:
- Create a Pivot Table from a raw dataset.
- Use Rows, Columns, and Values fields.
- Apply "Slicers" for interactive filtering.
3.1 Pivot Table Fundamentals
Part 1 — Definition
A Pivot Table is an interactive tool that summarizes large amounts of data. It doesn't change the source data; it creates a new "Summary" view. You can drag and drop fields to instantly see totals by region, date, or category.
Part 2 — The 4 Quadrants
- Filters: High-level filtering for the whole table.
- Columns: Data displayed horizontally.
- Rows: Data displayed vertically.
- Values: The numbers being calculated (Sum, Average, Count).
Part 3 — Example
Problem: Find total sales by "Product Category".
- Select data → Insert → Pivot Table.
- Drag Category to Rows.
- Drag Sales Amount to Values.
Result: A small table showing each category and its total revenue.
Part 4 — Video
Video: Coming Soon
3.2 Pivot Charts & Slicers
Part 1 — Definition
A Pivot Chart is a visual representation of a Pivot Table. Slicers are visual buttons that allow you to filter Pivot Tables and Charts with a single click, making your reports interactive.
Part 2 — Process
- Click inside Pivot Table → Insert → Slicer.
- Select "Region".
- Click "East" to instantly see only Eastern sales.
Part 3 — Example
Problem: Create a "Mini Dashboard" in Excel.
Combine a Bar Chart showing sales by month and a Slicer for "Product Category". This allows a manager to see monthly trends for specific products at the click of a button.
Part 4 — Video
Video: Coming Soon
🏆 Chapter Challenge
Challenge Objective
Analyze a "Sales Data" file to identify the best-performing region.
Requirements
- Calculate Total Sales and Total Profit by Region.
- Add a Slicer for "Order Year".
- Identify which region had the highest profit in 2023.
Step-by-Step Process
- Insert a Pivot Table.
- Put Region in Rows, Sales and Profit in Values.
- Insert Slicer for Year.
- Select "2023" and observe the results.
Expected Deliverables
- The Excel file with the Pivot Table and Slicer.