Pivot Charts in Data Analysis

Pivot charts are useful in data analysis when we need to show the summary of a pivot table in a visual form. They help us compare values, find trends, and present results clearly, so the data can be understood more easily. In Excel, pivot charts are used to handle sales reports, employee records, expense sheets, customer data, inventory lists, and other table-based data.

In this article, we will learn about pivot charts in data analysis, their uses, and the main features used to create and customize them in Excel.

What Is a Pivot Chart in Data Analysis?

A pivot chart is a chart that is connected to a pivot table. It displays the summarized data of the pivot table as a column, line, pie, or other type of chart. When we change the layout, filter, or values of the pivot table, the pivot chart changes automatically, and the same happens in the opposite direction.

For example, if a pivot table shows the total sales of each region, we can create a pivot chart to compare the regions visually. Similarly, we can use a line chart to show how monthly sales change over time.

Difference Between a Normal Chart and a Pivot Chart

Why Are Pivot Charts Important in Data Analysis?

Numbers in a table are useful, but patterns are easier to see in a chart. Pivot charts make this process easier by combining summarization and visualization in one place.

They can be used to:

• Compare categories, such as regions or products.
• Show trends over days, months, or years.
• Show the share of each category in the total.
• Find the highest and lowest values quickly.
• Filter the chart using field buttons and slicers.
• Update the chart automatically when the data changes.
• Create dashboards and presentation reports.
• Drill down from yearly data to monthly data.

Sample Dataset Used in This Article

We will use the following dataset in the examples. The headings are in row 1, and the data is in the range A1:E9.

Requirements for Creating a Pivot Chart

Before creating a pivot chart, we need the following.

• The source data should have a unique heading in each column.
• There should be no blank rows or blank columns inside the data.
• Dates and numbers should be stored as real dates and numbers.
• A pivot table should exist, or Excel can create one together with the chart.

How to Create a Pivot Chart in Excel

Method 1: From an Existing Pivot Table

Steps

1. Click on any cell inside the pivot table.
2. Go to the PivotTable Analyze tab.
3. Click on PivotChart.
4. Choose a chart type, such as Clustered Column, and click OK.

Explanation:
In the above steps, we select a cell in the pivot table and insert a pivot chart. After that, Excel reads the fields used in the pivot table. Finally, it creates a chart linked to the same data.

Method 2: Directly from the Data

Steps

1. Click on any cell inside the data range.
2. Go to the Insert tab and click on PivotChart.
3. Check the table or range, choose where to place the chart, and click OK.
4. Drag the fields into the PivotChart Fields pane.

Explanation:
In the above steps, we insert the PivotChart directly from the data. After that, Excel creates an empty pivot table and an empty chart together. Finally, we drag fields into the areas to build both.

Parts of the PivotChart Fields Pane

Creating a Column Chart for Regional Sales

Steps

1. Drag Region to the Axis (Categories) area.
2. Drag Sales to the Values area.
3. Insert a Clustered Column chart.

Output:

The chart shows three columns, and the South column is the tallest.

Explanation:
In the above example, we place Region on the axis and Sales in the values area. After that, Excel adds the sales of each region. Finally, it draws one column for each region, so we can compare them easily.

Creating a Chart with Axis and Legend Fields

Steps

1. Drag the region to the Axis (Categories) area.
2. Drag Product to the Legend (Series) area.
3. Drag Sales to the Values area.

Output:

The chart shows two columns for each region, one for Laptop and one for Mobile, in different colors.

Explanation:
In the above example, we place Region on the axis and Product in the legend. After that, Excel calculates sales for each combination. Finally, the chart shows the products side by side in every region.

Common Pivot Chart Types

Note that XY (Scatter), Bubble, and Stock charts cannot be used as pivot charts.

Changing the Chart Type

Steps

1. Click on the pivot chart.
2. Go to the PivotChart Analyze or Design tab.
3. Click on Change Chart Type.
4. Select the new chart type, and click OK.

Creating a Line Chart for Monthly Trends

Steps

1. Drag Date to the Axis (Categories) area.
2. Group the dates by months if Excel has not grouped them automatically.
3. Drag Sales to the Values area.
4. Change the chart type to Line.

Output:

The line rises in March and falls in April.

Explanation:
In the above example, we place the Date field on the axis and group it by months. After that, we select a line chart. Finally, the line shows how sales changed from month to month.

Creating a Pie Chart for Share Analysis

Steps

1. Drag Region to the Axis (Categories) area.
2. Drag Sales to the Values area.
3. Change the chart type to Pie.
4. Right-click on the pie and choose Add Data Labels to show percentages.

Output:
North shows about 38.24%, South shows about 40.20%, and East shows about 21.57% of the total sales of 408000.

Explanation:
In the above example, we use a pie chart on the regional sales summary. After that, we add percentage labels. Finally, each slice shows the share of that region in the total sales. A pie chart works best when there are only a few categories.

Field Buttons

Field buttons are the small drop-down buttons shown on the pivot chart. They allow us to filter the chart directly, without going back to the pivot table.

Steps

1. Click on the pivot chart.
2. Click on a field button, such as Region.
3. Select or clear the items we want to show, and click OK.

To show or hide the buttons, go to PivotChart Analyze > Field Buttons. Hiding them gives a cleaner look when the chart is used in a report.

Filtering with Slicers and Timelines

Slicers and timelines can filter a pivot chart in the same way as a pivot table.

Steps

1. Click on the pivot chart.
2. Go to the PivotChart Analyze tab and click on Insert Slicer.
3. Select the field, such as Product, and click OK.
4. Click on a slicer button to filter the chart.

Explanation:
In the above steps, we insert a slicer for the Product field. After that, the slicer shows one button for each product. Finally, clicking a button updates the chart to show only that product. For dates, we can use Insert Timeline. One slicer can also be connected to more than one pivot chart or pivot table.

Drilling Down and Up

Expand and collapse buttons are available on a pivot chart when the axis has more than one level, such as year and month. We can click Expand Entire Field to drill down to the details or Collapse Entire Field to return to the summary.

Explanation:
This feature is useful for presenting data step by step, for example, by showing yearly sales first and then opening a single year to see its monthly sales.

Formatting a Pivot Chart

We can improve the look of a pivot chart in the following ways.

• Chart Title: Click the title and type a meaningful name, such as "Region-wise Sales".
• Axis Titles: Go to Chart Elements (+) and select Axis Titles.
• Data Labels: Show values on the columns, lines, or slices.
• Legend: Move or remove the legend from Chart Elements.
• Colors and Styles: Use the Design tab to choose a color scheme and chart style.
• Gridlines: Remove extra gridlines to keep the chart simple.

By default, a pivot chart may lose manual formatting when the pivot table is refreshed. To keep the formatting, right-click on the chart, choose Format Chart Area, and make sure the option to preserve formatting is checked in the PivotTable Options.

Refreshing a Pivot Chart

A pivot chart does not update automatically when the source data changes. To update it, click on the chart, go to PivotChart Analyze, and click on Refresh. If new rows are added outside the original range, use Change Data Source, or convert the source data into an Excel Table so the range expands automatically.

Limitations of Pivot Charts

• XY (Scatter), Bubble, and Stock chart types are not available.
• The chart always depends on the pivot table, so it cannot be built from random cells.
• Changing the pivot table layout may remove some manual formatting.
• Some chart elements, such as trendlines, may be lost when the layout changes.
• A pivot chart cannot be created from a pivot table that uses the Data Model in the same way as one built from a normal range, so some features may behave differently.