Formula Errors

How to Fix Excel Formulas Referencing a Deleted Table After Refresh

Phi Hung Vo Sep 30, 2026 868 views

Question details

The user needs to prevent formulas from automatically changing to reference an old, deleted table name after a workbook data refresh.

Fix Excel Formulas Referencing a Deleted Table After Data Refresh

Product: Microsoft Excel
Device & OS: not provided
Scenario: Refreshing data in a workbook after a table has been deleted or renamed.
Observed behavior: Formulas unexpectedly revert to referencing the old, deleted table name instead of updating properly, which often results in reference errors.

Before you start

Before modifying your workbook structure or removing hidden data connections, save a backup copy of your file to prevent accidental data loss.

Solution 1Recommended

Find and Remove Hidden PivotTables

Hidden PivotTables or charts may still be linked to the old table, causing the workbook to refresh outdated references.

When you delete a table, Excel does not automatically delete the PivotTables or charts that were built using that table's data. If these objects are stored on a hidden worksheet, they will pull the old table name back into your formula logic during a global data refresh.

Unhide worksheets

Right-click on any visible worksheet tab at the bottom of the Excel window and select 'Unhide'. Repeat this until all hidden sheets are visible.

Locate outdated PivotTables

Check the newly unhidden sheets for any PivotTables or charts that rely on the deleted table.

Delete the hidden objects

Highlight the outdated PivotTable or chart and press the Delete key to remove the object completely.

Save and refresh

Save your workbook, then go to the Data tab and click 'Refresh All' to verify the formulas no longer revert to the old table name.

Hidden Objects Cleared: Once the hidden PivotTable is deleted, the ghost reference is destroyed, and your formulas will remain stable.

Solution 2

Clear Outdated References in Name Manager

Old table names might still be cached in the Name Manager, confusing the formula references.

Solution 3

Delete Outdated Queries and Connections

External links or background data queries might be pulling the old table back into the workbook.

Free Microsoft Office alternative

Try WPS Office for a Smoother Spreadsheet Experience

If you frequently encounter frustrating caching bugs and hidden object reference errors in Microsoft Excel, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet tool that makes managing data, formulas, and pivot tables straightforward and completely free.

1. Download and Install: Visit the official WPS website to download the free installation package and follow the on-screen prompts to install.
2. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx file to continue working without formatting loss.
3. Manage data effortlessly: Use the Data and Formulas tabs in WPS to effortlessly manage your tables, PivotTables, and defined names.

Seamlessly compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Intuitive Name Manager and PivotTable interface to easily track data sources.Lightweight installation with fast startup and performance.Free to use with a familiar tabbed user interface for easy migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my Excel formulas change to an old table name after refreshing?

This happens when hidden workbook objects, such as hidden PivotTables, charts, or background queries, still hold a reference to the deleted table. During a refresh, Excel attempts to update these objects and incorrectly reverts your formulas to the old name.

How can I find hidden worksheets in my workbook?

Right-click any visible worksheet tab at the bottom of your screen and select 'Unhide'. A dialog box will appear listing all hidden sheets. Select them to make them visible so you can inspect their contents for outdated PivotTables.

Can the Name Manager cause formula reference issues?

Yes. If a deleted table's name is still cached as a defined name in the Name Manager, it can conflict with your current formulas. Go to the Formulas tab, open Name Manager, and delete any invalid or outdated references.

How do I remove old data connections?

Go to the Data tab and select 'Queries & Connections'. Check the right-side panel for any queries pointing to the old table, right-click them, and select 'Delete' to permanently remove the connection from your workbook.

Excel & Data Problems