如何更改 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.