Why a PivotTable doesn't update (and how to fix it)
Causes and fixes
- The PivotTable hasn't been refreshed
- New rows are outside the source range
- It should refresh by itself when the file opens
- Deleted items still show in the filter list
- Amounts show as Count instead of Sum
1The PivotTable hasn't been refreshed
A PivotTable works from a saved copy of the data. Changing the source cells doesn't change it until you refresh.
Fix
Right-click the PivotTable and choose Refresh, or press Alt+F5 on Windows.
2New rows are outside the source range
If the PivotTable was made from A1:D200 and you add rows 201 and below, refreshing still reads only A1:D200.
How to recognise it
Click the PivotTable, then PivotTable Analyze > Change Data Source. The range ends before your last row.
Fix
Turn the source into an Excel table: click in the data and press Ctrl+T. Then use Change Data Source and type the table name, such as Table1. From then on, new rows are included when you refresh.
3It should refresh by itself when the file opens
Fix
Right-click the PivotTable, choose PivotTable Options, open the Data tab and tick Refresh data when opening the file.
4Deleted items still show in the filter list
A PivotTable remembers old values, such as a product you've renamed, and keeps them in its filter lists.
Fix
In PivotTable Options > Data, set Number of items to retain per field to None, click OK and refresh.
5Amounts show as Count instead of Sum
If any amount in the column is text or blank, Excel puts the field in as Count of instead of Sum of.
Fix
Convert text numbers with Convert to Number, then right-click a value and choose Summarize Values By > Sum.
Still stuck?
- PivotTables built from the same data usually share one copy of it, so refreshing one updates the others too.
- If the source is in another workbook, that file must be available when you refresh.
- Column widths jump on every refresh? Untick Autofit column widths on update in PivotTable Options > Layout & Format.