MO-201 · Question #15
In the 'Royalty Analysis' worksheet, add the "Title ID" field as a PivotChart filter. Apply the filter to the chart to display only results for title ID "CO20".
PivotChart Filters in Excel - "Royalty Analysis" Walkthrough --- Overall Goal A PivotChart is linked to a PivotTable. Its filter area lets you slice the entire chart's data by a field's values - without altering the underlying data or the PivotTable's row/column structure. The go
Question
Explanation
PivotChart Filters in Excel - "Royalty Analysis" Walkthrough
Overall Goal
A PivotChart is linked to a PivotTable. Its filter area lets you slice the entire chart's data by a field's values - without altering the underlying data or the PivotTable's row/column structure. The goal here is to add "Title ID" as a filter and then narrow the chart to show only title "CO20".
Step-by-Step Breakdown
1. Navigate to the 'Royalty Analysis' worksheet
You must be on the correct sheet. PivotChart fields are scoped to their own PivotTable - working on the wrong sheet means you're modifying the wrong object entirely.
2. Click on the PivotChart to select it
Clicking activates the PivotChart and surfaces the PivotChart Fields pane (task pane on the right) along with the Chart contextual ribbon tabs. Without selecting the chart, you have no access to its field configuration.
3. In the PivotChart Fields pane, locate "Title ID" and drag it to the Filters zone (or check the box and then move it)
- The Filters zone is distinct from Axis, Legend, and Values zones.
- Placing a field in Filters creates a filter drop-down above the chart (the "Report Filter" area), rather than breaking the data out as a series or axis category.
- If you accidentally dropped it into Axis or Legend, the chart would display every title as a separate category/series instead of giving you a filter control.
4. Use the filter drop-down (labeled "Title ID") that now appears above the chart - select "CO20"
- Click the drop-down arrow on the filter control.
- Uncheck "Select All," then check "CO20."
- Click OK.
This applies the filter: the chart now renders only data rows where Title ID = CO20. The PivotTable behind the chart updates simultaneously.
What Goes Wrong If Steps Are Skipped
| Skipped step | Result |
|---|---|
| Not clicking the chart first | PivotChart Fields pane doesn't appear; you may accidentally edit the PivotTable instead |
| Dragging to Axis/Legend instead of Filters | CO20 becomes one bar/line among all titles - no filtering occurs |
| Adding the field but forgetting to apply the filter value | "Title ID" appears as a filter with "(All)" selected - chart still shows every title |
Memory Tip
Think of it in two distinct phases:
"Add then Apply" - first add the field to the Filters zone (structural change), then apply a value via the drop-down (data change).
Many people skip the second phase, leaving the filter at "(All)" and wondering why the chart didn't change. Always confirm the drop-down reads "CO20," not "(All)," before considering the task complete.
Topics
Community Discussion
No community discussion yet for this question.