Change a Pivot Table’s Source Range Without Rebuilding It

Change the source when a pivot table points to the wrong cells, excludes newly added rows, or needs to use a replacement dataset. The workflow is:

1. Select the pivot table.
2. Replace its source range or table.
3. Refresh the result.
4. Check fields, filters and totals.

For example, suppose the original source is Sales!$A$1:$E$101:

Date	Region	Product	Units	Revenue
2026-09-01	North	Widget A	4	200

After adding 400 records, change the source to Sales!$A$1:$E$501. Include row 1 because its headers become the pivot-table field names.

Change the source in Excel

For a pivot table based on worksheet cells or an Excel table:

1. Click anywhere inside the pivot table.
2. Open PivotTable Analyze on the ribbon. In some older Excel versions, this tab is named Options.
3. In the Data group, select Change Data Source, then Change Data Source again.
4. Choose Select a table or range.
5. In Table/Range, enter a table name such as SalesData, type the complete cell reference, or select the desired range on the worksheet.
6. Select OK.
7. Right-click the pivot table and select Refresh, or use PivotTable Analyze > Refresh.

These steps follow Microsoft’s current instructions for changing a PivotTable to another Excel table or cell range. A PivotTable based on the Workbook Data Model cannot have its source changed through this standard route (Microsoft Support).

If you only added rows

Changing the source is necessary when a fixed range ends before the new records. A more durable fix is to use an Excel table:

1. Select a cell in the source data.
2. Choose Insert > Table and confirm that the table has headers.
3. Give the table a useful name, such as SalesData.
4. Change the pivot source from the fixed range to that table name.

Excel tables expand as rows are added. Microsoft says new and updated records in an Excel table are included when its PivotTable is refreshed (Microsoft Support).

You may still need to refresh, depending on your Excel version and settings. For a manual refresh, use PivotTable Analyze > Refresh, right-click the pivot table and choose Refresh, or press Alt+F5. Refresh All updates all PivotTables in the workbook (Microsoft Support).

Refresh and change source are different actions: Refresh rereads the current source, while Change Data Source points the pivot table somewhere else.

Change the source range in Google Sheets

1. Click the pivot table.
2. Click the pop-up Edit button beneath it.
3. In the Pivot table editor, click Select data range.
4. Enter or select the replacement range, including its header row.
5. Confirm the range, then review Rows, Columns, Values and Filters.

Google says a Sheets pivot table refreshes whenever a cell in its source range changes. That does not expand a fixed range: if the source is Sales!A1:E101, rows below 101 remain outside it until you change the range (Google Docs Editors Help).

Check the result before relying on it

Reusing the existing pivot layout is most straightforward when the replacement data has the same column names and compatible value types. Check these items immediately:

• Headers: Every source column should have one nonblank, distinct label. Renaming Revenue to Sales value, for example, may require replacing the old field in the pivot configuration.
• Range boundaries: Confirm that the header row and last data row are included, while notes and totals beneath the dataset are excluded.
• Row count: Compare the new source’s observation count with the expected count.
• Aggregation: Make sure numeric fields still use Sum, Average or the intended calculation rather than Count. Numbers stored as text can change the result; see Excel Pivot Tables Start With Clean Data.
• Filters and groups: Look for filters that hide new categories and date groups that no longer match the replacement data.
• Grand total: Compare the pivot total with a direct sum or another independently calculated control total.

Microsoft recommends column labels in the first row and consistent data types within each source column. It also advises removing source subtotals and grand totals before creating a PivotTable (Microsoft Support). If the replacement dataset has substantially different columns, creating a new pivot table may be safer than repairing the old layout.

Prepare the output for sharing

A pivot table is a summary, not a substitute for a clean source dataset. Before publishing either one, remove embedded subtotal and grand-total rows from the observation-level source; otherwise, the pivot can aggregate those totals again. Separate Spreadsheet Totals From Data Rows Before Export shows how to keep observations and summaries distinct.

Publish only data intended for public access. Include the source, coverage period and update date, and verify that the public table’s totals match the checked pivot result.