Excel Pivot Table: How to Change the Data Source

To change an Excel PivotTable’s source in Microsoft 365 for Windows, click inside the PivotTable, select PivotTable Analyze > Change Data Source > Change Data Source, enter or select the new table or range, and click OK. Then refresh the PivotTable to update its results.

What changing the data source actually does

A PivotTable summarizes data from a source, such as a worksheet range, an Excel table, an external connection, or the workbook’s Data Model. Changing the source tells Excel which records and fields the PivotTable should read. It is different from refreshing: a refresh rereads the existing source, while changing the source points the PivotTable somewhere else or changes the boundaries of the range it reads.

For example, suppose a PivotTable summarizes orders in A1:F200 on a sheet called Orders. If you add rows through row 250 but the PivotTable still uses A1:F200, refreshing will not include the new orders. You must extend the source range or use an Excel table that includes those rows, and then refresh.

Changing the source does not necessarily preserve every part of the old report. If the new source has different column names, Excel may remove fields that no longer exist. If it has new fields, they become available in the PivotTable Fields pane, but you decide whether to add them to the report.

Change a PivotTable to a new range

Use this method when your data is an ordinary worksheet range or when you need to point the PivotTable at a different block of cells. Make sure the new source has a header row, with one distinct, nonblank heading for each column.

Click any cell inside the PivotTable you want to update.
On the ribbon, choose PivotTable Analyze > Change Data Source > Change Data Source. In some Excel builds, the command may be shown simply as Change Data Source.
In the dialog box, check the Table/Range box. Type the new reference or select the source cells on the worksheet. For example, a source on the Orders sheet might be Orders!$A$1:$F$250.
Check that the range includes the header row and every record, but not unrelated notes or totals beneath the data.
Click OK, then review the field list and refresh the PivotTable if the results do not update immediately.

If the new source is on another sheet, include that sheet’s name in the reference. If a sheet name contains spaces, Excel normally surrounds it with apostrophes, as in 'Monthly Orders'!$A$1:$F$250. Selecting the cells directly in the dialog is often easier than typing a reference, especially when sheet names or range boundaries are easy to mistype.

Use an Excel table for data that keeps growing

If you regularly append records, converting the source range to an Excel table is usually more dependable than repeatedly changing a fixed range. Click a cell in the source data and choose Insert > Table, or press Ctrl+T on Windows. Confirm that My table has headers is selected when the first row contains field names. You can give the table a clear name on the Table Design tab, such as SalesData.

To switch an existing PivotTable to that table, open PivotTable Analyze > Change Data Source > Change Data Source and enter the table name in the source box. Once the PivotTable uses the table, new rows that become part of that table are included when you refresh. This avoids changing a range reference from, for example, row 250 to row 500 every time the dataset grows.

Tables do not make every new row appear in a PivotTable automatically. Excel must first include the row in the table, and the PivotTable still needs a refresh. Check the table boundary if you pasted data several rows below it or added a row in a way that did not expand the table. You can resize the table from Table Design > Resize Table.

Choose the right source for your situation

The best source depends on where the records come from and how they change. A range can be convenient for a one-time report, but a table is generally easier to maintain for an expanding list. A PivotTable built from a query, database, or Data Model has a different source structure and may need to be changed where that connection or model is defined.

Fixed worksheet data: Use the precise cell range if the source is stable and you want to include a known set of rows.
Frequently added rows: Use an Excel table so the source can expand with the data, then refresh the PivotTable.
Another worksheet or workbook: Select the intended range carefully and confirm the correct file and sheet are available. External workbook links can be affected if the file is moved or renamed.
Power Query or database data: Update the query, connection, or loaded table as appropriate, then refresh the data and PivotTable. Changing a PivotTable range is not a substitute for editing the query that supplies it.
Data Model or OLAP source: Manage the model or connection rather than expecting the normal range-editing dialog to work. Some source-change options are unavailable for these PivotTables.

When the command is missing or disabled, the PivotTable may be based on an external connection or the Data Model rather than a plain worksheet range. Check the source type before rebuilding the report: replacing the PivotTable can discard its layout, formatting, filters, and connected slicers.

Changing the source versus refreshing

Refresh is the right first step when the source already covers all the records and you have only changed values, added rows inside a table, or updated a connected data source. Select the PivotTable and choose PivotTable Analyze > Refresh. To refresh workbook data connections and PivotTables together, use Data > Refresh All.

Change the source when the PivotTable points to the wrong sheet or table, when a fixed range stops before the latest records, or when you want it to use a different dataset. Refreshing a PivotTable cannot make it read cells outside its current source. Conversely, changing the source does not guarantee that a query or external connection has fetched its latest data; that may need its own refresh first.

On Windows, Alt+F5 refreshes the selected PivotTable, and Ctrl+Alt+F5 refreshes all data connections and PivotTables in the workbook. Keyboard shortcuts can differ on Mac depending on the keyboard and Excel version, so use the ribbon command if those combinations do not work. In Excel for Mac, select the PivotTable and look for the corresponding commands on the PivotTable Analyze tab. Excel for the web has a more limited feature set; open the workbook in desktop Excel if the source-change command is unavailable.

Common problems and how to fix them

New rows are missing after refresh: Check the source address shown in the Change Data Source dialog. If it is a fixed range, extend it to include the rows or change the source to a table. For a table, confirm the new records are inside the table boundary.
The field list lost a field: The new source may not contain a column with the same header. Restore the correct header or add the field to the source, then change the source and refresh. Avoid blank or duplicate headings.
Excel reports that the PivotTable field name is not valid: Inspect the source’s header row for empty cells, merged headings, or a selected range that starts below the headings. Each column needs a usable heading.
The PivotTable looks unchanged: Verify that you changed the intended PivotTable, selected the correct range, and clicked OK. Then refresh it. A similar-looking PivotTable elsewhere in the workbook may be based on a different source.
Old items remain in a filter: PivotTable caches can retain items that no longer occur in the current data. In the PivotTable Options dialog, the data settings include a choice for how many items to retain per field; selecting None and refreshing can remove retained old items. The exact dialog layout can vary by Excel version.
Other reports or slicers change too: Some PivotTables share a cache or connections. A source change can affect related PivotTables or their shared filtering. Check dependent reports after changing a source, and consider whether they should continue using the same underlying data.

Before changing a source in a workbook used by other people, save a copy. Afterward, check totals against the source data, review filters and grouped fields, and confirm that charts or slicers still show the intended results. A changed dataset can make a previously valid grouping or calculated field unsuitable even if Excel accepts the new source.

What changes when the source has a different structure

A new source can have more rows, fewer rows, or a different set of columns. Additional rows usually require only a wider source boundary and a refresh. Different columns deserve more care: a PivotTable’s layout depends on source field names, so renamed or removed headings can leave report areas empty or remove fields from the field list.

Try to keep column headings stable when regularly replacing data. For instance, if one month’s heading is “Order Date” and the next month’s file calls it “Date Ordered,” Excel treats those as different fields. Standardizing the heading before replacing or appending data makes refreshes and report maintenance more predictable. Also keep each source column consistent in meaning; mixing dates and text in a date column can complicate grouping and filtering.

If the replacement source has extra columns, they generally become available to use, but they do not automatically appear in the report. If you want an entirely different report, move fields in the PivotTable Fields pane after changing the source. If the source comes from a query or the Data Model, make structural changes in that source first and then refresh the model or connection and the PivotTable.

Frequently Asked Questions

How do I change the source range of a PivotTable in Excel?
Click inside the PivotTable, choose PivotTable Analyze > Change Data Source > Change Data Source, edit the Table/Range reference, and click OK. Refresh the PivotTable if needed.

Why is Change Data Source grayed out?
The PivotTable may use an external connection, an OLAP source, or the Data Model, for which the standard worksheet-range command may not apply. Check the connection or model that supplies the data, or open the workbook in desktop Excel if you are using Excel for the web.

Will a PivotTable automatically include new rows?
Not always. A PivotTable based on a fixed range does not automatically extend that range. A PivotTable based on an Excel table can include added rows once they are inside the table, but you still need to refresh the PivotTable.

Do I need to change the source every time the data changes?
No. If the source already includes the updated records, refresh it. Change the source only when the PivotTable needs to read a different range, table, or data source.

Can I change the source for a PivotTable connected to Power Query?
Usually you should edit or refresh the query or its loaded output, depending on how the workbook is set up. A PivotTable based on a connection or Data Model may not offer the same range-editing options as one built directly from worksheet cells.

Can I use a table name instead of a cell range?
Yes. A PivotTable can use an Excel table as its source. Enter the table’s name in the Change Data Source dialog, then refresh after the table’s contents or boundaries change.

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.