Formula Errors

How to Fix Excel Formula Reference Errors When Changing Chart Workbook Links

Maira Mehtab Sep 22, 2026 869 views

Question details

The user encounters a formula-reference error after changing chart links from a source workbook to a destination workbook, even though the worksheet names and data match.

Product: Excel
Device & OS: not provided
Scenario: Updating chart source links from one workbook to another with matching structures.
Observed behavior: Excel displays a formula-reference error indicating invalid links or ranges.

Before you start

Before modifying workbook links or defined names, ensure both the source and destination workbooks are open to prevent automatic link updates from failing.

Solution 1Recommended

Resolve Erroneous Defined Names using Name Manager

Identify and delete or fix broken named ranges that are still pointing to the old workbook, which cause reference errors in your charts.

When you copy charts or worksheets between workbooks, Excel often carries over defined names. If these names still reference the old workbook and the link is severed or changed, it triggers a formula reference error. Cleaning up the Name Manager resolves this.

Open Name Manager

Navigate to the 'Formulas' tab on the Excel ribbon and click on 'Name Manager'.

Locate Invalid References

Look through the list of defined names for any entries that show '#REF!' or reference the old source workbook's file path in the 'Refers to' column.

Delete or Edit Names

Select the erroneous names and click 'Delete' to remove them, or click 'Edit' to update the reference to the correct data range in the new destination workbook.

Update Chart Links

Once the names are corrected, go back to your chart, right-click the chart area, and choose 'Select Data' to ensure the chart source and workbook links now correctly point to the new destination file.

Check Worksheet References: Also verify that any copied worksheets or underlying formulas do not contain hidden or hard-coded references to the old workbook.

Seamless Spreadsheet Management

Easily Manage Chart Links and Formulas in WPS Spreadsheet

WPS Spreadsheet provides an intuitive Name Manager and robust link tracking, allowing you to seamlessly update chart sources and fix formula reference errors without hassle.

1. Open your Workbook in WPS: Launch WPS Spreadsheet and open the destination file containing the broken chart links.
2. Access the Name Manager: Go to the 'Formulas' tab on the top ribbon and click on 'Name Manager' to view all defined names in the document.
3. Fix Invalid Links: Identify any names displaying errors, edit them to point to the correct local ranges, or delete the ones that are no longer needed.
4. Update Chart Source: Right-click your chart, choose 'Select Data', and verify the series references now point securely to the current workbook.

Built-in Name Manager to quickly identify and fix broken formula references.Fully compatible with Microsoft Excel formats (.xlsx, .xls) and complex chart links.Lightweight, fast, and features a familiar interface for smooth navigation.

Frequently Asked Questions

Why do I get a #REF! error when copying a chart to a new workbook?

This usually happens because the chart's data series or named ranges are still linked to the original workbook. If the destination workbook doesn't have the exact named ranges, or the source file is closed/moved, the spreadsheet application cannot resolve the reference.

How can I quickly find all external links in my Excel file?

You can go to the 'Data' tab and click on 'Edit Links' (or 'Queries & Connections') to view all external workbook references. From there, you can change the source to point directly to the current workbook.

Does deleting a defined name affect my existing formulas?

Yes, if an active formula or chart relies on a defined name that you delete, it will result in a #NAME? or #REF! error. Ensure you only delete names with already broken references, or choose to edit them to update the correct range instead.

Excel & Data Problems