How to Create PIVOT CHART in Excel

A PIVOT CHART is one of the best ways to present your data in Excel.

Why I’m saying this? Well, data in a visual way not only helps the user to understand it but it also helps you to present a clearer picture of it and you can make your point clear with led efforts.

And when we talk about Excel, there is a number of charts that you use but there’s one of all those that STANDS OUT and that’s a PIVOT CHART.

If you are serious about taking your data visualization skills to the whole next level you need to learn to create a pivot chart.

And in the guide, I’ll be explaining to you all the details you need to know to understand how the pivot chart works. But before that, here are some words from Wikipedia.

Pivot Chart is the best type of graph for the analysis of data. The most useful feature is the possibility of quickly changing the portion of data displayed, like a PivotTable report. It makes the Pivot Chart ideal for the presentation of data in sales reports.

Difference Between a Pivot Chart and a Normal Chart

• A standard chart use range of cells, on the other hand, a pivot chart is based on data summarized in a pivot table.
• A pivot chart is already a dynamic chart, but you have to make changes in data to convert a standard chart into a dynamic chart.

Steps to Create a Pivot Chart in Excel

You can create a pivot chart using two ways. One is to add a pivot chart to your existing pivot table, and the other is to create a pivot chart from scratch.

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 datasheet. Here I am using Excel 2013, but you use steps in all versions from 2007 to 2016.

1. Select any of the cells in your data sheet and go to Insert Tab → Charts → Pivot Chart.
   
2. The pop-up window will automatically select the entire data range and you have the option to choose the place where you want to insert your pivot chart.
   
3. Click OK.
4. Now, you have a blank pivot table and pivot chart in a new worksheet.
   

Note: When you insert a pivot chart it will automatically insert a pivot table along with it. And, if you just want to add a pivot chart, you can add your data to Power Pivot Data Model.

1. In pivot chart fields, we have four components as we have in a pivot table.
   1. Axis: The axis in the pivot chart is as same as we have rows in our pivot table.
   2. Legend: Legend in the pivot chart is as same as we have columns in our pivot table.
   3. Values: We are using quantity as values.
   4. Report Filter: You can use the report filter to filter your pivot chart.
   
   
2. Here is your fully dynamic pivot chart.
   

2. Create a Pivot Chart from the Existing Pivot Table

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

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

It will insert a new pivot chart in the same worksheet where you have your pivot table. And, it will use pivot table rows as the axis and columns as the legend in the pivot chart.

Important: Another smart and quick way is to use the shortcut key. Just select any of the cells in your pivot table and press F11 to insert a pivot chart.

Related – Excel Slicer

More Information about Pivot Charts

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

1. Change Chart Type

When you enter a new pivot chart, you have to select the type of chart which you want to use. And, if you want to change the chart type you can use the following steps for that.

1. Select your pivot chart and go to 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 is refreshing automatically, then your pivot chart will also update along with that.

Method-1

• Right-click on your chart, and then click on pivot chart options.
  
• Go to the data tab and tick mark “Refresh data when opening a file”. And then, click OK.
  

Method-2

Use below VBA code to refresh all kinds of pivot tables and pivot charts in your workbook.

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

Apart from the above code, you can use the following VBA code if you want to refresh a particular pivot table.

Sub auto_open()
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 some specific values. One thing is clear a pivot table and a pivot chart is connected with each other.

So, when you filter a pivot table, your chart will automatically filter. And, when you add any filter to your pivot table it will automatically add to your pivot chart and vice versa. Please follow these steps for this.

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

Note: By default, you have a filter option at the bottom of your pivot chart to filter axis categories.

4. Show Running Total in a Pivot Chart

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

Enter a running total in a pivot chart is just like entering a running total in a pivot table. But we need to make some simple changes in chart formatting.

1. From your pivot chart field list, drag your value field twice in the value area.
   
2. Now, in the second field value open “Value Field Settings”.
3. Go to the “show value as” tab and select running total from the drop-down.
   
4. Click OK (So here is your pivot chart with the running total but one more thing which we have to do to make it perfect).
5. Select your primary axis and change values as per your secondary axis.
   

5. Move a Pivot Chart to 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 on the move chart and you will get a pop-up window.
   
3. • New Chart Sheet.
   • Another Worksheet.
4. 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 make better control over it. 

1. Using a Slicer with a Pivot Chart to Filter

As I have 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 Analyze Tab → Filter → Insert Slicer.
   
2. Select the field which 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 Charts 

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

Filtering dates with a timeline is super easy. This is like an advanced filter that you can use to filter dates in terms of days, months, quarters, and years.

• Select your pivot chart and go to Analyze Tab → Filter → Insert Timeline.
  
• Select your date field from the pop-up window and it will show you fields with dates, and then click OK.

3. Present Months in a Pivot Chart by Grouping Dates

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

But here is the twist. You can group dates in your pivot table which will further help you to create a pivot chart with months even when you don’t have months in source data.

1. Go to your pivot table and select any of the cells from your date field column.
2. Right-click on it and select group.
   
3. Select the month from the pop-up window and click OK.

More Charting Tips and Tutorials

• Add a Horizontal Line in a Chart in Excel
• Add a Vertical Line in a Chart in Excel
• Bullet Chart in Excel
• Dynamic Chart Range in Excel
• Dynamic Chart Title in Excel
• Interactive Charts In Excel
• Sales Funnel Chart in Excel
• How to Create a HEAT MAP in Excel
• HISTOGRAM in Excel
• Pictograph in Excel
• Milestone Chart in Excel
• People Graph in Excel
• Population Pyramid Chart in Excel
• SPEEDOMETER Chart [Gauge] in Excel
• Step Chart in Excel
• Thermometer Chart in Excel
• Tornado Chart in Excel
• Waffle Chart in Excel