After you create a pivot table, you can add a pivot chart that is based on the pivot table. We'll see how to create a pivot chart, and then connect that pivot chart to a different pivot table. This is a useful technique if you've spent a long time setting up a pivot chart, with specific formatting, and you want to use a copy of that chart with a different pivot table.
To quickly create a pivot chart, you can use keyboard shortcuts, as shown in this short video tutorial. Written instructions are below the video.
To quickly create a pivot chart, you can use one of the following shortcuts.
The pivot chart will be in the default chart type. You can change to a different chart type, and adjust the chart's formatting. The chart below is a stacked bar chart, with the axis items in reverse order, and the axis crossing at the maximum value.
In Excel 2013 and later, you can create a pivot chart directly from the source data, without creating a pivot table first.
The pivot chart will be inserted on a new worksheet, and its connected pivot table will be created on the same sheet.
Unlike a normal chart, you can't change the source data in a pivot chart. To see the data source for the selected chart:
You can see the address of the chart's data range, but you can't make any changes to the range.
Watch the video below, to see a workaround for this problem. The written instructions are below the video.
Although you can't directly change the pivot chart source, you can follow a few easy steps, to make the change.
We'll make a copy of the original chart, and then unlink it from the pivot table, to create a static chart:
If you click on a series in the pasted chart, you'll see the labels and values, instead of references to the original pivot table. The Ribbon tab says "Chart Tools" now, instead of "PivotChart Tools".
Next, you can link the chart to a different pivot table.
The static chart changes back to a pivot chart, and if you open the Select Data Source window again, you'll see the reference to the new pivot table.
To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:
and much more!
Last updated: November 15, 2017 12:33 PM