Shares

Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth \$20, Absolutely Free

# How To Create An Excel Pivot Chart In Excel

Do you remember when last time you used an Excel Pivot Chart?

And, do you know pivot chart is a easiest way to create dynamic charts in excel?

The bad news is, most of the Excel users avoid using pivot chart in their work.

But the good news is, creating a pivot chart is simple, just like a creating a pivot table.

In this tutorial, you will learn how to use a pivot chart and how you can make best out of it.

I will also show you some useful examples which you can learn and apply in your work with no extra efforts.

So let’s get started.

And, here are some words from Wikipedia about Excel pivot chart.

Pivot Chart is the best type of graphs 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 Pivot Chart ideal for presentation of data in the sales reports.Wikipedia

Table of Content

## Standard Chart Vs. Pivot 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.
Download this cheat sheet to learn about Excel Pivot Chart.

## Creating an Excel Pivot Chart

You can create a pivot chart by using two ways.

One is to add a pivot chart in your existing pivot table, and other is to create a pivot chart from scratch.

### From scratch

Creating a pivot chart from scratch is as simple as creating a pivot table. All you need, a data sheet.

Here I am using Excel 2013 to create this pivot chart. But you can use these steps in all versions from 2007 to 2016.

Please follow these simple steps to create a pivot chart in excel.

• Select any of the cells in your data sheet.
• Go to Insert Tab → Charts → Pivot Chart.

• Click OK.
• Now, you have a blank pivot table and pivot chart in a new worksheet.

Important 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 into Power Pivot Data Model.
• In pivot chart fields, we have four components like we have in a pivot table.
• Axis: Axis in pivot chart is as same as we have rows in our pivot table. Here I am using the date as an axis for my Excel pivot chart.
• Legend: Legend in pivot chart is as same as we have columns in our pivot table. Make sure not to use more than five legends will make your chart rushed.
• Values: I am using quantity for values.
• Report Filter: You can use report filter to filter your pivot chart.

• So, here is your fully dynamic pivot chart.

### From Existing Pivot Table

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

• Select any of the cells from your pivot table.

• Go to Insert Tab → Charts → Pivot Chart.
• Select the chart which you want to use.

• 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 axis and columns as the legend in pivot chart.

And, the another smart and quick way is to use shortcut key.

Just select any of the cells in your pivot table and press F11 to insert a pivot chart.

## More Information

Managing an Excel Pivot Chart is simple. And, here I have listed some information which will help you manage it smoothly.

### Change Chart Type

When you enter a new pivot chart, you have to select the type of the chart which you want to use.

And, if you want to change the chart type you can use following steps for that.

• Select your pivot chart.
• Go to Design Tab → Type → Change Chart Type.

• Select your favorite chart type.

• Click Ok.

Most of the time I use column chart, line chart, and area chart as a chart type.

### 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. And, If not please use below methods.

Method-1

• Right-click on your Excel pivot chart.
• And then click on pivot chart options. (It will open pivot chart options, which are pivot table options.)

• Go to Data Tab.
• Tick mark “Refresh data when open a file.”

• Click Ok.

Method-2

Use below VBA code to refresh all kind of pivot tables and pivot chart in you workbook.

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

Above VBA code will refresh all pivot tables and pivot charts when you open a workbook.

And, if you want to refresh a particular pivot table, you can use following VBA code.

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

### Filter An Excel Pivot Chart

Just like a pivot table you can filter your pivot chart to check some specific values from it.

One thing is clear that a pivot table and pivot chart are connected with each other.

So, when you filter a pivot table, your chart will automatically filter.

And, when you add any filter in your pivot table it will automatically add into your pivot chart and vice versa.

Please follow these steps to add a filter in your pivot chart.

• Right click on your pivot chart and click on “Show Field List”.

• In your pivot chart field list, drag fields in the filter area.

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

### Running Total In Pivot Chart

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

To 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.

Note down following steps for it.

• From your pivot chart field list, drag your value field twice in value area.

• Now, in second field value open “Value Field Settings”.
• Go to “show value as” tab. And, select running total from the drop down.

• Click Ok.

Now, you have this kind of a pivot chart in your worksheet. But the better way is to use a combo chart here instead.

• Select running total data bars from the chart.
• Go to Design Tab and click on change chart type.

• Click Ok.

So here is your pivot chart with running total. But here is one thing which we have to do to make it perfect.

Adjust the axis labels.

• Select you primary axis and change values as per your secondary axis.

Rest of the formatting is as per you need.

### Move A Pivot Chart

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

To move your pivot chart.

• Select your chart and right click on it.
• Click on move chart and you will get a pop-up window.

• Now, you have two different options to move your chart.
• New Chart Sheet.
• Another Worksheet.
• Select the desired option and click OK.

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

## Extra Tips On Excel Pivot Chart

To Increase, the power of your pivot chart you use can provide these following tips. I have listed some of my favorite tips which I use with Excel pivot chart.

### Using A Slicer

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 to add a slicer to a pivot chart.

• Select your pivot chart.
• Go to Analyze Tab → Filter → Insert Slicer.

• Select the field which you want to use as a filter.

• Click OK.

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

### Using A Timeline

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

Filtering dates with a timeline is super easy.

This is like an advanced filter which you can use to filter dates in term of days, months, quarters and years.

Follow these steps to add a timeline.

• Select your pivot chart.
• Go to Analyze Tab → Filter → Insert Timeline.

• Select your date field from the pop-up window. It will only show you fields with dates.
• Click OK.

You can use time-level option to change the filtering criteria.

### Grouping Dates

Now, let’s say you have dates in your data, and you want to create a pivot chart on month 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 an Excel pivot chart on month basis even when you don’t have months in source data.

Here are the steps

• Go to your pivot table and select any of the cells from your date field column.
• Right-click on it and select group.

• Select the month from the pop-up window.
• Click OK.

You can also use this technique to group dates in quarters or years.

## Last Words

So the bottom line about Excel Pivot Chart is, it’s dynamic, easy to create and easy to manage.

Most of the Excel users are happy by using pivot tables because they are the best way to present data.

But, the thing we have to understand about pivot tables that they are all about analysis of number.

And, in this fast running world, everyone wants to absorb data more quickly, and Excel Pivot Chart is the most useful weapon for this.

## Over To You

What do you think about using an Excel pivot chart?

Do you use it frequently in your work?

Share your experiences with me in the comment box.