Mashhura Jahan
Using a Pivot Chart is one of the best ways to present your data in Excel. The main objective of this article is to explain how to use Pivot Chart in Excel.
Download Practice Workbook
What Is a Pivot Chart?
Pivot chart helps to summarize large amounts of data in Excel. A pivot chart is mainly a graphical representation of a pivot table. Pivot charts and pivot tables are linked together. A pivot chart can be convenient while representing data. And, it also can be used in various cases.
8 Examples to Use Pivot Chart in Excel
Here, I have taken a dataset that contains sales information. The table contains the Date, State, Departments, and Sales.
I will use this dataset to explain how to create a pivot chart and how to use pivot chart in Excel.
1. Creating a Pivot Chart in Excel
A pivot chart can be created in 2 ways. The first one is from scrap and the second one is directly from a pivot table.
1.1. Creating Pivot Chart by Using PivotChart Feature
In this section, I will show you how to create a pivot chart from scrap using my dataset.
Let’s see the steps.
Steps:
- To begin with, select any cell from your dataset. Here, I selected the first cell of my dataset which is B4.
- After that, go to the Insert tab.
- Next, click on the PivotChart. A drop-down menu will appear.
- Now, select PivotChart from that drop-down menu.
A dialog box will appear. Which will automatically select your entire dataset.
- Firstly, select where you want your pivot chart. Here, I selected Existing Worksheet.
- Secondly, select the location in the worksheet.
- Thirdly, select OK.
Now, a blank pivot table and a blank pivot chart will appear on your worksheet.
PivotTable Fields list will appear on the right side of the screen.
- Firstly, select and drag the Sales column to the Values area. The PivotTable will display the sum of sales in the Sales column.
- Secondly, select and drag the State, Date, and Departments column to the Rows area. The PivotTable will display them as Row Labels.
Finally, you will see that you have got your pivot table and pivot chart.
1.2. Creating Pivot Chart from Pivot Table
In this section, I will show you how to create a pivot chart directly from a pivot table.
Let’s see the steps.
Steps:
- Firstly, select any cell from your pivot table.
- Secondly, go to the Insert tab.
- Thirdly, select PivotChart.
- Now, select the type of chart you want for your PivotChart. Here, I selected Clustered Column.
- After that, select OK.
Now, you will get your pivot chart.
Read More: How to Import Data into PowerPivot & Create Pivot Table/Pivot Chart
2. Changing Chart Type of Pivot Chart in Excel
The first use of a pivot chart that I am going to explain is changing the chart type.
Let’s see step by step how to change the chart type of a pivot chart in Excel.
Steps:
- Firstly, select the chart.
- Secondly, go to the Design tab from the Ribbon.
- Thirdly, select Change Chart Type.
- After that, select the chart type you want for your pivot chart. Here, I selected a 2D Pie chart from Pie or Doughnut Charts.
- Now, click on OK to get your desired chart.
Here, you can see that I have changed my chart type from Clustered Column to a Pie chart.
Read More: How to Edit Pivot Chart in Excel (with Easy Steps)
3. Refreshing a Pivot Chart in Excel
In this section, I will explain how to refresh a pivot chart in Excel. Let’s see step by step.
Steps:
- Firstly, Right-click on the pivot chart.
- Secondly, select PivotChart Options.
A dialog box will appear.
- Now, go to the Data tab.
- Next, select Refresh data when opening the file.
- Finally, select OK.
Now, your data will be refreshed whenever you open the file.
Read More: Types of Pivot Charts in Excel (7 Most Popular)
4. Filtering Pivot Chart in Excel
In Excel, you can filter a pivot chart the way you want. In this section, I will explain how to filter a pivot chart in Excel. Let’s see step by step.
Steps:
- Firstly, select the row that you want to filter. Here, I selected State.
- Secondly, select the State which you want to see in your pivot chart. Here, I selected Verginia.
- Thirdly, select OK.
Now, you can see that the pivot chart and the pivot table are showing the data for the state of Virginia.
You can also clear the filter from your pivot chart just as easily.
- Firstly, select the row in which you added the filter. Here, I selected State.
- After that, select Clear Filter From “State”.
Now, you will sell that the filter is cleared and your pivot chart is showing data as before.
5. Inserting Slicer with Pivot Chart in Excel
Here, I will explain how to insert Slicer with pivot chart in Excel. Let’s see the steps.
Steps:
- Firstly, select the pivot chart.
- Secondly, go to the Insert tab.
- Thirdly, select Slicer.
- Now, select where you want to add a filter. Here, I selected Department.
- Next, click OK.
Now, you will see that your Slicer has been inserted. Here, you can add filters as you want.
- After that, select the Department in which you want to see the data. Here, I selected Electronics.
You can see that the pivot chart is showing data for the Electronics department.
- Now, you can remove the filter easily by clicking on Clear Filter.
Here, you will see that the filter is removed.
6. Inserting Timeline with Pivot Chart in Excel to Filter Dates
You can also add Timeline with a pivot chart in Excel to filter dates. Let’s see how it is done.
Steps:
- Firstly, select the pivot chart.
- Secondly, go to the Insert tab.
- Thirdly, select Timeline.
- After that, select Date.
- Next, select OK.
Now, you will see that a Timeline has been inserted into your worksheet.
- After that, select the time period in which you want to see the data. Here, I selected March.
Now, you will see that the pivot chart and the pivot table is showing data for March.
You can also clear the filtering.
- Finally, select Clear Filter.
Now, you will see that the filter is cleared and your pivot chart and pivot table is showing data as before.
7. Showing Running Total in Pivot Chart in Excel
You can also show the Running Total in a pivot chart in Excel. To explain this example I have taken a pivot table that contains Date in Rows and the sum of sale in Values.
Let me explain how it is done.
Steps:
- Firstly, Right-click on the chart.
- Secondly, select Show Field List.
- Thirdly, select and drag Sale into the Values area.
- Now, from the drop-down menu select Value Field Settings.
- After that, change the Custom Name to Running Total.
- Then, change the Show value as to Running Total In.
- Next, select Date as the Base field.
- Finally, select OK.
Now, you will see that the chart for Running Total has been inserted into your existing chart. But, I want to change the chart type.
- Firstly, select any bar of the chart.
- Secondly, go to the Design tab.
- Thirdly, select Change Chart Type.
- After that, click on the drop-down menu of Running Total.
- Next, select the chart type you want. Here, I selected Line with Marker.
- Finally, select OK.
Here, you can see the Running Total in my pivot table and pivot chart.
Read More: How to Make a Bar Graph in Excel with 2 Variables (3 Easy Ways)
8. Grouping Dates to Present Months in Pivot Chart in Excel
In this section, I will show you how to group dates to present months in a pivot chart in excel. Here, I have taken the same table the I took in the previous section.
Let’s see the steps.
Steps:
- Firstly, Right-click on any date from your pivot table.
- Secondly, select Group.
That will automatically select the range of date that is used in the table.
- After that, select Months.
- Finally, select OK.
Now, you will see that the data in the pivot table and pivot chart are presented as months.
Read More: Difference Between Pivot Table and Pivot Chart in Excel
Things to Remember
- It should be noted that if you group any data in your pivot table it will group in all the other worksheets of your workbook.
Practice Section
Here, I have provided a practice sheet for you to practice.
Conclusion
In this article, I tried to cover how to use pivot chart in Excel. I explained how to create it along with its uses. I Hope, it was helpful for you. If you like this article then visit ExcelDemy to get more articles like this. I recommend you practice more and more to understand better. Lastly, if you have any questions feel free to let me know in the comment section below.
