How to create a pivot chart

All pivot charts are based on pivot tables, so in order to have a pivot chart you must also have a pivot table.

Let’s take a look.

If you’re creating a pivot chart from scratch, first select a cell in the source data. Then go to the Insert tab and click the Pivot Table menu. From the menu, choose Pivot Chart.

Similar to creating a pivot table, you’ll need to confirm the data source and the location. Note the title of the window - Create Pivot Table with Pivot Chart. We’ll click OK to accept the defaults.

The result is a new blank pivot table and pivot chart, both on the same worksheet. As with a regular pivot table, the field list pane appears and disappears when we select the pivot table and the pivot chart.

Note that the names in the pivot list pane are slightly different when the chart is selected - Row Labels are called Axis fields, and Column labels are referred to as Legend Fields.

Let’s select the chart and add Total Sales and Region. As you can see, any change we make to the pivot chart is reflected in the pivot table.

If we select the pivot chart and move Region from the Axis field area to the Legend field area, both the pivot chart and the pivot table are reconfigured. If we select the pivot table and move Region back to the Row Labels area, we reverse the change.

Now let’s delete this worksheet and look at how to create a pivot chart from an existing pivot table.

First, we’ll create the new pivot table.

When you want to create a pivot chart from an existing pivot table, first select any cell in the pivot table. Then, navigate to the Options tab of the PivotTable Tools ribbon, and click PivotChart. After you select a style and click OK, you’ll see a pivot chart added to the same worksheet as the pivot table.

Once you’ve created a pivot chart, you can easily move it into its own worksheet. Just right-click and select Move Chart. Then select New Sheet and click OK. To move the pivot chart back to the worksheet, just reverse the process.