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

Step-by-Step Lab Exercise: Sales by Region

Use this dataset (the same one as in Data Management):

Region Salesperson Units Sales (Rs.)
East Ram 12 24,000
West Sita 5 9,500
North Hari 8 16,800
West Gita 15 31,500
South Bikash 3 5,400
East Anita 9 18,900
West Kiran 7 12,600
North Maya 11 22,000
  1. Click inside the data → Insert → PivotTable → New Worksheet.
  2. Drag Region to Rows, Sales to Values, and Units to Values.

  3. Expected pivot:

    Region Sum of Sales Sum of Units
    East 42,900 21
    North 38,800 19
    South 5,400 3
    West 53,600 27
    Grand Total 140,700 70
  4. Right-click a Sales value → Show Values As → % of Grand Total. Expected: West 38.1%, East 30.5%, North 27.6%, South 3.8%.
  5. Change Sum of Units to Average (Value Field Settings). Expected: West average 9 units.
  6. Insert a PivotChart (clustered column) titled “Sales by Region”.
  7. Insert a Slicer for Region and click West: the chart and pivot show only West.
  8. Change Ram’s sales to 30,000 in the source data, then Refresh. Expected: East becomes 48,900 and the grand total 146,700.

Common Errors and Fixes

Problem Likely cause Fix
New data does not appear in the pivot Pivot not refreshed or source range too small Refresh; use an Excel Table as the source so the range grows
Values show “Count of Sales” instead of Sum Some sales cells are text or blank Convert text numbers to numbers; change Summarize by to Sum
Region appears twice (“West” and “West “) Extra spaces or inconsistent spelling in source Clean the source with TRIM or Find & Replace
Typed numbers inside the pivot disappear Pivot cells are recalculated on refresh Never type in a pivot; change the source data
Slicer does not filter the chart Chart made from a normal range, not the pivot Create the chart with PivotChart

Key Takeaways

  • Pivot tables summarise large data quickly with Rows, Columns, Values, and Filters.
  • Value Field Settings change Sum, Count, Average, and % of total.
  • Pivot charts and slicers make reports interactive.
  • Always refresh after changing the source data.

Exam-Style Questions

Viva (short answer)

  1. What is a pivot table?
  2. Name the four areas of the PivotTable Fields pane.
  3. How do you show values as a percentage of the grand total?
  4. What is a slicer?
  5. Why must a pivot table be refreshed?

Lab test tasks

  1. Create a pivot table showing total sales and total units by region from the given dataset.
  2. Show each region’s sales as a percentage of the grand total and sort regions from highest to lowest.
  3. Create a pivot chart with a title and add a slicer for Region; report the total for one region.

📚 Continue to: Linking Workbooks