Excel Copy Pivot Chart: Change Its Data Source Safely?

Copying a PivotChart in Excel does not normally give the copy an independent data source: the copied chart remains connected to its underlying PivotTable. To use different data, create a separate PivotTable from that source and insert a new PivotChart from it; changing the original PivotTable’s source can also affect other charts or PivotTables that share its cache.

Why a copied PivotChart keeps its source

A PivotChart is not a standard chart with a freely editable range of cells behind it. It is a visual view of a PivotTable, and its fields, filters, and plotted values come from that table. When you copy the chart object on a worksheet, Excel generally copies the chart while retaining its link to the same PivotTable. The copy is therefore another view of the same pivot data, not a new chart ready to point at an unrelated range.

This connection explains a common surprise: changing a field button or filter on one copy can change the other chart’s view too. Both charts are reflecting the same PivotTable state. Formatting changes, such as changing a chart title or color, can be separate, but the pivot fields and their underlying data are not made independent just by copying the chart.

For an independent chart, the key step is not to copy the PivotChart. Make a separate PivotTable from the intended source, arrange its fields, and insert a chart based on that PivotTable. You can then format the new chart to resemble the original.

Check what the chart is connected to

Click the chart and look for PivotChart-specific controls, such as field buttons or a PivotChart Analyze tab on the ribbon. You can also select the associated PivotTable and look for PivotTable Analyze. A regular chart instead offers controls such as Chart Design and Select Data, where its plotted cell range can usually be edited directly.

To inspect the PivotTable behind a PivotChart, select the chart and use the ribbon’s PivotChart Analyze controls to locate or select the associated PivotTable, if that command is available in your Excel build. You can also look for the table on the worksheet where it was created. If the table is on another sheet, use the sheet tabs and find the PivotTable that supplies the chart. Knowing which table is connected is important before changing its source, especially in a workbook with several dashboards.

In Microsoft 365 for Windows, select a cell in the PivotTable and use PivotTable Analyze > Change Data Source > Change Data Source. If you have selected only a standard chart, that PivotTable command will not appear. A PivotChart does not provide the normal Select Data workflow for replacing its source range.

Change the source used by an existing PivotTable

If you intend to replace the data behind the existing PivotTable, change the table’s source rather than trying to edit the chart’s data range. Save a copy of the workbook first if the PivotTable feeds important reports or several charts.

Click a cell inside the PivotTable that feeds the chart.
Choose PivotTable Analyze > Change Data Source > Change Data Source.
In the dialog, enter or select the new table or cell range, then confirm with OK.
Use PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh, to update its results.
Check the PivotTable and every connected chart for missing fields, changed totals, and unexpected filters.

This approach is suitable when you want the existing report to represent a replacement source and the new data has compatible column headings and structure. For example, a PivotTable summarizing a table with columns named Region, Product, and Sales can usually be redirected to a new table with those same fields. Excel may retain the existing layout, but fields that are absent or renamed can no longer supply the same report.

Changing the source does not mean the copied chart becomes independent. It means the PivotTable it uses now reads from a different source. Any other PivotCharts attached to that PivotTable will continue to reflect the same PivotTable results.

Create an independent PivotChart from new data

When the copied chart must show a different dataset without changing the original report, build a new PivotTable and chart. This keeps the original chart’s connection in place and gives the second report its own pivot layout.

Put the new data in a clean rectangular range with one unique heading per column. Avoid blank header cells, merged cells, and completely blank rows within the data.
Click inside the range. For a growing dataset, consider converting it to an Excel table with Insert > Table, and confirm that the table has headers.
Choose Insert > PivotTable. Confirm the source and choose where to place the PivotTable; select New Worksheet if you want to keep it separate from the original report.
In the PivotTable Fields pane, place fields in the appropriate areas: Rows, Columns, Values, and Filters. Check that numeric fields are summarized as intended, such as Sum rather than Count.
Select a cell in the new PivotTable and choose Insert > PivotChart. Choose a chart type, then set its title and formatting.
Compare the new PivotTable’s totals with the source data before using the chart in a report.

If the new chart should look like the old one, recreate its formatting after inserting it. Depending on Excel’s available chart commands and the chart types involved, you may be able to use a chart template or copy formatting, but these options do not transfer an independent PivotTable connection. Treat the new PivotChart’s PivotTable as the source of truth.

Choose a source that is easy to maintain

A fixed range can work for a one-time report, but it may exclude records added below its last row. An Excel table is often more reliable for regularly updated lists: when you add rows to the table, the table expands, and refreshing a PivotTable based on that table can include those rows. Use a descriptive table name so it is easier to identify in the source dialog.

Before selecting a new source, check these practical details:

Consistent headings: Each column should have a heading, and headings should be unique. A field renamed from “Sales” to “Net Sales” may appear as a different field to the PivotTable.
Consistent data types: A column mixing dates, text, and numbers can summarize or group unexpectedly.
Complete rows: Each record should have its values on one row, with no subtotal rows mixed into the source list.
Source size: A larger source can take longer to refresh and may change totals simply because it includes additional records.
Refresh behavior: A changed source does not guarantee that every PivotTable display updates immediately; refresh the relevant table and verify the result.

For a simple example, suppose the original source is an annual sales table and the new source is a table for a different region. If both have matching headings, a new PivotTable can use the same Rows and Values arrangement while keeping its own regional data. If the new source contains extra fields, those can be added to the new PivotTable without changing the original one.

Understand shared PivotTable caches

Excel stores PivotTable source data in a PivotCache, which supports PivotTable calculations and refreshes. PivotTables created from the same source may share a cache. Consequently, changing one PivotTable’s layout or source can have consequences beyond the chart currently selected. The exact behavior depends on how the PivotTables were created and whether they share a cache, so do not assume that a copied chart or a second PivotTable is fully isolated.

If other reports matter, identify the PivotTables that use the same source and test the change in a workbook copy. After changing a source, inspect related tables and charts for field changes or refresh errors. When separation is important, create the new PivotTable as a distinct report from its own source, then verify its behavior. Creating it from a different table or source helps avoid treating a chart copy as a separate data model, but still check the result rather than relying on appearance alone.

There is an additional distinction for PivotTables built from the Data Model or external connections. Their data may be supplied by a query, connection, or model rather than a plain worksheet range. The standard Change Data Source dialog may not be the right place to replace that data. In those cases, change the relevant query or connection, load or update the model as appropriate, and refresh the PivotTable. The available controls depend on how the workbook was built.

Common problems and how to resolve them

Change Data Source is unavailable: Click inside the PivotTable, not just the chart. If the selected object is a regular chart, use its chart controls instead. A PivotTable connected to the Data Model or an external source may require changing its connection or query.
The new rows do not appear: Confirm that the source range includes them, or that the PivotTable source is an expanding Excel table. Then refresh the PivotTable.
A field is missing: Check the source’s heading spelling and whether the new source actually contains that column. Renamed headings may need to be added to the PivotTable layout again.
The chart still changes when the original changes: The chart is likely still using the original PivotTable. Create a separate PivotTable and insert a new PivotChart from it instead of copying the original chart object.
Values show Count rather than Sum: Inspect the field in the Values area and its value-field settings. Mixed or text-formatted values in the source can cause Excel to count entries instead of summing them.
Old values remain after editing data: Refresh the PivotTable and check the source range. If the source itself has changed, confirm that the PivotTable is pointed at the intended range or table.

On Excel for Mac, the ribbon layout and names can vary slightly by release, but the same principle applies: select the PivotTable to find its analysis and source controls, and create a separate PivotTable for an independent chart. Excel for the web has a more limited set of PivotTable editing features than the desktop app; if the source-change or connection controls are unavailable, open the workbook in the desktop version of Excel.

When a regular chart is a better alternative

If you do not need PivotChart field buttons, interactive PivotTable filters, or automatic pivot summaries, a regular chart may be simpler. Create a helper range containing the exact values you want to plot, then insert a standard chart from that range. In a regular chart, use Chart Design > Select Data to edit the series and category ranges. That command is not the way to replace a PivotChart’s underlying PivotTable source.

A regular chart can be a good choice for a fixed presentation view or a dashboard where you want to control the plotted cells directly. A PivotChart is more useful when viewers need to summarize and filter records through PivotTable fields. Decide based on the interaction and refresh behavior you need, not just on whether one chart object can be duplicated.

Frequently Asked Questions

Can I change the data source of a copied PivotChart only?
A copied PivotChart normally remains connected to its original PivotTable. To keep the original unchanged, create a separate PivotTable from the new source and insert a new PivotChart based on it.

Why is Select Data greyed out for my PivotChart?
A PivotChart gets its plotted fields from a PivotTable, rather than from a chart range that you edit through Select Data. Change the PivotTable source or create a separate PivotTable and chart.

Will changing the PivotTable source change my original chart?
If the original chart uses that PivotTable, it will show the results produced by the changed source after refresh. Other PivotTables can also be affected if they share a cache, so check related reports.

Can I copy a PivotChart to another worksheet and make it independent?
Moving or copying the chart object to another worksheet does not by itself create an independent PivotTable. Build a separate PivotTable first, then create a chart from that table.

Do I need to refresh after changing the source?
Yes. Refresh the PivotTable after selecting the source, then check that its fields and values match the intended data. A source table that grows may also need a refresh before new records appear.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.