Excel Pivot Charts

A Pivot Chart is a chart that is directly connected to a Pivot Table. Just like a standard chart visualizes data from a spreadsheet, a Pivot Chart visualizes data from a Pivot Table — but with one major advantage: it is interactive. Clicking on filters, slicers, or field buttons in the Pivot Chart instantly updates the visual display without rebuilding the chart.

What is a Pivot Chart?

A Pivot Chart is a dynamic chart linked to a Pivot Table. When the Pivot Table is filtered, grouped, or changed, the Pivot Chart updates automatically to reflect those changes. This makes it ideal for interactive dashboards and presentations where the audience needs to explore different views of data.

Key Difference from a Regular Chart

  Regular Chart → Linked to a fixed data range (e.g., A1:B10)
                → Must be manually updated if data changes

  Pivot Chart   → Linked to a Pivot Table
                → Automatically updates when the Pivot Table changes
                → Has interactive field buttons for filtering

Creating a Pivot Chart

Method 1: From an Existing Pivot Table

1. Click anywhere inside an existing Pivot Table.
2. Go to PivotTable Analyze → PivotChart.
3. The Insert Chart dialog opens. Choose the chart type (Column, Bar, Line, Pie, etc.).
4. Click OK. The Pivot Chart is created on the same sheet or can be moved to a new sheet.

Method 2: Creating Pivot Table and Chart Together

1. Click any cell in the raw data.
2. Go to Insert → PivotChart.
3. Choose the data range and destination (new or existing sheet).
4. Click OK. Excel creates both the Pivot Table and Pivot Chart simultaneously.

Understanding Pivot Chart Field Buttons

When a Pivot Chart is selected, it displays field buttons directly on the chart canvas. These are interactive dropdowns that allow filtering without going back to the Pivot Table.

• Axis (Category) field buttons: Appear at the bottom. Control what is shown on the horizontal axis.
• Legend (Series) field buttons: Appear at the top or right. Control what each data series represents.
• Report Filter field buttons: Appear at the top-left. Allow the entire chart to be filtered by a category.

To hide the field buttons: Right-click any field button on the chart → Hide All Field Buttons on Chart.

Example: Regional Sales Pivot Chart

Step 1: Source Data

  | A        | B      | C       | D      |
1 | Month    | Region | Product | Sales  |
2 | January  | North  | Laptop  | 45000  |
3 | January  | South  | Phone   | 20000  |
4 | February | North  | Tablet  | 15000  |
5 | February | East   | Laptop  | 60000  |
6 | March    | South  | Tablet  | 18000  |

Step 2: Pivot Table Setup

  Rows: Region
  Columns: Month
  Values: Sum of Sales

Step 3: Insert Pivot Chart

Click inside the Pivot Table → PivotTable Analyze → PivotChart → Clustered Column → OK.

Result

A column chart appears showing sales per region for each month. Each region gets a cluster of columns, one per month. Clicking the "Month" field button allows filtering to show only January, February, or March.

Chart Types Suitable for Pivot Charts

Recommended Chart Types by Use Case

• Clustered Column / Bar: Compare values across categories (e.g., sales by region).
• Line: Show trends over time (e.g., monthly revenue trend).
• Pie / Doughnut: Show percentage breakdown (e.g., product share of total sales).
• Stacked Column: Show totals and the contribution of each sub-category.
• Area: Show cumulative values over time.

Changing Chart Type

1. Right-click on the Pivot Chart.
2. Select Change Chart Type.
3. Choose the desired type and click OK.

Connecting Slicers to a Pivot Chart

Slicers that are connected to a Pivot Table also control the linked Pivot Chart. Multiple Pivot Charts can share the same slicer if they are all connected to the same Pivot Table.

Adding a Slicer

1. Click inside the Pivot Table or Pivot Chart.
2. Go to PivotTable Analyze → Insert Slicer.
3. Select the field(s) to use as slicers (e.g., Region, Month, Product).
4. Click OK.

When a slicer button is clicked, both the Pivot Table and Pivot Chart filter simultaneously.

Connecting One Slicer to Multiple Pivot Charts

1. Right-click the slicer.
2. Select Report Connections.
3. Check all the Pivot Tables (and their linked charts) that should be controlled by this slicer.
4. Click OK.

Formatting a Pivot Chart

Applying a Style

Click the Pivot Chart. On the Chart Design tab, choose a style from the gallery. Color themes can be applied here as well.

Adding Chart Elements

Click the + button on the right of the chart to add or remove:

• Chart Title
• Data Labels
• Legend
• Axis Titles
• Gridlines

Formatting Individual Elements

Double-click any part of the chart (bars, lines, axes, title) to open the formatting pane on the right side for detailed customization.

Moving a Pivot Chart

To a Separate Chart Sheet

1. Right-click the Pivot Chart border.
2. Select Move Chart.
3. Choose New Sheet and give it a name (e.g., "Sales Dashboard").
4. Click OK. The chart fills the entire sheet for a clean, full-screen presentation.

Pivot Chart vs Regular Chart

  Feature                          | Pivot Chart | Regular Chart
  ---------------------------------|-------------|---------------
  Linked to Pivot Table            | Yes         | No
  Updates when Pivot Table changes | Yes         | No
  Has interactive field buttons    | Yes         | No
  Works with Slicers               | Yes         | Partial
  Can be based on raw data         | No          | Yes

Summary

• A Pivot Chart is a chart directly connected to a Pivot Table and updates automatically when the Pivot Table changes.
• It is created from an existing Pivot Table via PivotTable Analyze → PivotChart.
• Field buttons on the chart allow filtering without modifying the Pivot Table directly.
• Slicers connected to a Pivot Table also control all linked Pivot Charts.
• Multiple Pivot Charts can share a single slicer using Report Connections.
• Moving the chart to its own sheet creates a clean, full-screen dashboard view.