How to Break Links in Excel When Source Is Not Found (4 Ways)

In this article, we discuss how to break links in Excel when the source is not found.

What Causes Source Not Found Errors in Excel?

When we connect our data to another workbook’s data via an external link, if changes are made in the source file, these changes also appear in the linked workbook.

Sometimes we might choose to break these links deliberately, for example if we prefer the file to remain static without the connections to the source file when sharing it.

But sometimes the links break when the source file is no longer available, because it has been moved, deleted or become corrupted. In the below image, the Source file used to have a connection with the Destination file. But clicking the Check Status command reveals that the source is not found. If the correct link to the source is in fact no longer available, we should remove/ break the Link.

How to Break Links in Excel When the Source Is Not Found: 4 Ways

We’ll use the dataset below to demonstrate how to break links when the source is unavailable. In the dataset, we have product information like Quantity and Cost.

Another part of the dataset contains Revenue and Profit.

Let’s break the links between these 2 files.

Method 1 – Deleting All Named Ranges to Break Links

If there are any named ranges available in your source dataset, it’s a good idea to delete them before breaking the links. However, you may face difficulty breaking the link if the source data is somehow damaged or unavailable.

Steps:

To delete the named ranges, go to the Formula tab, then click on Defined Names.
From the dropdown menu, click on the Name Manager.

Inside the Named Manager dialog box, there is a Named Range called Source.

Select it, and click on the Delete icon on top of the dialog box.
Click OK.

To break lines from the Data tab:

Go to the Data tab > Queries and Connections.
Click on Edit Links.

In the Edit Links box, there is a link with the Excel file named Source.xlsx.

Select it and click Break Link.

The broken link is removed.

Method 2 – Removing External Links from Charts

You may have some charts that link to external files in your workbook. These links must be broken before sharing the file.

Steps:

Below is a dataset that has a connection with an external dataset. We’ll break the link while the dataset is unavailable.

Suppose we have created the chart below from this dataset, where the data is linked to the Revenue column in the source workbook.

To see the linked workbook reference, right-click on the chart and Select Data.

In the Select Data window, the data source is linked with a workbook named Source.

Move the source file to another folder directory.
Go to Edit Links in Queries and Connections,

There is a connection showing between the source and destination workbook.

Select the link and click Check Status.

The status is now Error: Source not found.

Click OK.

Now, if we try to update the values by changing them in the source file, the destination file will not update. So we need to delete the existing link via Edit Links on the Data tab.

Click on Edit Links.
In the Edit Links dialog box, select the link and click on Break Link.

The link is broken.

Method 3 – Making a Zip of the Excel File

Converting the Excel file into a zip file will enable us to directly delete the external links folder to break the links.

Steps:

We can change the file type to ZIP by renaming the Excel file.

In the File Explorer, right-click on the file and click on Rename from the context menu.
Change the extension from xlsx to zip .

A warning will appear indicating that renaming this file can cause instability in the file.

Click Yes.

The file is now displaying as a zip-type file.

Right-click on the zip file and from the context menu, click on Open with WinRar.

In the Winrar application, there are a couple of folders.

Double-click on the xl folder.

In the xl folder, look for the folder externalLinks.
Select that folder and delete it by clicking on the Delete icon.

We have successfully removed all the external links and therefore broken them.

Method 4 – Changing the File Extension

The last resort if all of the previous methods don’t work, is to change the format of the Excel file. Switching to the .xls extension can remove existing connections between the files.

Steps: