How to Fix an Excel PivotTable Showing Old Data After Refresh

Clicking the refresh button on your spreadsheet and seeing the exact same numbers is a frustrating experience, especially when you know the underlying data has changed. When a PivotTable refuses to display recent updates, the software is not usually broken. Instead, the PivotTable is either reading from a strict, fixed cell range that excludes your newly added rows, or it is clinging to cached memory of deleted items. To get your report accurate again, you need to diagnose exactly where the connection between your raw data and the PivotTable cache is failing.

Update the Data Source Range to Capture New Rows

The most common reason new data fails to appear is that your PivotTable is looking at a static range of cells. If your original data spanned from cell A1 to D100, and you pasted new sales records down to row 150, clicking refresh will do nothing because the PivotTable is still only reading up to row 100.

To verify and fix the exact range your PivotTable is reading:

• Click on any cell inside your PivotTable to reveal the hidden ribbon menus.
• Navigate to the PivotTable Analyze tab (called Options in older versions) on the top ribbon.
• Click the Change Data Source button. A dialog box will appear, and Excel will highlight the current data range on your raw data sheet.
• Inspect the flashing dashed line. If it stops above your new data, manually highlight the entire data set, including the new rows and headers, and click OK.

To prevent this from happening every time you add data, you must convert your raw data into a dynamic Excel Table. Go to your raw data sheet, click any cell within the data, and press Ctrl + T. Ensure the box for "My table has headers" is checked and click OK. Your data is now formatted as a Table (usually named Table1 by default). Return to your PivotTable, click Change Data Source, and type the name of your table (e.g., Table1) into the field. From now on, any rows pasted at the bottom of the table will automatically be included the next time you refresh.

Clear Ghost Items from Filter Dropdowns

Sometimes your PivotTable reflects new calculations, but deleted items stubbornly remain in your row labels, column labels, or slicer dropdown menus. This happens because Excel retains a memory of deleted items in the Pivot Cache to prevent custom formatting from breaking when data temporarily disappears.

To flush deleted data from the PivotTable cache:

• Right-click on any cell inside the PivotTable.
• Select PivotTable Options from the context menu.
• Navigate to the Data tab within the options window.
• Locate the section labeled Retain items deleted from the data source.
• Click the dropdown menu next to Number of items to retain per field and change it from Automatic to None.
• Click OK to apply the setting.

After changing this setting, the ghost data will not vanish instantly. You must right-click the PivotTable and select Refresh one more time to clear the memory. Open your filter dropdowns to verify that the deleted categories are completely gone.

Force a Complete Refresh of the Pivot Cache

If your workbook contains multiple PivotTables built from the same raw data, they share a single background Pivot Cache to keep file sizes manageable. Occasionally, refreshing a single table fails to update the shared cache, or background queries get stuck. Forcing a global refresh ensures all caches synchronize with the raw data.

To refresh the entire workbook:

• Navigate to the Data tab on the main ribbon.
• Find the Refresh All button. Click the downward-facing arrow just below the icon.
• Select Refresh All from the dropdown menu (or press Ctrl + Alt + F5 on your keyboard).

Check the status bar at the very bottom of the window. If you see a message saying "Running background query," wait for it to disappear. Once the status bar clears, check your PivotTable totals to verify the new data has loaded.

Managing Spreadsheet Data Sources in WPS Office

If you are auditing documents on a different workstation or using WPS Office as your primary suite, the PivotTable architecture operates under the same cache and range principles. WPS Spreadsheet fully supports PivotTables and allows you to manage data sources without losing formatting or breaking calculations established in other spreadsheet programs.

To update the data source and refresh in WPS Office:

• Open your spreadsheet in WPS Office and select any cell within the existing PivotTable.
• The PivotTable tab will appear on the top ribbon. Click it to access your report tools.
• Click Change Data Source. A dialog will prompt you to select the new cell range. Highlight your updated raw data directly on the sheet, ensuring the column headers are included in the selection.
• Click OK to confirm the new range.
• Click the Refresh button located on the same PivotTable ribbon to pull the updated values into your layout.

WPS Office will seamlessly recalculate the totals based on the newly defined range. If you previously converted your source data to a dynamic table before opening the file in WPS Office, clicking Refresh will automatically capture the new rows without needing to manually adjust the range.

Frequently Asked Questions

Why does my PivotTable refresh automatically when opening the file?

This happens when the workbook is configured to update the cache upon launch. To change this behavior, right-click the PivotTable and select PivotTable Options. Go to the Data tab and uncheck the box labeled "Refresh data when opening the file." Click OK. You will now need to refresh manually.

How can I tell if my PivotTable is linked to an external data model?

Click inside the PivotTable and navigate to the PivotTable Analyze tab. Click the Change Data Source button. If the option to select a table or range is grayed out, or if you see a connection string pointing to an external server, your PivotTable is built on an external Data Model or Power Pivot query, which must be refreshed via the Data tab's Queries & Connections pane.

Does clearing the Pivot Cache delete my calculated fields?

No, changing the retain items setting to "None" and clearing the cache only flushes the stored memory of the raw data strings. Your custom calculated fields, calculated items, value field settings, and PivotTable layouts are preserved correctly.

Why is the Refresh button entirely grayed out?

The refresh command becomes disabled if the worksheet containing the PivotTable is protected. Go to the Review tab and click Unprotect Sheet (you may need a password if one was set). The refresh button will also gray out if you have multiple worksheets grouped together. Right-click the sheet tab at the bottom of your screen and select Ungroup Sheets to restore functionality.