Pivot Table Issues

How to Refresh an Excel PivotTable After Changing Its Data Source

Maira Mehtab Sep 27, 2026 869 views

Question details

Users need to know how to successfully refresh a PivotTable after moving the source data to a new worksheet, and how to fix errors caused by incorrect external file paths.

Product: Excel 365
Device & OS: not provided
Scenario: Updating an existing PivotTable after relocating its source data to a different worksheet or adjusting the data range.
Observed behavior: The PivotTable fails to refresh with the new data, often pointing to old ranges or generating errors due to incorrect external file paths stored in the workbook.

Before you start

Ensure you know the exact location of your new data source range and verify that all columns in the new data have non-empty header labels before updating.

Solution 1Recommended

Update the Data Source and Remove Broken Links

Manually point the PivotTable to the new worksheet range and clean up any problematic external links that prevent data refreshing.

When a data source is moved or modified, the PivotTable retains its original range reference. You must explicitly update this reference. Additionally, broken external links can lock the file and prevent PivotTables from properly pulling in new local data.

Select the PivotTable

Click on any cell within your existing PivotTable. This action will reveal the PivotTable Analyze and Design tabs on the top ribbon.

Change Data Source

Navigate to the PivotTable Analyze tab (or Options tab in older versions) and click the 'Change Data Source' button. In the dialog box, select the correct data range on your newly created or moved worksheet.

Edit Links to Remove External References

Go to the Data tab on the main ribbon and click on 'Edit Links' (if the button is active). Review the list for any unwanted or incorrect external file paths. Update them to the correct file, or click 'Break Link' to remove them.

Refresh the PivotTable

Once the source is updated and bad links are removed, right-click anywhere inside the PivotTable and select 'Refresh' from the context menu to load the newly referenced data.

Testing for File Corruption: If the PivotTable still will not refresh, test the issue by creating a specific, blank workbook. Copy your source data into it and build a new PivotTable to ensure the paths and files are consistent.

Efficient Data Analysis with WPS Office

Easily Manage and Refresh PivotTables with WPS Spreadsheet

WPS Office offers a robust and user-friendly Spreadsheet application that allows you to smoothly create, modify, and refresh PivotTables, ensuring your data analysis is always accurate and up to date.

1. Open Your File in WPS Spreadsheet: Launch WPS Office and open the Excel file that contains your data and PivotTable.
2. Access PivotTable Options: Click on your PivotTable to bring up the contextual 'PivotTable' tab on the top ribbon.
3. Change Data Source: Click on the 'Change Data Source' button and highlight the new data range in your updated worksheet.
4. Refresh the Data: Right-click the PivotTable and select 'Refresh' to instantly update the table with the modified source information.

High compatibility with Microsoft Excel (.xlsx) file formats and complex data sets.Intuitive tools for managing PivotTable configurations and changing data sources.Lightweight architecture that processes large data models quickly.Completely free, fast, and feature-rich Office alternative.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the PivotTable Analyze tab missing from my ribbon?

The PivotTable Analyze tab is contextual, meaning it only appears when you have actively selected a PivotTable. Click on any cell inside your PivotTable to make the tab visible on the ribbon.

How do I find exactly which cell or range my PivotTable is using?

Select a cell in your PivotTable, navigate to the PivotTable Analyze tab, and click 'Change Data Source'. The dialog box that opens will display the exact worksheet and cell range currently supplying data to the table.

Can I automatically refresh my PivotTable when opening the file?

Yes. Right-click the PivotTable and select 'PivotTable Options'. Go to the 'Data' tab within the options window and check the box for 'Refresh data when opening the file', then click OK.

Excel & Data Problems