Excel Pivot Chart: How to Change the Data Source Safely

To change an Excel PivotChart’s data source, select its associated PivotTable, choose PivotTable Analyze > Change Data Source, enter or select the new range or table, and click OK. Then refresh the PivotTable. A PivotChart is tied to its PivotTable, so changing the chart’s source is not the same as changing the source of a standard chart.

How a PivotChart gets its data

A PivotChart displays the summarized results of a PivotTable. The PivotTable organizes source records into fields such as dates, products, regions, and sales; the chart visualizes the resulting totals, counts, or other calculations. The chart’s field buttons, filters, and layout are connected to that PivotTable rather than to a separate list of worksheet cells.

This connection explains a common point of confusion: selecting a PivotChart and looking for the regular chart command to select or edit data may not provide the expected controls. To change which records and fields feed the report, change the PivotTable’s source. To change only the way the existing report is displayed, adjust PivotTable fields, filters, or chart settings instead.

Changing the source does not automatically mean that the report’s layout will change. If the replacement data has different column headings, the PivotTable may show new fields, lose access to old ones, or need its field arrangement adjusted. Keeping a consistent set of headers makes a source change much smoother.

Change the source in Excel for Microsoft 365 on Windows

Use the following process for a PivotChart based on a worksheet range or Excel table:

Click the PivotChart, then locate and select the PivotTable that supplies it. If you are unsure which table is connected, click a chart field button or inspect nearby report sheets; selecting the chart alone may not make the source command easy to find.
Click a cell inside the PivotTable. The contextual PivotTable Analyze and Design tabs should appear on the ribbon.
Choose PivotTable Analyze > Change Data Source > Change Data Source. In some ribbon layouts the command is displayed directly on the PivotTable Analyze tab.
In the dialog box, enter a worksheet range or select the new source cells. You can also enter the name of an Excel table, such as SalesData, in the Table/Range field.
Check that the selected source includes the header row and all intended data columns, then click OK.
Refresh the PivotTable using Data > Refresh All, or right-click inside the PivotTable and choose Refresh. Check the PivotChart and its fields after the refresh.

For example, suppose a report currently uses January!$A$1:$D$450 and the records have moved to a sheet named All Sales. You can select the PivotTable, open the Change Data Source dialog, and replace the old reference with the appropriate range on All Sales. Sheet names containing spaces need single quotation marks in a worksheet reference, as in 'All Sales'!$A$1:$D$900. Selecting the cells with the dialog can help avoid a typing mistake.

Use an Excel table when records keep growing

If new records are added regularly, an Excel table is usually a more dependable source than a fixed range. Select the source data and choose Insert > Table, or use Home > Format as Table, and confirm that the table has headers. Give it a recognizable name from Table Design > Table Name, such as SalesData. Set the PivotTable source to that table name through the Change Data Source dialog.

When you append records directly below the table, the table normally expands to include them. The PivotTable still needs to be refreshed before its results and chart reflect those added records. This is an important distinction: a table can expand automatically, but a PivotTable does not necessarily recalculate its displayed summary the moment new rows are entered.

New columns require a little more attention. Add a column as part of the table so the table includes it, then refresh the PivotTable and look for the new field in the PivotTable Fields pane. If it is missing, confirm that the column has a valid header and that the PivotTable source is the table rather than an older fixed range. Avoid blank header cells and duplicate or inconsistent headings, which make fields harder to identify and can interfere with a clean report.

Change a fixed range or switch to a different source

A fixed source such as Sheet1!$A$1:$D$500 includes only the cells in that address. If you add row 501, the row is outside the original source until you expand the range. To include more rows, open Change Data Source and select the larger range, for example through row 501. If the replacement data is on another worksheet, select that worksheet’s range instead.

When switching to a separate data set, check its structure before applying it. PivotTables work best when each column has one clear field name and each row represents one record. A sales list might contain Order Date, Product, Region, and Amount, with no merged header cells or subtotal rows inside the source. Replacing a tidy transaction list with a pre-summarized report can produce confusing results because the PivotTable may summarize already aggregated numbers.

If the new source has different fields, drag the available fields into the appropriate areas—Rows, Columns, Values, or Filters—and confirm the value calculation is right. For instance, a numeric Amount field might be summarized by Sum, while a text Order ID field is more likely to be counted. A changed source can also affect number formats, calculated fields, and filters, so inspect the report rather than assuming that its previous configuration still fits.

Refresh the chart and verify the results

Changing the source tells the PivotTable where to find data; refreshing tells it to retrieve and summarize the current records. In Excel for Windows, use Data > Refresh All to refresh workbook data, or right-click the PivotTable and select Refresh for that report. The chart updates as its linked PivotTable updates.

After refreshing, check more than whether the bars or lines changed. Confirm that expected categories are present, totals make sense, date fields group as intended, and filters or slicers are not hiding the records you expected to see. A useful check is to compare a known category total in the PivotTable with a separately verified total from the source data. If the chart is blank or unexpectedly small, inspect its PivotTable filters and the source’s headers and range.

A refresh does not repair every source problem. It will not include rows outside a fixed range, correct a misspelled field name, or make incompatible columns equivalent. Fix the source definition or source data first, then refresh again.

External connections and Data Model PivotCharts

Not every PivotChart uses an ordinary worksheet range. A PivotTable may use an external connection, a Power Query result, or the Excel Data Model. In these cases, the Change Data Source command can be unavailable, restricted, or unsuitable for editing the underlying connection. Do not assume that a disabled command means the PivotChart is broken; it may indicate that the source is managed somewhere else.

For a Power Query source, use Data > Queries & Connections, locate the query, and choose Edit to review its steps and source. The exact change depends on whether the query reads a workbook, file, folder, database, or another source. After updating the query, load or refresh the result as appropriate, then refresh the PivotTable and check the chart.

For a connection-based PivotTable, inspect Data > Queries & Connections or the workbook’s available connection controls. A Data Model report may require changes to the model’s tables or the query that loads them rather than a new cell range in the PivotTable dialog. If the workbook is maintained by someone else or depends on a managed database connection, confirm the intended source and permissions before editing it.

Common problems and how to fix them

Change Data Source is missing or disabled: First click inside the PivotTable, not just the chart. If the source is a Data Model or external connection, edit the query or connection instead of trying to replace a normal range.
New rows do not appear: A fixed range may not include them. Expand the source range or change the source to an Excel table that includes the records, then refresh.
A new field is absent: Check whether the source includes that column, whether it has a nonblank header, and whether the PivotTable is based on the updated table or range. Refresh after correcting the source.
The chart shows unexpected categories or totals: Review PivotTable filters, slicers, and the field arrangement. Verify whether numeric values are summarized by Sum, Count, or another operation that suits the data.
Another PivotTable changes too: Some reports can share a PivotTable cache or data connection. Check related reports after changing a source, especially in a workbook where several PivotTables were created from the same original data.
The replacement source causes errors or odd results: Compare the column headers and data types with the original source. Keep one record per row, use consistent headers, and avoid mixing totals or notes into the source list.

Excel for Mac and Excel for the web

Excel for Mac also provides PivotTable source and refresh controls in the desktop application, although the ribbon layout and the exact placement of contextual commands can differ from Windows. Select a cell in the PivotTable and look for the PivotTable analysis or options controls and the command to change the data source. If a workbook uses an external connection or the Data Model, available editing options can depend on the source and Excel version.

Excel for the web can open and work with many PivotTables, but its available PivotTable editing features may differ from the full desktop app. If you cannot find the source-change command or the workbook depends on a connection feature that the browser does not support, open the file in desktop Excel and make the change there. In either environment, save the workbook and verify the refreshed chart after changing the source.

Frequently Asked Questions

Can I change a PivotChart’s data source directly?
Change the source through the PivotTable linked to the chart. A PivotChart is not an independent standard chart with a separate cell-range source. Select the PivotTable and use PivotTable Analyze > Change Data Source for a normal worksheet-based source.

Why can’t I click Change Data Source?
You may have selected only the chart, or the PivotTable may use an external connection or the Data Model. Click inside the PivotTable first. If the command remains unavailable, inspect the query or connection that supplies the report.

Will a PivotChart update automatically when I add rows?
Not necessarily. A fixed range does not automatically extend to new rows. An Excel table can expand as records are appended, but refresh the PivotTable so its summary and chart include those records.

Does changing the source delete my chart formatting?
Changing the source does not inherently replace the PivotChart with a new chart, but different fields or categories can change what it displays. Review the chart, field layout, filters, and formatting after the source change and refresh.

Can I use a named range as the new source?
For a worksheet-based PivotTable, a defined name that refers to a suitable rectangular source may be entered in the Table/Range field. Ensure that the name covers the header row and all intended records, and refresh after applying it. An Excel table name is often easier to maintain when the list grows.

Before you finish

For a one-time correction, replacing the existing range in Change Data Source may be all that is needed. For a report that will continue to receive records, using a well-structured Excel table can reduce the chance of leaving new rows outside the source. For Power Query, connection-based, or Data Model reports, edit the upstream query or connection rather than forcing a worksheet-range change.

Whichever method you choose, confirm the source address, refresh the report, and validate a few totals and categories. Those checks catch the most common problems: a source range that stops too early, a header row that was missed, or a filter that makes a correct refresh look incomplete.

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.