Learning Objectives

  • Understand when to use pivot tables
  • Create pivot tables from data
  • Customize pivot table fields and layout
  • Create pivot charts
  • Analyze data using pivot tables

Step-by-Step Guide

1) When to Use Pivot Tables

Use pivot tables when dataset is large and you need fast summaries by category, date, or region without writing complex formulas.

2) Create a Pivot Table

  1. Select clean tabular data with headers.
  2. Go to Insert → PivotTable.
  3. Choose new worksheet.
  4. Drag fields to Rows, Columns, Values, Filters.

3) Customize Fields and Layout

  • Change value summary (Sum, Count, Average)
  • Group by month/quarter/year
  • Show values as % of total
  • Apply report layout and number formatting

4) Create Pivot Charts

  1. Select pivot table.
  2. PivotTable Analyze → PivotChart.
  3. Use chart type matching question (bar for comparison, line for trend).

5) Analyze and Interpret

  • Add slicers for interactive filtering
  • Compare segments quickly
  • Validate totals with source data

Best Practices

  • Remove blank rows before pivoting
  • Avoid merged cells in source data
  • Refresh pivot after source updates

Practice Task

From a sales dataset, create:

  • Revenue by region
  • Monthly trend chart
  • Top 5 products by sales
  • Slicer by salesperson
  • One insight paragraph summarizing findings

📚 Continue to: Cell Referencing