Change Pivot Chart Source Data

In an Excel file, you might have a couple of pivot tables on different worksheets. If you create a pivot chart for one of those pivot tables, you might spend a long time setting it up, with specific formatting and design settings.

It would be nice to copy that chart, and use it for another pivot table, but you can’t alter the source data for a pivot chart.

We’ll take a look at the Data Source setting, and see how you can connect a chart to a different pivot table, by using a workaround.

Pivot Chart Source Data

Unlike a normal chart, you can’t change the source data in a pivot chart.

To see the data source for the selected chart, follow these steps:

1. On the Ribbon, click the Design tab, under PivotChart Tools
2. Click Select Data, to open the Select Data Source window.

In the Select Data Source window, you can see the address of the chart’s data range, but you can’t make any changes to that range.

Unlink the Pivot Chart

So, you can’t directly change the pivot chart source, but with a few easy steps you can solve this problem.

First, follow these steps to make a copy of the chart, and unlink it from the pivot table. That will create a static chart:

1. Right-click the pivot chart’s Chart area or border, and then in the popup menu, click Copy.
2. On the Excel Ribbon, click the File tab, and click New, then create a new blank workbook.
3. On the Excel Ribbon, click the Home tab, and click Paste.

Check the Unlinked Chart

To see what happened to the chart, when you unlinked it, follow these steps:

• Click on a series column in the pasted chart
• Look in the Formula Bar, to see the series formula

In the Formula Bar, there aren’t any cell references now. Instead, the formula shows text and numbers:

• label names – Bars, Los Angeles, San Diego
• values – 12009, 6972

That’s the result of unlinking the pivot chart from its pivot table.

Link Pivot Chart to Different Pivot Table

Next, you can follow these steps, to link the pivot chart to a different pivot table.

1. Right-click the pivot chart’s Chart area or border
2. In the pop-up menu, click Cut.
3. Switch to the workbook that has the pivot table that you want to use as the chart’s new data source.
4. With the chart selected, on the Excel Ribbon, click the Design tab, under Chart Tools
5. Click Select Data, to open the Select Data Source window
6. Click any cell in the pivot table, and click OK

Pivot Chart Linked to Different Pivot Table

As soon as you make that link to the different pivot table, the static chart changes back to a pivot chart.

To check the data source, open the Select Data Source window again

In that window, you’ll see the dimmed-out reference to the new pivot table.

Watch the Change Data Source Video

To see the steps for changing a pivot chart’s data source, watch this short video.

Get the Workbook

To get the Excel workbook for this article, go to the Excel Pivot Chart Source page on my Contextures site.

The zipped Pivot Chart Source Data workbook is in xlsx format, and does not contain any macros.

___________________________