Pivot Chart in Excel

What is the Pivot Chart in Excel?

Pivot Chart in excel is an in-built Programme tool in excel which helps you out to summarize selected rows and column of data in a spreadsheet. It’s the visual representation of a pivot table or any tabular data which helps to summarize & analyze the datasets, patterns, and trends. In Simple words pivot chart in Excel is an interactive excel chart that summarizes large amounts of data.

How to Create a Pivot Chart in Excel? (Step by Step with Example)

Let us learn how to create a Pivot Chart in Excel with the help of an example. Here we do the Sales Data Analysis.

You can download this Pivot Chart Excel Template here – Pivot Chart Excel Template

Below mentioned data contains a compilation of sales information by date, salesperson, and region; here, I need to need to summarize sales data for each representative by region-wise in the chart.

Pivot Chart Example 1

  1. To create a Pivot Chart in Excel, select the data range.

    Pivot Chart Example 1-1

  2. Then click the Insert tab within the Ribbon.

    Pivot Chart Example 1-2

  3. Then select the PivotChart drop-down button within the Charts group. If you want to create a PivotChart only, then select PivotChart from the drop-down or if you want to create both a PivotChart and PivotTable, then select PivotChart PivotTable from the drop-down.

    Pivot Chart Example 1-3

  4. Here, I have selected, create both a PivotChart and PivotTable. Create PivotChart dialog box appears, which is similar to the Create Pivot Table dialog box. It will ask for the options, i.e., from a table range or from an external database. By default, it selects table range, and it will ask you where to place a pivot table chart, here you need to always select in a new worksheet.

    Pivot Chart Example 1-4

  5. Once you click OK, It inserts both PivotChart PivotTable in a new worksheet.

    Pivot Chart Example 1-5

  6. PivotChart Fields task pane appears on the left side, which contains various fields, i.e., Filters, Axis (Categories), Legend (Series), and Values. In the PivotTable Fields pane, select the Column fields applicable to the pivot table; you can drag and drop, i.e., salesperson to the Rows section, Region to the Columns section, and sales to the Values section.

    Pivot Chart Example 1-6

    Then the chart looks like as given below.

    Pivot Chart Example 1-7

  7. You can name this sheet as SALES_BY_REGION, click inside the PivotTable, you can change the chart type, in Change Chart Type option, based on your choice under Analyze tab in the home, select PivotChart, insert chart popup window appears, in that Select Bar, under that select Clustered Bar chart. Right, Click on the Pivot Chart, select Change Chart Type.

    Pivot Chart Example 1-8

  8. Under Change chart type, select Column, then Select the Clustered Column Chart.

    Pivot Chart Example 1-9

  9. Now, you can summarize the data with the help of interactive controls present across the chart. When you click on Region Filter Control, a search box appears with the list of all the regions, where you can check or uncheck boxes based on your choice.

    Pivot Chart Example 1-10

  10. On the corner of the chart, you have an option to format chart elements based on your choice.

    Example 1-11

  11. You have an option to Customize the Pivot Table Values; by default, Excel uses the SUM function to calculate the values available in the table. Suppose if you select only region values in the chart, it will display the total SUM of Sales for each region.

    Example 1-12

  12. You have an option to change Chart Style in excel by the click of the Styles icon on the corner of the chart.

    Example 1-13

  13. This Chart will get updated when you change any dataset values in a pivot table. This option can be optimized by the following steps: Right-click and select PivotChart Option.

    Example 1-14

    Example 1-15

    In the above chart options, go to the Data tab and click on the checkbox Refresh data when opening a file. So that refresh data gets activated.

Things to Remember

In an excel pivot chart, you have an option to insert a timeline to Filter Dates (monthly, quarterly, or yearly) in a Chart to summarize sales data (This step is applicable only when your dataset contains only date values).

Chart Timeline

You can also use a Slicer with a Pivot Chart to Filter region wise data or other field data of your choice to summarize sales data.

Chart Slicer

Recommended Articles

This has been a Step by Step Guide to Create a Pivot Chart in Excel. Here we discuss how to create a Pivot Chart in Excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion