Excel 資料透視表重新整理不了：新行、快取與資料來源

Tested in: The Region-by-Product totals used as a sanity check below (East 9450, West 15000, grand total 24450) were computed in Google Sheets on 12 November 2026 and matched a pivot of the same eight-row sales list. Excel 365 menu names are the current desktop labels; I did not click Refresh in Excel this session.

This is a troubleshooting page. If you do not have a pivot yet, start with How to Create a Pivot Table in Excel.

Refresh did nothing: the range is too short

Refresh rereads the cells the pivot already points at. It does not go looking for rows you typed underneath. If the source was $A$1:$F$9 and the new sale is on row 10, the pivot will keep reporting the old grand total — in the eight-row sample, that total is 24450 — until you widen the source.

In Excel 365

1. Click inside the pivot.
2. Open PivotTable Analyze > Change Data Source.
3. Point it at the full list, including the new rows, or at the Table name if you have one.
4. Click OK, then Refresh if the totals have not moved yet.

In Google Sheets

1. Click the pivot.
2. In the editor, edit the range (for example A1:F9 to A1:F20).
3. Confirm the Values well still says SUM, not COUNTA, and that the new region or product appears in the table.

Turning the source into an Excel Table before you build the pivot is the lasting fix. New rows become part of Sales (or whatever you named it), and Refresh reads them without another visit to Change Data Source. Sheets has no exact Table equivalent; keep the named range or the range in the editor up to date.

A leftover Grand Total row inside a newly widened range will double-count. If 24450 suddenly becomes 48900 after you “fix” the source, look for a total you already wrote at the bottom of the list.

Refresh one pivot, or Refresh All

On the PivotTable Analyze tab, Refresh updates the pivot you are in. Refresh All updates every pivot in the workbook, plus other connections (queries, external data) if they exist. Two pivots on the same list can disagree if you refreshed only one of them.

Excel also has a pivot cache: the pivot does not read the sheet on every keystroke. That is why you can edit a revenue cell and the pivot still shows the previous 9450 for East until you refresh. PivotTable Analyze > Options (or right-click the pivot > PivotTable Options) has a checkbox to refresh when the file opens. Turn that on if the workbook is opened more often than it is refreshed by hand.

Sheets generally recalculates the pivot when the source cells change, inside the current range. If a number in the source is wrong, fix the source; there is no cache to clear.

Filters, slicers, and values that look “stuck”

A refresh can succeed and the new row still be invisible. A region filter or a slicer may be set to East only, so a new West sale (which would add to the 15000 West total) never appears. Clear the filter, or check Select Multiple Items in the old filter drop-down, then refresh again.

Value fields set to Count instead of Sum also look like a refresh bug: the grand “total” is 8, the number of rows, not 24450. That is the wrong summary function, not a stale cache. Change the field to Sum and confirm the source column is numeric.

Dates that are really text will not group, and a new date may show up as its own odd label after refresh. See dates stored as text.

External data and protected sheets

If the pivot was built from a query, Power Query, or another workbook, Refresh on the pivot may not be enough. Use Refresh All, or refresh the query first, then the pivot. A broken connection, a moved file, or a prompt you dismissed will leave the cache where it was.

A protected sheet can block Refresh or Change Data Source. Unprotect it, refresh, then protect it again if you still need the lock. A pivot on a hidden sheet is still in Refresh All; you do not have to unhide it just to update it.

Formulas that point at a pivot cell with a regular address such as =K3 can look stale after a layout change even though the pivot itself refreshed. That is a reference problem, not a refresh problem. Use GETPIVOTDATA as described on the pivot table how-to.

A short check before you rebuild the pivot

Compare the source to the pivot. =SUM(F2:F9) on the sample list is 24450, and =SUMIFS(F2:F9,B2:B9,"East") is 9450. If those sheet formulas already include the new rows and the pivot does not, the source range or a filter is the culprit. If the sheet formulas also miss the new rows, you are totaling the wrong cells, and refreshing the pivot will not help.

Rebuild the pivot only after you have checked the range, the filters, and Count vs Sum. Rebuilding last is fine; doing it first throws away slicers and layout for a problem that Change Data Source would have fixed.

If you chart the pivot, refresh the pivot before you worry about the chart. A chart tied to a stale pivot is covered, from the charting side, in How to Make a Chart in Excel.