How to Fix Excel Table Rows Disappearing After PivotTable Refresh
Question details
Users experience data loss where newly added rows in a source table disappear or become unavailable after refreshing a linked PivotTable and applying filters.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Updating a source data table and refreshing the associated PivotTable to reflect the newly added data.
- Observed behavior
- Added rows in the source table disappear or become hidden upon refreshing the PivotTable, particularly when the 'Retain deleted items' option is set to 'None'.
Ensure your PivotTable data source range fully includes the newly added rows, and verify that no hidden filters are accidentally excluding the new data.
Change the PivotTable Data Retention Settings
Modify the PivotTable options to prevent data items from disappearing when refreshing the table.
In some versions of Excel, setting the PivotTable to retain 'None' for deleted items can cause a glitch where newly added rows temporarily vanish upon refresh. Switching this to 'Automatic' often resolves the conflict.
Right-click anywhere inside your existing PivotTable and select 'PivotTable Options' from the context menu.
In the PivotTable Options dialog box, click on the 'Data' tab at the top.
Locate the 'Retain items deleted from the data source' section. Click the dropdown menu next to 'Number of items to return per field' and change it from 'None' to 'Automatic' or 'Max'.
Click 'OK' to apply the changes, then right-click your PivotTable and select 'Refresh' to see if the missing rows reappear.

Verify and Update the PivotTable Data Source Range
Ensure the PivotTable is reading the entire updated dataset, including the newly added rows at the bottom.
Easily Manage and Refresh PivotTables with WPS Spreadsheet
WPS Spreadsheet provides robust, easy-to-use PivotTable tools that automatically adapt to your data changes. By formatting your source data as a dynamic table, WPS seamlessly retains your data structure and rows upon every refresh, avoiding frustrating display glitches.
- 1. Open Your Data: Launch WPS Spreadsheet and open your workbook containing the data.
- 2. Create a Dynamic Table: Highlight your source data and press 'Ctrl+T' to convert it into a dynamic Table.
- 3. Insert a PivotTable: Go to the 'Insert' tab on the top ribbon and click 'PivotTable' to generate your data summary.
- 4. Refresh with Ease: Whenever you add new rows to your source Table, simply right-click your PivotTable and select 'Refresh' to instantly update your view without losing rows.

Frequently Asked Questions
Why is my PivotTable not updating when I add new rows to the source data?
A PivotTable does not update automatically in real-time. You must manually right-click the PivotTable and select 'Refresh', or go to the PivotTable Analyze tab and click 'Refresh All'. Additionally, if your source data is not formatted as an official Table, the PivotTable's reference range will not automatically expand to include the new rows.
How do I make my PivotTable data source dynamic?
To make your data source dynamic, highlight your source data and press 'Ctrl+T' to format it as a Table before creating the PivotTable. Any new rows added to this Table will automatically be detected and included in the PivotTable upon the next refresh.
What does the 'Retain items deleted from the data source' setting do?
This setting dictates whether data items that have been removed or changed in your source data continue to appear in your PivotTable dropdown filter menus. Changing it to 'None' prevents obsolete items from cluttering your filters, but in certain software versions, it may trigger a bug that temporarily hides valid data.




