How to Use Pivot Chart in Excel (8 Suitable Examples)

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.

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.

Creating Pivot Chart from Pivot Table

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

Creating Pivot Chart from Pivot Table

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.

Creating Pivot Chart from Pivot Table

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.

Creating Pivot Chart Directly from Pivot Table

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.

Changing Chart Type of Pivot Chart in Excel

Here, you can see that I have changed my chart type from Clustered Column to a Pie chart.

Changing Chart Type of Pivot Chart in Excel

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.

Refreshing a Pivot Chart in Excel

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.

Refreshing a Pivot Chart in Excel

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.

Filtering Pivot Chart in Excel

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

Filtering Pivot Chart in Excel

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

Filtering Pivot Chart in Excel


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.

Inserting Slicer with Pivot Chart in Excel

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

Inserting Slicer with Pivot Chart in Excel

  • Now, you can remove the filter easily by clicking on Clear Filter.

Here, you will see that the filter is removed.

Inserting Slicer with Pivot Chart in Excel


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.

Inserting Timeline with Pivot Chart in Excel to Filter Dates

  • After that, select Date.
  • Next, select OK.

Now, you will see that a Timeline has been inserted into your worksheet.

Inserting Timeline with Pivot Chart in Excel to Filter Dates

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

Inserting Timeline with Pivot Chart in Excel to Filter Dates

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.

Showing Running Total in Pivot Chart in Excel

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. 

Showing Running Total in Pivot Chart in Excel

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

Showing Running Total in Pivot Chart in Excel

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

Showing Running Total in Pivot Chart in Excel

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.

Grouping Dates to Present Months in Pivot Chart in Excel

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.

Grouping Dates to Present Months in Pivot Chart in Excel

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.

How to Use Pivot Chart in Excel


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.


Related Articles

Mashhura Jahan

Mashhura Jahan

Hey! Welcome to my profile. Currently, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo