Excel 2013: How to Use PivotCharts

A PivotChart is like a PivotTable in that it summarizes the data from a worksheet. However, where a PivotTable is simply a table with data listed in it, a PivotChart is a graphical representation of the data.

In this article, we are going to learn how to:

Create a pivot chart

Change the fields in a pivot chart

Format a pivot chart

Change the type of pivot chart

Add filters to a pivot chart

Hiding the buttons in a pivot chart

Moving a pivot chart

Deleting a pivot chart

Creating a PivotChart

To create a pivot chart, go to the Insert tab.

Go to the Charts group.

Here you can see Recommended Charts, graphical representations of the types of charts you can add, as well as the PivotChart button.

Click PivotChart to display the dropdown menu, then click PivotCharts again.

You will then see the Create PivotChart dialogue box.

The range of cells should already be filled in for you, just as it was with PivotTables.

Also, just as with PivotTables, you can decide if you want to place your chart in a new worksheet or an existing one. We are going to choose a new worksheet.

Click OK.

We can now start building our pivot chart.

Just as with PivotTables, we are going to go to the PivotChart Fields panel on the right to design our pivot chart.

Drag and drop the fields from the top section of the panel into the sections located in the bottom half of the panel.

These sections are Filters, Legend (Series), Axis (Categories), and Values. The Axis is essentially the row, and the Legend is essentially the column.

Below you can see the fields that we dropped into the sections.

We can see our pivot chart in the worksheet.

Notice that a pivot table also appears. You can either hide the columns of the pivot table so you cannot see them, or you can move the pivot chart to its own worksheet.

To do this, go to the Analyze tab under PivotChart Tools. Click the Move Chart button.

You will then see the Move Chart dialogue box.

Put a check beside New Sheet, then enter a name for the new sheet.

Click the OK button.

The chart then appears in its own worksheet.

Changing the Fields

In our current pivot chart, we have the Item and Price Each field.

The items appear across the bottom of the chart.

The price of each item is represented by the vertical bars.

By looking at the bars, we can tell that board game and the electronic game are our most expensive items.

If we want to change the fields that are displayed – or add more fields, we go to the PivotCharts Field panel on the right.

To remove a field, drag the field from the bottom half of the panel to the top half.

To add a new field, drag the field from the top half of the panel to the bottom half. Drop it into the section where you want it to appear.

Formatting PivotCharts

To format a pivot chart, you will use the two buttons that appear at the top right corner of your pivot chart. They are pictured below.

Click on the button with the paint brush on it to choose a style and color for the bars in your chart.

As you can see in the snapshot above, when you click on the Style tab, you can pick a style.

Click on the Color tab to pick a color. Select a row of colors to pick a color scheme.

Want to learn more? Take an online course in Advanced Excel 2013.

Click on the button with the paintbrush to close the window when you are finished.

We added a new style and color to our pivot chart below.

Now let's click the button with the plus sign (+) on it.

This shows you the available chart elements.

The chart elements that you are using have checkmarks beside them.

Put a checkmark beside any chart element that you want to add to your pivot chart.

Uncheck any elements that are currently checked, but you want to remove.

When you are finished, click the button with the plus sign again to close the window.

Changing the PivotChart Type

The bar chart is the default pivot chart layout. However, you can easily change the pivot chart type once you've created your pivot chart.

To change the chart type, click on the pivot chart so that it's active.

Click on the Design tab under PivotChart tools, then click the Change Chart Type button.

You will then see the Change Chart Type dialogue box.

On the left side of this dialogue box, you will see the different types of charts that you can use.

Click on one of these. We are going to click on Pie.

On the top right side of the dialogue box, you will see the graphical representations of the different pie charts that you can add.

Below this, you will see a thumbnail image of how your chart will look.

We don't see anything here we want to use, so we are going to click Line on the left.

Once you find the chart that you want to use as we have done above, click OK.

Now that we have a new chart type, we may want to format it.

We are going to click the plus button, and remove the data labels element. However, when we do this, we won't be able to see the actual prices anymore, so we are going to add a data table element.

Adding Filters to a PivotChart

Filtering data on pivot charts is almost just the same as filtering data in pivot tables.

Let's learn how to do it.

Our pivot chart is pictured below.

Currently, our chart shows us all of the items we have listed, but we might not want all of those items showing in our chart. To filter the data and determine what items are displayed, click the Item button in the lower left corner. Naturally, you may not have a field named Item in your chart, so you will just click the button in the lower left hand corner of your chart.

When we click the button, this is what we see:

You can see checkmarks beside all of our items. Uncheck the items that you do not want to appear in the chart.

Click OK when you are finished.

As you can see, some items have been filtered out of our chart.

If you look at the Item button in the lower left corner, you will see a Filter icon to let us know the data has been filtered.

We can also use the Item button to sort our items alphabetically, either ascending or descending.

Click on the button.

Choose Sort A to Z or Sort Z to A. You can also click More Sort Options to sort by values.

That said, you may want to filter the data in your chart by fields that do not appear in the chart. We did this with pivot tables.

To do this, go to the PivotChart Fields panel on the right.

Drag the field you want to use to filter the data into the Filter section in the bottom half of the panel.

We have chosen to filter our data by Date Received.

Now go back to your chart. Click the Date Received button (or the button for the field that you are using to filter the data) located at the top right of the chart.