Pivot Table Issues

Fix Excel PivotTable Refresh Deleting Rows from Source Table

Steven Parker Oct 22, 2026 868 views

Question details

User experiences rows being deleted from the local source table when refreshing multiple PivotTables connected to SharePoint data.

Fix Excel PivotTable Refresh Deleting Rows from Source Table

Product: Microsoft Excel
Device & OS: not provided
Scenario: Refreshing multiple PivotTables that are connected to an Excel table and a SharePoint data model.
Observed behavior: Manually added rows in the local source Excel table are removed because the refresh action updates the Data Model and reconnects to the original SharePoint source, overwriting local additions.

Before you start

Before troubleshooting, verify if your Excel workbook is actively connected to an external SharePoint list or data model, as this background connection is what overwrites local data additions upon refreshing.

Solution 1Recommended

Add New Data Directly to the SharePoint File

Prevent local data overwrites by making your additions directly in the source file on SharePoint before refreshing your PivotTables.

Because Excel synchronizes with the original SharePoint data model during a refresh, any rows added locally will be discarded. Updating the primary source ensures all PivotTables reflect the latest data accurately.

Navigate to the SharePoint Source

Append the Data

Open the source file and manually append your new rows or data entries, then save your changes to SharePoint.

Refresh Local Workbook

Return to your local Excel workbook, go to the 'Data' tab on the ribbon, and click 'Refresh All' to safely update your PivotTables with the new data.

Add New Data Directly to the SharePoint File

Data Preserved: Adding data to the source guarantees that no records are lost during subsequent refreshes by any user.

Solution 2

Remove the SharePoint Data Connection

Disconnect the workbook from SharePoint so that manually added rows are preserved during a local refresh.

Solution 3

Create an Independent Copy of the Table

Keep the SharePoint connection active for the original data, but build your PivotTables from an independent, copied table where you can safely add rows.

Free Microsoft Office alternative

Try WPS Office for Stable Spreadsheet Management

If complex SharePoint data connections in Microsoft Excel are causing unwanted data loss during PivotTable refreshes, consider using WPS Office. It provides a lightweight, highly compatible alternative for managing spreadsheets and PivotTables without overly complex background syncing issues.

1. Download WPS Office: Download and install the free WPS Office suite from the official website.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx workbook.
3. Manage PivotTables: Use the Insert > PivotTable feature to analyze your data safely and efficiently.

Fully compatible with Microsoft Excel (.xlsx) formats and PivotTables.Free and lightweight, avoiding complex background data sync overrides.Familiar user interface for seamless migration from Microsoft Office.Robust data analysis tools without unexpected source data deletion.

microsoft office alternative - wps office

Frequently Asked Questions

Why does refreshing my Excel PivotTable delete my source data?

When a workbook is connected to an external source like SharePoint, clicking 'Refresh All' updates the background Data Model. If you manually added rows locally, Excel overwrites your local table with the original data from SharePoint, deleting your manual additions.

Can I stop Excel from overwriting my local table additions?

Yes, you can stop this by breaking the connection to the external data source. Go to Data > Queries & Connections, and remove the link. This converts your connected table into a standard, unconnected local table.

How do I safely update a SharePoint-connected PivotTable?

To safely update the PivotTable without losing data, add any new rows directly to the original source file hosted on SharePoint. When you refresh the local Excel file, it will properly pull and display the newly added rows.

Does 'Refresh All' affect all PivotTables in the workbook?

Yes, clicking 'Refresh All' on the Data tab updates all external data ranges and PivotTables in the workbook, reconnecting and syncing them with their respective source data models.

Excel & Data Problems