Formula Errors

How to Fix Excel Table Formulas Referencing an Old Workbook

Olivia Miller Oct 9, 2026 871 views

Question details

The user wants to remove external references from an Excel table copied into a new workbook, as newly added rows automatically pull formulas linked to the old file and return #REF! errors.

How to Fix Excel Table Formulas Referencing an Old Workbook

Product: Microsoft Excel
Device & OS: not provided
Scenario: Copying a worksheet and its data table into a new workbook, then adding new rows to the copied table.
Observed behavior: While existing formulas updated correctly, new table rows generate formulas with external references to the original workbook, resulting in #REF! errors.

Before you start

Before modifying formulas or breaking data links, verify your source workbook's location and save a backup copy of your current file to prevent accidental data loss.

Solution 1Recommended

Break External Links and Update Named Ranges

The most effective way to stop new table rows from pulling old references is to sever the external workbook links and clean up the Name Manager.

Excel tables rely on calculated columns that automatically apply formulas to new rows. If the underlying named ranges or background links still point to the old file, breaking these links forces Excel to look at the current workbook instead.

Open Edit Links

Navigate to the 'Data' tab on the ribbon and click on 'Edit Links' in the Queries & Connections group.

Break Old Links

Select the source file that points to your old workbook from the list, then click 'Break Link' and confirm your choice.

Clean Up Name Manager

Go to the 'Formulas' tab and click 'Name Manager'. Locate any named ranges that contain external paths to the old workbook, select them, and click 'Delete'.

Refresh Calculated Column

Select the first cell in your table's formula column, ensure the formula references the current workbook, and press Enter to overwrite the old calculated column rules for all subsequent rows.

Break External Links and Update Named Ranges

Check Formula Autocorrect: Make sure 'Fill formulas in tables to create calculated columns' is enabled in your Excel AutoCorrect Options so the updated formula automatically drops down to new rows.

Solution 2

Recreate the Table from Raw Values

If breaking external links fails to reset the table's background memory, recreating the table from scratch will permanently clear any stubborn cached references.

Manage Formulas and Data Links Easily with WPS Office

WPS Spreadsheet provides an intuitive interface for managing external references, named ranges, and table formulas, helping you avoid frustrating #REF! errors when moving data between files.

1. Open Your File in WPS: Launch WPS Spreadsheet and open the workbook containing the problematic table.
2. Access External Links: Navigate to the 'Data' tab and click on 'Edit Links' to review any lingering external connections.
3. Break the Connection: Select the obsolete old workbook references and click 'Break Link' to convert them safely into values.
4. Verify Formula Ranges: Open the 'Formulas' tab, select 'Name Manager', and ensure all ranges point strictly to the current document.

Easily identify and break external workbook links with one clickFully compatible with Microsoft Excel (.xlsx) formats and table formulasUser-friendly Name Manager to swiftly track down and delete broken referencesLightweight, fast, and completely free to use for everyday spreadsheet tasks

Frequently Asked Questions

Why does Excel keep referencing the old workbook after copying a table?

When an Excel table with calculated columns is copied to a new workbook, the application may retain the original workbook path in its memory. This typically happens if specific column references or named ranges within the formula were explicitly tied to the source file structure.

How do I find hidden external links in my workbook?

You can locate hidden external links by opening the Name Manager under the Formulas tab, using the Find tool (Ctrl + F) to search for '.xl' or '[' across all formulas, or by checking your Data Validation rules and Conditional Formatting criteria.

What does the #REF! error mean in Excel tables?

The #REF! error indicates that an Excel formula is trying to calculate a value using a cell reference that is no longer valid. This usually occurs when rows or columns have been deleted, or when the formula points to a closed, moved, or deleted external workbook.

Excel & Data Problems