Chapter 10: PivotTables & PivotCharts

PivotTables & PivotCharts.

This is the chapter that turns raw data (even thousands of rows) into instant summaries, trends, comparisons, and interactive reports — without writing complex formulas every time. PivotTables are used everywhere: sales analysis, finance reports, HR dashboards, inventory tracking, school results — basically any time you need to answer questions like “What sold best last month in Mumbai?” or “How did average price change over time?” in seconds.

I’ll explain everything very slowly, step by step, like a patient teacher sitting next to you with your tea shop sales data open on screen. We’ll continue using our TeaSales table from previous chapters (Date, Item, Qty, Price, Total, City) — assume you have 100+ rows of daily sales data from Jan 2026 onward. All steps are current for Excel for Microsoft 365 / 2021+ / 2025–2026 (no major breaking changes in PivotTables recently; features like slicers, timelines, calculated fields remain the same, with minor UI tweaks and better spill behavior in some Insider builds).

Let’s turn your sales log into professional insights!

1. Creating PivotTables from Data

Best practice first: Your data should be in an Excel Table (from Chapter 6) — it auto-expands and makes refresh easier.

Step-by-step to create a PivotTable:

1. Click anywhere inside your TeaSales table (or select the whole data range).
2. Go to Insert tab → Tables group → PivotTable button (or Alt + N + V).
3. In the dialog:
   • Table/Range: should auto-fill with your table name (e.g., TeaSales)
   • Choose where to place: New Worksheet (recommended for beginners)
   • Click OK.
4. A new sheet appears with:
   • Empty PivotTable area on left
   • PivotTable Fields pane on right (very important!)

Basic first PivotTable – Total Sales by Item:

1. In PivotTable Fields pane:
   • Drag Item to Rows area
   • Drag Total to Values area → It defaults to Sum of Total (perfect!)
2. Done! You see:
   • List of items (Chai, Coffee, Biscuit…)
   • Total sales amount next to each

Refresh when data changes:

• Right-click anywhere in PivotTable → Refresh (or Data tab → Refresh All — updates all PivotTables)

Pro tip: If you add new rows to the source table → right-click PivotTable → Refresh → it includes them automatically (because source is a Table).

2. Grouping Data, Calculated Fields

A. Grouping – Combine items into categories (dates by month/quarter, numbers into bands)

Example 1 – Group dates by Month & Year:

1. Drag Date field to Rows area (below Item if you want nested).
2. Right-click any date in the PivotTable (e.g., 01-Jan-26) → Group
3. In Grouping dialog: check Months and Years → OK → Dates collapse into Jan-2026, Feb-2026, etc.

Example 2 – Group sales amounts into bands (custom grouping):

1. Drag Total to Rows (shows individual sales — not useful)
2. Right-click one value → Group → set Starting at 0, Ending at 5000, By 1000 → OK → Groups like 0–999, 1000–1999, etc. (great for price banding analysis)

B. Calculated Fields – Add your own formulas inside the PivotTable (no need to add columns in source data)

Example – Profit (assume 40% profit margin):

1. Click anywhere in PivotTable → PivotTable Analyze tab (or PivotTable Tools > Analyze) → Fields, Items & Sets → Calculated Field
2. Name: Profit
3. Formula: =Total*0.4 (double-click Total from field list to insert it)
4. Click Add → OK
5. Drag new Profit field to Values area → now shows profit per item/month!

Another example – Average Price per Item:

• Calculated Field: AvgPrice = Total / Qty
• Drag to Values → format as currency

Tip: Calculated fields work on summarized data — good for ratios like profit %, avg price. For row-by-row calcs (e.g., tax per transaction), better add column in source.

3. Slicers and Timelines

These make your PivotTable interactive like a dashboard — no more digging in filter dropdowns!

A. Slicers – Visual buttons to filter

1. Click inside PivotTable → PivotTable Analyze tab → Insert Slicer
2. Check fields: City, Item, Date (if not grouped yet)
3. Click OK → floating buttons appear (e.g., Mumbai, Navi Mumbai…)
4. Click Mumbai → PivotTable instantly shows only Mumbai sales!
5. To connect one slicer to multiple PivotTables:
   • Right-click slicer → Report Connections → check all relevant PivotTables

B. Timelines – Special slicer for dates (looks like a slider)

1. Click inside PivotTable → PivotTable Analyze → Insert Timeline
2. Choose Date field → OK
3. Timeline appears → drag handles or click months/years/quarters → Filter by Jan-Feb 2026 → all connected PivotTables update

Beautiful combo: Place slicers (City, Item) on top + Timeline below → users click to explore data instantly.

4. PivotCharts and Dashboards Basics

PivotChart = chart linked to PivotTable (change PivotTable → chart updates automatically)

Create one:

1. Click inside PivotTable → PivotTable Analyze → PivotChart button (or Insert tab → PivotChart)
2. Choose chart type (e.g., Clustered Column) → OK
3. Chart appears — linked to current PivotTable fields

Example dashboard setup:

1. Create PivotTable 1: Sales by Item (columns)
2. Create PivotChart 1: Column chart from above
3. Create PivotTable 2: Sales by Month (rows = grouped Date, values = Sum Total)
4. Create PivotChart 2: Line chart showing trend
5. Add slicers: City, Item
6. Add Timeline: Date
7. Arrange on one sheet: charts side-by-side, slicers/timeline at top → Click Mumbai + Jan 2026 → both charts update to show only Mumbai Jan sales!

Customization tips (same as regular charts):

• Chart Design tab → add titles, data labels, change colors
• Move legend, adjust axes
• Right-click chart → Move Chart → to new sheet for full dashboard

Refresh everything: Data tab → Refresh All (updates all PivotTables + charts)

Quick Summary Table – Chapter 10

Homework / Practice (Do This Today!)

Use your TeaSales table (add more rows if needed — 50+ entries):

1. Create PivotTable: Rows = Item, Values = Sum of Total → see top sellers
2. Add Calculated Field: Profit = Total * 0.4 → drag to Values
3. Group Date field → by Months & Years
4. Insert Slicer for City and Item
5. Insert Timeline for Date
6. Create PivotChart (Column) from the PivotTable
7. Make a second PivotTable: Rows = grouped Date, Values = Average of Qty
8. Create Line PivotChart for trend
9. Arrange on one sheet → test clicking slicers/timeline → watch magic!