PivotTables & PivotCharts in Depth

Excel Advanced

Chapter 3 · PivotTables & PivotCharts in Depth

Excel Fundamentals Chapter 9 built a PivotTable from four boxes — Rows, Columns, Values, Filters. That's enough to summarize data, but a PivotTable can do considerably more: calculate a brand-new figure from other summarized fields, group numbers into custom bins, and be controlled with clickable buttons instead of dropdown menus. This chapter covers all of it, plus the PivotChart — a chart wired directly into a PivotTable's own data.

Calculated Fields: Adding a Formula Inside the PivotTable

A calculated field (PivotTable Analyze → Fields, Items & Sets → Calculated Field) creates a brand-new field, computed from a formula referencing the PivotTable's other fields, that then behaves like any other field you can drag into Values:

Calculated field named "Profit Margin", formula:
=(Sales - Cost) / Sales

The field names inside a calculated field's formula (Sales, Cost) refer to the source data's own columns — but critically, the formula is applied to the already-summarized totals for whatever Row/Column grouping is currently in place, not to each individual source row before it's aggregated. For a region with 50 transactions, "Profit Margin" is calculated once, from that region's TOTAL Sales and TOTAL Cost — it is not 50 separate row-level margins averaged together afterward.

Grouping Revisited: Custom Numeric Bins

Excel Fundamentals Chapter 9 covered grouping dates into Months and Quarters. The same right-click → Group also works on numeric fields placed in Rows, letting you specify a start, an end, and an interval to build even-sized bins automatically:

Right-click a numeric Row field (e.g. transaction Amount) → Group:
Starting at: 0
Ending at:   500
By:          100
→ produces bins: 0-99, 100-199, 200-299, 300-399, 400-499, 500+

This turns a field with too many individual unique values to usefully summarize (every distinct transaction amount) into a manageable handful of ranges — the PivotTable equivalent of a histogram, built with no formula at all.

Slicers: Clickable Filter Buttons

Insert → Slicer replaces the plain dropdown in the Filters area with a panel of visible buttons — one per unique value — that filter the PivotTable the instant you click them. Multiple values can be selected at once (Ctrl+click, or Shift+click for a range), and the currently-inactive values visibly grey out, making the active filter state obvious at a glance in a way a closed dropdown never shows.

A single Slicer can also control more than one PivotTable at once: right-click the Slicer → Report Connections, and tick every PivotTable that should respond to it. Clicking one Category button then filters every connected PivotTable (and any PivotChart built from them) simultaneously — a genuinely different capability from the Filters area, which only ever affects the one PivotTable it belongs to.

Timelines: A Specialized Slicer for Dates

Insert → Timeline is only offered when a PivotTable has an actual date field available. It provides a draggable horizontal range selector, with buttons to switch its granularity between Days, Months, Quarters, and Years — dragging the selected range instantly filters the connected PivotTable(s) to that period, without needing to open Chapter 9's own date-grouping dropdown each time.

PivotCharts: Charts Wired Directly to a PivotTable

Insert → PivotChart (or PivotTable Analyze → PivotChart) creates a chart that reads its data straight from a PivotTable, rather than from a fixed range the way Excel Fundamentals Chapter 8's charts do. A PivotChart gets its own field buttons directly on the chart, letting you rearrange which fields appear without touching the underlying PivotTable at all — and it automatically responds to any Slicer or Timeline connected to its source PivotTable, since it's reading the exact same live summary, not a separate copy of it.

Filtering method	Visual	Can control multiple PivotTables at once
Filters area (Excel Fundamentals Ch.9)	A closed dropdown — current selection not visible until opened	No
Slicer	Clickable buttons, active/inactive state always visible	Yes, via Report Connections
Timeline	A draggable date range, with granularity buttons	Yes, via Report Connections — dates only

Slicers, Timelines, and PivotCharts all still rely on the same underlying refresh

A Slicer, a Timeline, and a PivotChart are all different ways of viewing or controlling one underlying PivotTable's cached data — none of them bypass Excel Fundamentals Chapter 9's core rule that a PivotTable never updates on its own. After the source data changes, a single Refresh brings the PivotTable, every Slicer and Timeline connected to it, and every PivotChart built from it, all current together in one step.

A calculated field can never compute a true row-by-row average

Because a calculated field's formula runs on already-summarized totals, there is no way to make one compute something like "the average of each individual row's profit margin" — it can only ever compute "(total Sales minus total Cost) divided by total Sales" for whatever grouping is showing. If a genuine row-level calculation is needed (average margin computed per transaction, then averaged), that column has to be calculated in the SOURCE data itself — e.g. an Excel Table calculated column (Chapter 7's [@ColumnName]) — before it ever reaches the PivotTable, not invented afterward as a calculated field.

Hands-On Exercises

Exercise 1

A PivotTable summarizes Sales and Cost by Product. Create a calculated field named "Profit" that computes Sales minus Cost. For a product with total Sales of 5000 and total Cost of 3200 across all its transactions, what does the Profit field show? Explain whether this is the sum of each individual transaction's own profit, or something else, and why the distinction matters.

📄 View solution

Exercise 2

A numeric Age field is placed in Rows with 200 unique values, making the PivotTable unreadably long. Describe exactly how to group it into 10-year bins from 0 to 100, and explain what the resulting Row labels would look like.

📄 View solution

Exercise 3

Two PivotTables (one summarizing Sales by Category, one summarizing Sales by Region) are both built from the same Transactions table. Describe how to set up a single Category Slicer that filters BOTH PivotTables at once when clicked, and explain why a plain Filters-area dropdown on just one of the PivotTables couldn't achieve the same result.

📄 View solution

Chapter 3 Quick Reference

Calculated field — a new field computed from other fields' TOTALS, not from individual source rows
Numeric grouping — right-click → Group with Starting at / Ending at / By, building even bins from a field with too many unique values
Slicer — clickable filter buttons; can connect to and filter multiple PivotTables via Report Connections
Timeline — a draggable date-range slicer with Day/Month/Quarter/Year granularity buttons
PivotChart — a chart reading live from a PivotTable, inheriting any connected Slicer/Timeline automatically
All of the above still require a manual Refresh after the source data changes — none of them bypass Excel Fundamentals Chapter 9's core refresh rule