Pivot tables are close to my heart. I love to use them. But apart from this, there is one thing which I love to use.
That’s ==> Pivot Chart
Yes, a pivot chart.
And, do you know a pivot chart is the easiest way to create a dynamic chart.
Today, in this post, I’d like to share with you a step by step process to create a pivot chart in Excel.
You will also learn some useful examples which you can apply in your work with instantly.
But before that, here are some words from Wikipedia.
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.
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.
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 but you use steps in all versions from 2007 to 2016.
If you already have a pivot table in your worksheet then you can insert a pivot chart by using these simple steps.
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.
Managing a pivot chart is simple and here is some information which will help you do it smoothly.
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.
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.
Use below VBA code to refresh all kind of pivot tables and pivot chart in you workbook.
Dim PC As PivotCache
For Each PC In ActiveWorkbook.PivotCaches
Apart from above code you can use following VBA code if you want to refresh a particular pivot table.
Just like a pivot table, you can filter your pivot chart to show some specific values. 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 for this.
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.
So here is your pivot chart with running total but one more thing which we have to do to make it perfect.
Like a standard chart, you can move your Excel pivot chart to a chart sheet or any other worksheet.
To move your pivot chart.
You can also move your chart back to the original sheet using same steps.
Some of extra tips to make a better control over it.
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.
Using a slicer is always a better option is than a standard filter.
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 which you can use to filter dates in term of days, months, quarters and years.
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 a pivot chart with months even when you don’t have months in source data.
Download this sample file from here to learn more.