How to Use Pivot Chart in Excel (5 Different Uses)

Get FREE Advanced Excel Exercises with Solutions!

A Pivot Chart is the visual representation of our raw data. Using a Pivot Chart is one of the best ways to present your data in Excel. It allows us to analyze data using various types of graphs and layouts. The main objective of this article is to explain how to use Pivot Chart in Excel.

how to use pivot chart in excel

What Is a Pivot Chart in Excel?

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

In this article, we will create a pivot chart first. Later we will discuss how to use a pivot chart in Excel.

Create a Pivot Chart in Excel

Let’s assume we have the following dataset. It contains some employee information of a corporation including their IDs, names, departments, hiring dates, and annual salaries.

dataset to use pivot chart in excel

First, we are going to show how we can create a pivot chart out of it in Excel.

  • Select any cell from your dataset. Here, I selected the first cell of my dataset which is B4.
  • Now go to the Insert tab and click on the PivotChart in the Charts A drop-down menu will appear.
  • Select PivotChart from that drop-down menu.

creating pivot chart in excel

  • Now select the cell or spreadsheet you want the chart to be from the Create PivotChart We have selected New Worksheet here.

create pivot chart box particulars

  • After clicking on OK, a blank pivot table will appear on a new sheet with our options.

blank pivot chart and table

  • Now click on any of the cells within the table or chart area, and you will get the PivotTable Fields window on the right side of the spreadsheet.
  • In this window, we are selecting Employee ID in the Rows field and Annual Salary in the Values field.

moving particulars to fields to create pivot chart

As a result, we will get the pivot chart as well as the table on the spreadsheet.

sample pivot chart to use in excel

Note: You can create a pivot chart from the pivot table too. If you have already created a pivot table, you can get the PivotTable Analyze tab on the ribbon once you have selected any of the cells in the table. You can get the PivotChart option available there in the Tools group.

Read More: How to Create Chart from Pivot Table in Excel

How to Use Pivot Chart in Excel: 5 Different Uses

The pivot chart has many uses- data analysis, exploration, visualization, comparison, filtering, dashboard creation, etc. It offers many functionality that normal charts don’t offer. We will be discussing some of them in this section. We are going to demonstrate them in the chart that we have created.

1. Use Pivot Chart by Filtering Data

Let’s suppose, we have created the following pivot chart from our dataset. This chart not only represents the employee IDs but also the designated departments.

sample pivot chart with 2 particulars for use in excel

What if we want to filter out some employees from the data? Or do we want to show employees from particular departments only? The pivot chart has the option to filter out data for these unique problems. To filter the data in the pivot chart in Excel, follow these steps.

  • To filter departments, click on the filter button named Department at the bottom left of the chart.
  • In the filter menu, manually select/deselect the departments you want to filter out from the chart. We have selected “Executive”, “IT”, and “Marketing” manually here.

use filter in pivot chart of excel

  • After clicking on OK, we can get the filtered pivot chart on the same Excel spreadsheet.

pivot chart after filtering in excel

Note: You can do that for any of the particulars you have inserted in the Rows field of the pivot chart.

2. Insert Slicer in Pivot Chart to Filter Data from Particulars

Let’s go back to the original chart and add a slicer to them now. The purpose of adding a slicer is to filter data from the Rows field particulars.

It will give the same result as the previous one. So using this or the previous one is entirely up to the user’s choice.

Another advantage involved with the slicer is the filtering options are always available beside the pivot chart.

To add slicer and filter data from them, follow these steps.

  • First, select the pivot chart.
  • Then go to the Insert tab and select Slicer from the Filters group.
  • The Insert Slicers box will open, select Department from it.

use slicers in pivot chart of excel

  • After clicking on OK, the slicer will be available in the spreadsheet. Select the desired department you want to filter out by clicking on it here. For example, we have selected IT in the following figure.

how to use pivot chart in excel

  • With slicers, you can select multiple options too. The Multi-Select option is available on top of the slicer. Click on it and select multiple options from the slicer. The graph will follow.

selecting multiple slicers

Read More: Use Excel VBA to Create Chart from Pivot Table

3. Insert Timeline with Pivot Chart to Filter Dates

You can also add timelines in pivot charts. This helps filter out data based on a particular time period. Basically, this is a slicer for the time period. To add a timeline in a pivot chart, follow these steps.

  • Select the chart first.
  • Then go to the Insert tab and select Timeline from the Filters group.
  • The Insert Timelines window will appear. The timeline options from the dataset will be available here. We have the Hire Date as our dataset has that.

use of timelines in pivot chart of excel

  • As a result, the timeline will appear on the spreadsheet. Select the time period you want from here now on.

We have selected July in the timeline. So the chart changed accordingly to reflect that.

timeline in action

4. Display Running Total in Pivot Chart

Another option we can have in the pivot chart is to calculate and display the running total. The running total indicates the cumulative total of the ongoing values available to a point. To display the running total in the chart we have created, follow these steps.

  • Let’s first modify the chart to have a separate running total. We have added the “Annual Salary” again in the Values field.

pivot chart fields for running sum

You can skip this step if you just want the running total to show in the pivot chart.

  • Click on the “Sum of Annual Salary2” (or the first one if you wish) and select Value Field Settings from the menu.

changing settings of a value field

  • In the Value Field Settings box, select a custom name. Here we named it ‘Running Total’.
  • Go to the Show Value As Under the Show Value As option, select Running Total In. In the Base field, select Months (Hire Date).

value field settings for running sum

  • After clicking on OK, you can get the running total in the pivot chart.

running sum in column

  • To make it more presentable, let’s modify it to a line chart. We can do that by selecting the chart and selecting the Change Chart Type option from the Type group of the Design tab.

change chart type options

  • We have selected a Line for the second line from the Combo tab of the Change Chart Type box as shown in the figure below.

changing to line graph

We can finally get the additional running total in the pivot chart like below.

combo chart for running sum

Read More: Types of Pivot Charts in Excel

5. Group Dates in Pivot Chart

We can also group dates in a pivot chart. This helps to summarize the data on a timely basis like days or months. Let’s assume we have the chart below. It has the dates against the annual summary in the pivot chart.

sample pivot chart for grouping

We are going to group the dates and determine the annual salaries assigned on a monthly basis with the following steps.

  • Right-click on a date of a pivot table and select Group from the context menu.

group option in context menu

  • In the grouping box, you can manually determine the starting and ending date. Although it will be automatically filled up. However, you have to select Months under the By field and click on OK.

grouping by months

We will get all the dates now grouped in months in the pivot chart.

pivot chart grouped in months

How to Change Chart Type of Pivot Chart in Excel

Once you have created a pivot chart, you are not stuck with it for the rest of the calculations or presentations. You don’t have to create a chart from the very first point either. You can follow the steps to change the chart type if you have already created one and want a new way to represent your data.

  • First, click on the chart and go to the Design tab on the ribbon.
  • Then select the Change Chart Type option in the Type group.

changing chart type in use of pivot chart

  • In the Change Chart Type box, select the type of chart you want now. We have selected a 2-D pie chart from the Pie tab.

changing to pie chart

  • After clicking on OK, we will see Excel has changed the pivot chart type.

pie chart as pivot chart

Read More: How to Edit Pivot Chart in Excel

How to Refresh a Pivot Chart in Excel

Changing any data in the original dataset won’t automatically update the data. For example, let’s take the following chart we have created from our dataset previously.

sample dataset to display change

If we change the annual salary input of the first entry, there won’t be any change in the pivot chart.

chart isn't changing with value

To update the data in the chart or refresh it, follow these steps.

  • First, select the chart.
  • Then go to the PivotTable Analyze tab on the ribbon and select Refresh from the Data group.

refresh option for chart

The chart data will now refresh to match that of the dataset.

chart changing with value

Read More: How to Refresh Pivot Chart in Excel

Things to Remember

  • If you group any data in your pivot table it will group in all the other worksheets of your workbook.
  • You can directly insert a pivot chart without creating a pivot table first.
  • Inserting a pivot chart will always include the pivot table in the sheet automatically.

Frequently Asked Questions

  • What is the difference between a pivot chart and a normal chart?

A pivot chart is always linked to a pivot table. Where the normal chart is linked to a range of cells. The pivot chart is always more dynamic, flexible, and interactive and makes data aggregation and summarization easier because of the features we have described in the article.

  • What is the difference between a pivot chart and a pivot table?

A pivot chart is represented by visually representing graphic elements such as columns, bars, lines, etc. Meanwhile, a pivot table is the data in a tabular format with rows and columns that provides a bit more functionality than the normal range of cells in Excel.

  • How many types of pivot charts are there in Excel?

There are many types of pivot charts available in Excel. Most notable ones include- column charts, bar charts, line charts, area charts, pie charts, doughnut charts, scatter charts, bubble charts, pivot chart stacked column charts, pivot chart 100% stacked column charts.

Download Practice Workbook

You can download the workbook used for the demonstration from the link below.


That concludes our discussion on how to use pivot chart in Excel. We included the use of a filter, slicer, timeline, running total, and grouping as general usage. This article also covers the method to change the chart type and how to update or refresh pivot chart data in Excel. Hopefully, this made creating and working with pivot charts easier for you. I hope you found this guide helpful and informative.

If you have any questions or suggestions, let us know in the comments below.

Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.


Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF