Pivot Table Issues

Fix Excel PivotTable Showing Old, Blank, or Missing Data

Maira Mehtab Sep 28, 2026 869 views

Question details

The user is encountering an issue where an Excel PivotTable displays outdated field items, blank columns, or fails to include newly added source data records.

Product: Excel
Device & OS: not provided
Scenario: Updating and refreshing a PivotTable after modifying, deleting, or adding new records to the original source dataset.
Observed behavior: The PivotTable retains old data in its cache, shows empty columns for missing data, and excludes newly appended records even after changes are made to the source data.

Before you start

Ensure your source dataset does not contain completely empty rows or columns, and verify that any newly added data is placed directly adjacent to the existing dataset.

Solution 1Recommended

Clean Source Data and Update the Data Source Range

Removing unnecessary blank rows or columns and ensuring the PivotTable captures the entire dataset is the most reliable way to fix data inconsistencies.

Often, PivotTables fail to display new data because the original data source range is static. If you added new rows below the original range, the PivotTable won't recognize them. Additionally, excessive gaps or blank rows within the data can cause the PivotTable to display blanks.

Clean the dataset

Review your source data and delete any entirely blank rows or columns that are interrupting the data structure.

Access PivotTable Analyze

Click anywhere inside your existing PivotTable to display the 'PivotTable Analyze' (or 'Options' in older versions) tab on the ribbon.

Change Data Source

Click the 'Change Data Source' button in the Data group. A dialog box will appear displaying the current range.

Select the new range

Highlight the entire dataset again, ensuring that all newly added rows and columns are included in the 'Table/Range' field, and click 'OK'.

Refresh the PivotTable

Click the 'Refresh' button on the ribbon, or right-click the PivotTable and select 'Refresh' to apply the updated source data.

Use Excel Tables: To avoid updating the range manually in the future, format your source data as a Table (Ctrl + T) before creating the PivotTable. New data added to the Table will automatically be included upon refresh.

Solution 2

Clear PivotTable Cache to Remove Deleted Items

If the PivotTable still displays dropdown filter items that have been deleted from the source data, adjusting the cache settings will force it to forget old records.

Manage Data Easily with WPS Office

Create and Refresh PivotTables Smoothly in WPS Spreadsheet

WPS Spreadsheet provides powerful and intuitive PivotTable features, allowing you to handle large datasets, dynamically adjust source ranges, and clear outdated cache with just a few clicks.

1. Open Data: Launch WPS Spreadsheet and open your existing dataset.
2. Insert PivotTable: Select your data range, go to the 'Insert' tab, and click 'PivotTable'.
3. Adjust Options: If you add new data later, select the PivotTable, click 'Options' on the ribbon, and choose 'Change Data Source'.
4. Clear Cache Easily: Right-click the PivotTable, choose 'Table Options', and under the Data tab, set item retention to 'None' to clear old data.
5. Refresh All: Go to the Data tab and click 'Refresh All' to instantly update your summaries with the latest information.

100% compatible with Microsoft Excel (.xlsx) formats and PivotTable structures.One-click refresh for all PivotTables in your workbook.Built-in data cleaning tools to easily remove blanks and duplicates.Free to use with a lightweight, user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my PivotTable update automatically when the source data changes?

PivotTables do not update automatically in order to save processing power, especially on large datasets. You must manually right-click the table and select 'Refresh', or use the 'Refresh All' button on the Data tab after making changes.

How can I ensure new rows are automatically included in my PivotTable?

Convert your source data range into an official Table by selecting it and pressing Ctrl+T. When you use a Table as the source for your PivotTable, any new rows added to the bottom will be automatically included when you click refresh, without needing to change the data source range.

What causes blank items to appear in PivotTable rows or filters?

Blank items typically appear if the selected source data range extends far beyond your actual data (capturing empty rows at the bottom of the sheet), or if you have entirely empty rows interspersed within your dataset. Adjusting the source range and cleaning the data will remove them.

Why do deleted names still show up in my PivotTable drop-down filters?

PivotTables cache data to improve performance. Even if a record is deleted from the source, the cache remembers it. You can fix this by going to PivotTable Options > Data tab, and setting 'Number of items to retain per field' to 'None', then refreshing the table.

Excel & Data Problems