What Is A Pivot Chart In Excel And How To Make It

This hands-on tutorial explains what is a Pivot Chart and how to make and customize it. We will also see the difference between Pivot Chart vs Table:

Charts are considered one of the best ways to present the report. They help us in understanding and analyze the data in a simpler way. Pivot charts in Excel give us a visual representation of data in various different ways.

In this tutorial, we will learn all the details that are needed to work with pivot charts in Excel. Creation of charts of various types, formatting their layout, adding filters, adding custom formulas, and using the format of one chart to another chart belonging to the different pivot tables.

=> Read Through The Simple Excel Training Guide

What Is A Pivot Chart In Excel

A pivot chart in Excel is a visual representation of the data. It gives you the big picture of your raw data. It allows you to analyze data using various types of graphs and layouts. It is considered to be the best chart during a business presentation that involves huge data.

Pivot Chart Vs Table

Pivot Table provides us a way to summarize large data in a grid-like matrix. You can choose the fields you wish to use in the table for rows and columns. The pivot chart provides us with a graphical representation of the pivot table. You can choose from multiple layouts and chart types.

This chart also summarizes the data. You can create both a pivot table and chart for a data source and handle them simultaneously. That means the changes made in the pivot table will reflect in the chart and vice versa.

Data source

Given below is the data source sample that will be used in this tutorial. Click on the link to download the Sample_Data Pivot Chart

Create A Pivot Chart

There are 2 ways to make a pivot chart in Excel.

#1) Create From Data Source

We can create a chart directly from the datasheet without a pivot table.

To achieve this follow the below steps.

#1) Select any cell in the table.

#2) Go to Insert -> Pivot Chart

#3) You can choose to create a new sheet or mention the table range you want to place the chart under Existing Worksheet.

#4) Click OK

This will create a blank pivot chart and its related pivot table. You can add the desired fields to generate a report and chart.

#2) Create From PivotTable

If you have already created a pivot table, you can use the same to generate a pivot chart. We have created a sample PivotTable as shown below.

To create a chart.

#1) Select any cell in PivotTable.

#2) Go to Insert-> Pivot chart

#3) It will give you a list of available charts, select the desired chart.

#4) Click ok.

This will generate a chart with data taken from the pivot table. The pivot chart example is shown below.

Note: Alternatively you can use the shortcut key F11. Click on the pivot table and press F11 on the keyboard.

Customizing The Chart

You can customize the chart using the + and the paint icon present at the right of the chart.

+ Button – It helps you to add or remove chart elements like titles, gridlines, legends, etc and decide their positions.

You can add the title of the chart, mention Axis titles, etc. We have added the chart title and Axis title as an example.

Style of the Chart – You can change the chart style and color by clicking on the paintbrush icon.

You can also change the color of the chart as you desire from the color section.

Recommended Charts

Excel gives us Recommended Pivot charts that allow us to quickly choose the type of PivotChart that meets your business requirements.

#1) Select the data source table.

#2) Go to Insert -> Recommended Charts.

#3) Click the Recommended Charts.

#4) Click on the chart you need.

#5) Click OK

The resulting pivot table and chart will be created in a new sheet and you can further customize them as needed.

Pivot Chart Fields

It has 4 fields as shown below.

1. Filters: Fields under this gives us the ability to add report filters.

2. Legends (Series): Fields under this represent the Column headers in the pivot table.

3. Axis (Categories): This represents the Rows in the Pivot Table. These fields are shown in the Axis Bar on the chart.

4. Values: Used to show the summarized numeric values.

Pivot Charts Tools

Analyze: There are various options available to make the chart more user-friendly.

Chart Name: It is the name of the chart. It is used in writing VBA code and is also in the selection pane. It is available in Excel 2010 and later.

Options: PivotTable Options dialog box will be displayed where you can set Layout & Format, set to show/hide grand total, setting sort options, display options, etc.

Active Field: You can change the column name on the table. For example, Grand Total to Final amount, etc., and the same will get updated in the Table and Chart.

Expand Field: This is used to automatically expand all the values.

If you have multiple fields like Years, Quarters, and Date then instead of expanding individually, you can click on the Expand Field.

Collapse Field: This is opposite the Expand Field. This will collapse the expanded fields and present a compact chart.

Expand Example

Collapse Example

Note: Suppose you have only one field in Rows, then by clicking on the Expand Field, you give a dialog with all the fields and you can select the desired field. The selected field will be added to the Rows section and the Chart will be updated automatically.

Insert Slicer

You can insert a slicer into the chart just like the pivot table.

To integrate the slicer with a chart follow the below steps.

1. Click on the pivot chart.
2. Go to the Analyze tab -> Insert Slicer.
3. In the dialog Select fields, you need to create the slicers.
4. Click OK

This will insert the slicer box as shown below. We have seen how to use slicer in our previous tutorial.

Insert Timeline

You can insert a Timeline into the chart just like a pivot table.

To integrate Timeline with the chart follow the below steps.

1. Click on the pivot chart.
2. Go to the Analyze tab -> Insert Timeline.
3. Select the required Date field.
4. Click OK

This will insert the timeline as shown below. We saw how to use the timeline in our previous tutorial.

The result based on the timeline is updated on both the Pivot table as well as the chart.

Filter Connection

You can link the slicer or timeline to multiple pivot charts. For example, we have created 2 Pivot tables and 1 Slicer. You apply the slicer to both charts.

1. Click on the pivot chart to which the slicer is not currently connected.
2. Go to Analyze -> Filter Connection
3. Select the Slicer you want to connect.
4. Click Ok

Now you can handle both charts with a single slicer.

Calculations

If you want to add any custom formulas, you can do so using the calculation field.

Example:

#1) Select the Pivot chart you want to add the custom formulas to.

#2) Go to Analyze -> Fields ->Items -> Sets

#3) Select Calculated Fields.

#4) In the Name, enter the name you wish.

#5) In Formula, Add your custom formula. If you are giving a 10% discount on the total sum, then you can add a formula as shown below.

#6) The pivot table, pivot fields, and chart will be updated accordingly.

Refresh

Whenever you change the values in the data source, click anywhere on the pivot chart and Right-click and select Refresh or go to Analyze -> Refresh. Refreshing a pivot table also will refresh the chart.

Change Data Source

Whenever you add more rows to the data source, the chart won’t take the added rows, as we have defined the range while creating the chart.

To include the new rows:

1. Click anywhere on the Pivot Chart.
2. Go to Analyze -> Change Data Source
3. Change PivotTable Data Source dialog will appear and you can enter the new data range.
4. Click Ok

Make sure that you do the above steps for all the charts individually.

Clear

Using Clear, you can clear the entire Pivot Chart. It will be an empty Chart and a Table.

1. Click on the Pivot Chart
2. Analyze -> Clear -> Clear All

You can also clear all the applied filters by Analyze -> Clear-> Clear Filters

Move Chart

After creating a chart, you can move it to the desired location.

Follow the below steps:

1. Click on the pivot chart.
2. Go to Analyze -> Move Chart
3. Select the desired option from the dialog:

• • New Sheet: The sheet will be automatically created and the chart will be displayed.
  • Object in: You can select among the available sheets and the chart will be moved to the selected sheet.

Field List: You can show/hide the PivotChart Fields pane.

Field Buttons: You can show/hide the Legend Field, Axis Field, Value Field, Report filter, etc. on the chart.

Design

There are several options available to design the chart under this tab.

Add Chart Element: This gives us the same options as we got when we clicked on the + button next to the pivot chart. They help us to add elements to the chart like title, error bard, etc.

Quick Layout: You can change the default layout and select among the predefined layout available. For example, we have moved the Region layout to the Top instead of the right side.

Change Colors: Select the different colors for your chart.

Chart Style: Choose the Style for your chart from these available charts.

Switch Row/Column: You can easily switch Rows and Columns with just one click and the pivot table and chart will be updated automatically.

Before Switching Row/Column

After Switching Row/Column

Select Data: Suppose you have spent a lot of time formatting a pivot chart according to your company standards and all your charts should be in the same format. Then this option comes in handy. You can’t directly copy the pivot chart and change the data source. There are a couple of steps to be conducted.

#1) Select the desired pivot chart and copy the chart area.

#2) Open a new workbook. File -> New Workbook

#3) Paste the copied chart. You can notice in the Menu bar that it says Chart Tools and not PivotChart Tools.

#4) Now select the Chart area and hit the Cut option.

#5) Go to the workbook where you want to use this chart.

#6) Note: You should already have a pivot table created.

#7) Paste the chart from step 4.

#8) Go to Design present under Chart Tools. Click on Select Data Tab.

#9) Click on any cell in the pivot table.

Pivot Chart will be created with the data present in the new pivot table, but the format remains the same as previously. You can modify the Axis and Legend as needed for the new table.

The resulting chart for the new pivot table is shown below.

Change Chart Type: You can change the default column chart type to the desired type as shown below.

The chart will update automatically based on the selection.

Pie Chart

Bar Chart

Format

These are basically used to custom format the text present inside the chart.

Current Selection: This will show all the elements present in the table and you can select the one in which you want to change the format style. For example, We will select Chart Title and change its style.

#1) Select Chart Title from the drop-down.

#2) Click on Format Selection.

#3) Format Chart Title will Open on the right pane.

#4) Choose the color, style, border, etc as you desire.

After a few basic formatting, a Chart Title will look as below.

Reset to Match Style: This will reset all the changes and give the default style.

Insert Shapes: You can insert shapes like lines, arrows, and also a text box for a better explanation.

Shape Style: You can select different styles for the plot area. Select the area you want to change the style and click on the style.

After applying the styles to the entire chart, the Column and Rows are shown below.

Arrange: If there are multiple pivot charts and they are overlapping on one another on these options.

Bring Forward

• Select the chart you want to bring in front.
• Click on Bring Forward option to bring the chart one step forward.

Bring To Front: This option will bring your chart above all the other charts.

Send Backward

• Select the chart you want to send back.
• Click on the send backward option to send the chart one level back.

Send to Back: This is used to send the selected chart back to all the other charts.

Selection Pane

You can decide the visibility of the chart using the selection pane. This page shows you all the charts and slicer available and you can click on the eye icon to decide whether that particular item should be visible on the worksheet or not.

Size: This is used to customize the pivot chart height, width, scale height, scale width, etc.

Frequently Asked Questions

Q #1) How do you create a pivot chart in Excel?

Answer: There are 2 ways to create pivot charts.

#1) Create From Data Source

• Select any cell in the data source table.
• Go to Insert -> Pivot Chart
• Select the range.

This will create a blank pivot table and pivot chart.

#2) Create From PivotTable

If you have already a pivot table:

• Select any cell in the Pivot Table.
• Go to Insert -> Pivot chart
• It will give you a list of available charts, select the desired chart.

This will create the chart with data relevant to the pivot table.

Q #2) Why do we use a pivot chart in Excel?

Answer:

There are many advantages of using pivot charts:

• It gives an effective and easy way to represent data in a graphical manner.
• You can easily summarize data by dragging the desired fields to any of the 4 available sections of the table.
• Gives an efficient way of changing raw data into an organized format by manipulating them with easy filtering, alignment, customization, calculations, etc.

Q #3) How do I format a Pivot Chart?

Answer: You can format the chart using various options present under the pivot chart Tools. It provides you options to add new fields, change color, font, background, etc., to make your chart look more interactive and presentable. Click anywhere on the pivot chart to open the Tools section.

Q #4) Can I add a slicer to Pivot Charts?

Answer: Yes, slicers and timelines can be added to the pivot charts. This will help us to filter both the chart and the related pivot table simultaneously.

1. Click on the pivot chart.
2. Go to the Analyze tab -> Insert Slicer.
3. In the dialog Select fields, you want to create the slicers.
4. Click OK

You can then add a Filter connection to link one slicer to multiple charts.

Conclusion

In this tutorial, we learned about Excel pivot charts. It is a visual representation of a pivot table or a data source. It helps us to view the summary data in a graphical format with different chart types.

There are multiple options available to filter, format, customize charts, and add various layouts as you desire. A pivot chart in Excel is useful when dealing with a huge amount of data. It’s very useful during a business presentation with one-click filtering, time-wise filtering, customized calculations, etc.

=> Explore The Beginner’s Excel Training Guide