Pivot Table Issues

How to Fix Excel Table Rows Disappearing After PivotTable Refresh

Aamir Naveed AkramAamir Naveed Akram Oct 28, 2026 870 views

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.

How to Fix Excel Table Rows Disappearing After PivotTable Refresh
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'.
Before you start

Ensure your PivotTable data source range fully includes the newly added rows, and verify that no hidden filters are accidentally excluding the new data.

Solution 1Recommended

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.

1
Open PivotTable Options

Right-click anywhere inside your existing PivotTable and select 'PivotTable Options' from the context menu.

2
Navigate to the Data Tab

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

3
Adjust the Retention Setting

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'.

4
Save and Refresh

Click 'OK' to apply the changes, then right-click your PivotTable and select 'Refresh' to see if the missing rows reappear.

Change the PivotTable Data Retention Settings
Setting Applied Successfully: Changing this setting ensures that filtered or temporarily modified items remain visible and available in the PivotTable filter lists.
Seamless Data Analysis in WPS Office

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. 1. Open Your Data: Launch WPS Spreadsheet and open your workbook containing the data.
  2. 2. Create a Dynamic Table: Highlight your source data and press 'Ctrl+T' to convert it into a dynamic Table.
  3. 3. Insert a PivotTable: Go to the 'Insert' tab on the top ribbon and click 'PivotTable' to generate your data summary.
  4. 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.
Fully compatible with Microsoft Excel (.xlsx) file formats and PivotTable structures.Dynamic table ranges automatically include new source rows upon a PivotTable refresh.Highly stable data processing preventing rows from mysteriously disappearing.Free, lightweight, and features an intuitive, familiar user interface.
QA img-9

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.