Pivot Table Issues

How to Fix Excel Pivot Table Not Updating After Replacing Source Data

Bushra Parveen Oct 12, 2026 870 views

Question details

The user's Excel Pivot Table continues to display old values even after the source data rows have been completely replaced with new data.

How to Fix Excel Pivot Table Not Updating After Replacing Source Data

Product: Microsoft Excel
Device & OS: not provided
Scenario: Updating a monthly Pivot Table report by replacing the previous month's source data rows with a new dataset.
Observed behavior: The Pivot Table retains and displays old entries from the previous dataset, including items that no longer have corresponding values in the updated source data range.

Before you start

Ensure that your newly pasted source data has the exact same column headers as the previous dataset, and verify that there are no entirely blank rows interrupting your data range.

Solution 1Recommended

Clear the Pivot Table Cache

By default, Pivot Tables cache old data to speed up processing. You can change this setting to force the table to drop deleted items from the previous dataset.

When you replace data in your source range, the Pivot Table may still remember the old entries due to its internal cache. This is why you see leftover categories even after replacing the source rows. Changing the data retention settings will force Excel to discard items that no longer exist in the current source data.

Open PivotTable Options

Right-click any cell inside your Pivot Table and select 'PivotTable Options' from the context menu.

Navigate to the Data tab

In the PivotTable Options dialog box, click on the 'Data' tab located at the top.

Adjust item retention

Locate the 'Retain items deleted from the data source' section. Click the 'Number of items to retain per field' dropdown and change it to 'None'.

Refresh the Pivot Table

Click 'OK' to save your settings. Finally, right-click the Pivot Table again and select 'Refresh' to update the table with the new data.

Tip: After changing this setting to 'None', any future data replacements will automatically drop old items the moment you click Refresh.

Solution 2

Update the Data Source Range

If you pasted more data rows than the previous dataset contained, the Pivot Table might still be locked to the older, smaller range.

Efficient Data Analysis

Easily Manage and Refresh Pivot Tables in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive interface for analyzing large datasets with Pivot Tables. Easily replace data sources, manage caches, and refresh your data without formatting issues or leftover ghost items.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your existing Pivot Table and source data.
2. Update source data: Paste your new data into the source sheet, ensuring the column headers remain exactly the same as the original dataset.
3. Refresh the data: Click any cell inside the Pivot Table, navigate to the Analyze tab on the top ribbon, and click Refresh to update your report instantly.

100% compatible with Microsoft Excel (.xlsx) formats and Pivot TablesLightweight and fast, ensuring quick refreshes even with massive datasetsIntuitive drag-and-drop Pivot Table builderFree to use with comprehensive data analysis tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Pivot Table show old data that I already deleted from the source?

Excel retains old items in the Pivot Cache by default to optimize performance and prevent errors in existing filters. You must set the 'Number of items to retain per field' to 'None' in the PivotTable Options menu to permanently clear these ghost items.

How do I make my Pivot Table update automatically when source data changes?

Pivot Tables do not update in real-time as you type. To update them automatically, you can check the 'Refresh data when opening the file' option in the Data tab of the PivotTable Options, or right-click and press Refresh manually after making changes.

Should I use Excel Tables for my Pivot Table source data?

Yes, it is highly recommended. Converting your source data into an official Excel Table (by pressing Ctrl+T) before creating a Pivot Table ensures that any new rows or columns added later are automatically included in the source range, preventing missing data when you refresh.

Excel & Data Problems