How to Change a Pivot Table Data Source in Excel
Point an Excel pivot table at a new or wider range, or at a table, so new rows are included. Different from a refresh that only reloads the existing source.
On this page
Tested in: The sales totals used as a source-vs-pivot check were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click Change Data Source in Excel this session.
If the range is already right and the numbers are stale, use pivot table not refreshing. This page is about pointing the pivot at a different or larger range.
Change the range in Excel
- Click any cell inside the pivot.
- Open PivotTable Analyze (or Options / Analyze in older Excel).
- Click Change Data Source.
- In Table/Range, enter the new range, such as
Sales!A1:F20, or the name of an Excel table. - Click OK, then Refresh if the totals do not update immediately.
After the change, a known total should still match the sheet. On the eight-sale sample, Revenue is 24450, East is 9450, West is 15000, and the months are 7800, 5400, 8850, and 2400. If you widened the range but April (2400) is still missing, the new range may have stopped one row short, or a filter is on.
Prefer a table as the source: select the list, Insert > Table, give it a name, and enter that name in Change Data Source. New rows typed at the bottom of the table are included on the next refresh, so you should not have to change the source every week.
If the new range is much larger than the list and includes empty rows, you can get a (blank) item; tighten the range or see remove blanks from a pivot table.
Change the range in Google Sheets
Click the pivot and, in the editor, edit the Data range. Include the header row. If you added columns, drag the new field into Rows, Columns, or Values. Sheets does not have a separate Change Data Source command; editing that range is the equivalent.
When the button is grey, or the totals look wrong
Change Data Source is grey if you have not selected a cell in the pivot, or if the pivot is based on the data model (Power Pivot). In the data-model case you change the model’s source, not this dialog.
A range in another workbook can break when that file is closed. Keep the source in the same workbook when you can.
If the grand total no longer equals =SUM(F2:F9) (24450 on the sample), you may have included a leftover total row in the new range, excluded a month, or left Values on Count. Fix the range first, then refresh. A pivot chart built on this pivot will follow the new source once the pivot itself is right; if the chart still looks short, see chart not showing all data.