MO-211 Expert Prep
Module 4 · Lesson 4.8

Create and Modify PivotCharts

Edit on GitHub

A PivotTable gives you a structured, numeric summary of your data. A PivotChart is the visual layer on top — a dynamic chart that is directly connected to a PivotTable and updates automatically when the PivotTable changes. Unlike a standard chart, a PivotChart comes with interactive field buttons and responds to slicers, making it a core tool for building self-service analytical dashboards.

Part 1: Create a PivotChart

Method A — From an Existing PivotTable

  1. Click anywhere inside an existing PivotTable.
  2. Go to the PivotTable Analyze tab > Tools group > PivotChart.
  3. Select a chart type and click OK.

The chart is inserted on the same sheet and is immediately linked to the PivotTable.

Method B — From Raw Data (Creates Both PivotTable and PivotChart)

  1. Click anywhere in your source data range.
  2. Go to Insert tab > Charts group > PivotChart dropdown > PivotChart.
  3. Confirm the data range and destination (new or existing worksheet).
  4. Click OK.

Excel creates a blank PivotTable and a blank PivotChart simultaneously. Drag fields into the PivotTable field areas — the chart updates in real time.

TIP
When created from raw data, the PivotTable and PivotChart are always created together and remain permanently linked. You cannot disconnect them.

Part 2: Manipulate Options in an Existing PivotChart

Once a PivotChart is selected, three contextual tabs appear:

Tab Key Options
PivotChart Analyze Field buttons, filters, PivotTable connections, data refresh
Design Chart layout, chart styles, change chart type, move chart
Format Shape styles, word art, size and position

Key Modifications

Change the chart type: Design tab > Change Chart Type — switch between Bar, Line, Pie, etc. without losing the PivotTable connection.

Move the chart to its own sheet: Design tab > Move Chart > New sheet — gives the chart a full-page view for presentations.

Add/remove chart elements: Design tab > Add Chart Element — toggle titles, data labels, legend, gridlines, error bars.

Filter directly on the chart: PivotCharts have field buttons — dropdown arrows visible on the chart face. Click them to filter the data directly. These are connected to the PivotTable filters and update the underlying data.

Part 3: Apply Styles to PivotCharts

Chart styles control the visual theme of the chart — colors, fonts, and overall look.

Using the Design tab: - Chart Styles gallery (Design tab) — hover to preview styles; click to apply. - Change Colors dropdown — switch between Colorful (multi-color) and Monochromatic palettes.

Using the Format Pane: Right-click any chart element > Format [Element]... — opens the Format pane for granular control over fills, borders, shadows, and glows for individual chart components.

Applying a Theme: If you apply a workbook theme (Page Layout > Themes), all PivotCharts update their colors to match the new theme palette automatically.

TIP
For consistent branding across multiple PivotCharts, apply a custom theme color set at the workbook level rather than formatting each chart individually.

Part 4: Drill Down into PivotChart Details

One of the most powerful PivotChart features is the ability to expand and collapse hierarchy levels directly on the chart.

Using Field Buttons on the Chart

When your PivotTable has hierarchical fields (e.g., Year > Quarter > Month), the PivotChart shows +/– drill-down buttons on the axis:

  • Click + next to a category label to expand to the next level of detail.
  • Click – to collapse back to the summary level.

Using the PivotTable

Any change to the PivotTable (expanding a group, adding a field, applying a filter) instantly updates the connected PivotChart. Driling down in the PivotTable is reflected visually in the chart.

Show/Hide Field Buttons

Field buttons (the dropdown arrows and +/– controls on the chart face) can be toggled: PivotChart Analyze tab > Show/Hide group > Field Buttons dropdown: - Hide All — clean chart face for presentation - Show Report Filter Fields Only — show filter dropdowns only - Show All — restore all field buttons

IMPORTANT
Hiding field buttons is **cosmetic only** — the filters and drill-down behavior still work through the connected PivotTable and any linked slicers. Hiding buttons does not remove filtering functionality.

Part 5: PivotChart vs. Standard Chart

Feature Standard Chart PivotChart
Data source Fixed cell range PivotTable (dynamic)
Updates automatically Only if source range changes Yes — when PivotTable changes
Interactive filtering No Yes — field buttons + slicers
Hierarchy drill-down No Yes
Slicer support Partial (with extra setup) Yes (native)
Chart types available All All (except XY Scatter and Bubble with some limitations)

Official Resources