Learning Objectives

  • Decide when a pivot table beats manual SUMIF sheets
  • Build pivots with Rows, Columns, Values, and Filters
  • Change value settings (Sum, Count, Average, % of total)
  • Create pivot charts and interactive Slicers
  • Refresh pivots after source data changes

Sample file: sales_data_large.csv

When to Use a Pivot Table

Use pivots when you need fast answers like:

  • Total sales by Region
  • Average units by Product
  • Monthly trend from a Date field
  • Count of orders per Salesperson

Prerequisites: clean headers, one row per record, no totals row mixed into the source.

Step-by-Step: Create a Pivot

  1. Click any cell in the Sales table.
  2. Insert → PivotTable → New Worksheet. Name the sheet Pivot.
  3. Drag fields:
Area Field
Rows Region
Values Sum of Sales Amount
Values (again) Sum of Units Sold
  1. Format Sales Amount as currency / accounting.
  2. Optional: add Product under Region for a hierarchy.

Change value settings

Right-click a value cell → Value Field Settings:

  • Summarize by Average / Count
  • Show Values As → % of Grand Total

Pivot Charts

  1. Select the pivot → PivotTable Analyze → PivotChart.
  2. Clustered column for region comparison; pie for share of total.
  3. Chart title: Sales by Region.
  4. Keep the chart linked to the pivot (filtering the pivot filters the chart).

Slicers (interactive filters)

  1. Click the pivot → PivotTable Analyze → Insert Slicer.
  2. Tick Region (and/or Product).
  3. Click a slicer button to filter; use multi-select if needed.
  4. Clear filter with the funnel icon on the slicer.

Slicers appear in Model Lab Test Set B — practise them here so the timed test is familiar.

Group Dates (optional)

If you have a Date field:

  1. Drag Date to Rows.
  2. Right-click → Group → Months / Quarters / Years.
  3. Build a monthly trend line chart from the grouped pivot.

Refresh and Integrity

  • After editing source rows: right-click pivot → Refresh.
  • If you added columns, change PivotTable source range (or use an Excel Table so the range grows).
  • Never type manually inside pivot cells — values will be overwritten on refresh.

Practice Task

From sales_data_large.csv:

  1. Pivot: Region × Sum of Sales Amount + Average Units
  2. Pivot chart with a clear title
  3. Slicer on Region; screenshot or note the West-only total
  4. Second pivot: Product ranking (sort Values largest to smallest)
  5. Write three bullet insights for a Dashboard sheet

📚 Continue to: Linking Workbooks