Pivot Chart in Excel (Complete Guide 2026)

A pivot chart is one of the best ways to present your data in Excel. Why do I say that? Showing data visually doesn't just help your reader understand it; it also helps you make your point clearly and with far less effort.

Excel gives you plenty of charts to work with, but there's one that stands out from the rest, and that's the pivot chart. If you're serious about taking your data visualization skills to the next level, this is the one to learn.

So what exactly is it? A pivot chart is a chart built on top of a pivot table. Instead of plotting a fixed range of cells like a normal chart, it summarizes your data and updates automatically whenever your pivot table changes, making it perfect for analyzing large data sets and presenting reports.

In this guide, I'll walk you through everything you need to know to understand how a pivot chart works and how to create one. Let's get started.

Difference Between a Pivot Chart and a Normal Chart

Before you start, let me clear up something that confuses a lot of people. A normal chart and a pivot chart look similar, but they work in completely different ways. Here's the simplest way to think about it.

When to use which: If you have a small table and a fixed view, a normal chart does the job. But the moment your data is large, or you want to filter, summarize, and explore it from different angles, a pivot chart is the better choice every single time.

Steps to Create a Pivot Chart in Excel

You can create a pivot chart in two ways. One is to add a pivot chart to an existing pivot table, and the other is to create one from scratch. Let me show you both, step by step.

1. Create a Pivot Chart from Scratch

Creating a pivot chart from scratch is as simple as creating a pivot table. All you need is a data set. Here, I'm using Excel 365.

1. Select any of the cells in your data, then go to the Insert Tab → Charts → Pivot Chart.
2. In the dialog box, Excel automatically selects your entire data range, and you choose where to place the chart — a new worksheet or the existing one.
3. Click OK.
4. Now you have a blank pivot table and a blank pivot chart in a new worksheet.

1. In the pivot chart fields pane, you get the same four components you have in a pivot table:
   • Axis — the same as the rows in your pivot table.
   • Legend — the same as the columns in your pivot table.
   • Values — here, I'm using quantity as the values.
   • Filters — use this area to filter the entire pivot chart.
   
2. And here's your fully dynamic pivot chart, ready to go.

2. Create a Pivot Chart from the Existing Pivot Table

If you already have a pivot table in your worksheet, you can insert a pivot chart with these simple steps.

1. Select any of the cells from your pivot table.
2. Go to the Insert Tab → Charts → Pivot Chart, and select the chart type you want to use.
3. Click OK.

Excel inserts a new pivot chart in the same worksheet as your pivot table. It uses the pivot table rows as the axis and the columns as the legend.

More Information about Pivot Charts

Managing a pivot chart is simple, and here's some information that will help you do it smoothly.

1. Change Chart Type

When you create a new pivot chart, you pick the chart type up front. But if you want to change the chart type later, here are the steps for that.

1. Select your pivot chart and go to the Design Tab → Type → Change Chart Type.
2. Select your favorite chart type.
3. Click OK.

2. Refresh a Pivot Chart

Refreshing a pivot chart is just like refreshing a pivot table. If your pivot table refreshes automatically, your pivot chart will update along with it.

Method 1 — Refresh on file open

• Right-click on your chart, then click PivotChart Options.
• Go to the Data tab, tick "Refresh data when opening the file", and click OK.

Method 2 — Refresh with VBA

Use the below VBA code to refresh all the pivot tables and pivot charts in your workbook when the file opens.

Sub auto_open()
    Dim PC As PivotCache
    For Each PC In ActiveWorkbook.PivotCaches
        PC.Refresh
    Next PC
End Sub

And if you only want to refresh one particular pivot chart, use this code instead. Replace "Chart 5" with your chart's name.

Sub refresh_single_pivot_chart()
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh
End Sub

3. Filter a Pivot Chart

Just like a pivot table, you can filter your pivot chart to show specific values. A pivot table and its pivot chart are connected to each other.

So when you filter the pivot table, the chart filters automatically — and when you add a filter to the chart, it applies to the pivot table too, and vice versa. Follow these steps.

1. Right-click on your pivot chart and click "Show Field List".
2. In the pivot chart field list, drag fields into the filter area.

4. Show Running Total in a Pivot Chart

In the pivot chart below, I've used a running total to show the growth throughout the period.

Adding a running total in a pivot chart is just like adding a running total in a pivot table. But we need a few simple changes in the chart formatting.

1. From your pivot chart field list, drag your value field into the Values area twice.
2. In the second value field, open "Value Field Settings".
3. Go to the "Show Values As" tab and select Running Total from the drop-down.
4. Click OK. Your pivot chart now shows the running total, with one more step to make it perfect.
5. Select your primary axis and adjust its values to match your secondary axis.

5. Move a Pivot Chart to a New Sheet

Like a standard chart, you can move your Excel pivot chart to a chart sheet or any other worksheet. To move your pivot chart:

1. Select your chart and right-click on it.
2. Click Move Chart, and you'll get a pop-up window.
3. Select the desired option and click OK.

You can also move your chart back to the original sheet using the same steps.

Extra Tips on Pivot Charts

Some extra tips to give you better control over your pivot charts.

1. Using a Slicer with a Pivot Chart to Filter

As I've already mentioned, you can use a slicer with your pivot chart.

And the best part is that you can filter multiple pivot tables and pivot charts with a single slicer. Follow these steps.

1. Select your pivot chart and go to the Analyze Tab → Filter → Insert Slicer.
2. Select the field you want to use as a filter.
3. Click OK.

Using a slicer is always a better option than a standard filter.

2. Insert a Timeline to Filter Dates in a Pivot Chart

If you want to filter your pivot chart using a date field, you can use a timeline instead of a slicer.

Filtering dates with a timeline is super easy. It works like an advanced filter you can use to filter dates by days, months, quarters, and years.

• Select your pivot chart and go to the Analyze Tab → Filter → Insert Timeline.
• Select your date field from the pop-up window and click OK.

3. Present Months in a Pivot Chart by Grouping Dates

Let's say you have dates in your data and you want to create a pivot chart on a monthly basis. One simple way is to add a month column to your data and use it in the chart.

But here's the twist: you can group dates in your pivot table, which lets you build a pivot chart by month even when you don't have a month column in the source data.

1. Go to your pivot table and select any cell from the date field column.
2. Right-click on it and select Group.
3. Select Months from the pop-up window and click OK.

Frequently Asked Questions