如何更改 Excel 数据透视表的数据源
单击透视表，打开“数据透视表分析 > 更改数据源”，将表/区域改为完整列表或表名称，然后刷新。仅刷新不会纳入旧区域下方的新行。把列表做成表并作为源，以后在表底部新增的行会在下次刷新时进入透视表。
本文目录
快速解答
单击透视表，打开“数据透视表分析 > 更改数据源”，将表/区域改为完整列表或表名称，然后刷新。仅刷新不会纳入旧区域下方的新行。把列表做成表并作为源，以后在表底部新增的行会在下次刷新时进入透视表。
检查要点
更改后，已知合计应与工作表一致。八行样本中，收入为 24450，东区 9450，西区 15000，各月为 7800、5400、8850、2400。若扩大了区域仍缺少 4 月的 2400，可能新范围少了一行，或仍有筛选。源区域过大并包含空行时，可能出现“(空白)”项。
边界与兼容性
若按钮是灰色，可能没有选中透视表，或透视表基于数据模型，那时要改模型的源。另一工作簿中的区域在该文件关闭时可能失败。Google Sheets 在透视表编辑器的数据框中修改范围，没有单独的“更改数据源”命令。
完整英文示例、公式与作者备注
Excel How-toTested 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.