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

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, working with multiple workbooks is a common task. You may have connections or links between these workbooks. It helps to visualize the changes if there are some changes in the source file. But, sometimes you may need just the data to demonstrate it. And if the data source is not available for some reason, then it adds more complications. That’s why you need to break links between them. If you are curious to know how you can break links in Excel when the source is not available, then this article may come in handy for you. In this article, we discuss how you can break links in Excel when the source is not found with an elaborate explanation.


What Causes Excel Not Finding Source?

When you connect your data to another workbook’s data, you can call it an external link. If you make any changes in the source file, you will see a change in the other workbook.

For work purposes, we sometimes need to share files with other people. While sharing the file, we prefer the file to be static. This means that we want our file does not to have any connection with the source file. To do this, we need to break the links between the files.

Another level of complication arrives when the source file itself is no more. In the below image, the Source file is used to have a connection with the Destination file. But if you click the Check Status command, you will see that the status of that connection is showing that it’s not there. Meaning the file is already moved to some other place. So we’d better remove/ break the Link to avoid an untoward outcome.

  • However, the drawback is that you must keep the linked workbook open at all times. The data would not update if you changed the name, location, or deletion of the associated workbook file.
  • If you’re working with a workbook that contains external links and you need to share it with others, either delete the external links or you could say that the links between these workbooks are unavailable.

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

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

How to Break Links in Excel When Source Not Found

  • And another part of the dataset contains Revenue and Profit.

How to Break Links in Excel When Source Not Found

We’d like to link two of these files and see how their links work if the source file is unavailable.


1. Delete All Named Ranges to Break Links

If there are any named ranges available in your source dataset, you had better delete them before you break links.

Steps

  • If your data has a named range and you have created a link with that named range, then you might find difficulty breaking the link if the source data is somehow damaged or unavailable.
  • Before breaking the link, you need to first delete the named ranges inside of the worksheet.
  • To delete the named ranges, first, go to the Formula, then click on the Defined Names.
  • From the dropdown menu, then click on the Name Manager.

Delete All Named Ranges to Break Links to Break Links in Excel When Source Not Found

  • Inside the Named Manager dialog box, you can see that there is a Named Range. The title of that named range is Source.
  • Click on the Delete icon on top of the dialog box.
  • Click OK after this.

  • Then you can break lines from the Data tab.
  • Go to Data tab > Queries and Connections.
  • Then click on the Edit Links.

  • In the Edit Links box, notice that there is a link between the Excel files named  Source.xlsx.
  • While selecting the link, click on the Break Link.

links between two file are broken

  • And this is how we can break links in Excel while the source is not found.

Read More: How to Break Links in Excel and Keep Values


2. Remove External Links from Charts

You may have some charts that you have created on external files. Those links must be broken before sharing them with someone else.

Steps

  • Below is the dataset that has a connection with an external dataset.
  • We need to break the link while the dataset is unavailable.

Remove Charts External Links to Break Links in Excel When Source Not Found

  • We first created the chart where we have the data linked to the Revenue column in the source workbook.
  • To see the linked workbook linked reference, right-click on the chart and then Select Data.

  • We can notice in the Select Data window that although the workbook name is the destination, the data source is linked with a workbook named Source.

  • Next, we will move the source file to another folder directory.
  • If you go to the Edit Links in the Queries and Connections, then you can see that there is a connection showing between the source and destination workbook.
  • And if you Check Status of the link, you can see that the status turned into an Error: Source not found.
  • Click OK after this.

  • Now if we try to update the values by changing them in the source file then the destination file would not update as well.
  • So that means we need to delete the existing link between them in the Edit Links from the Data tab.

  • Click on the Edit Links, and in the Edit Links dialog box, click on Break Link.

named range deleted

  • And this is how we can break links in Excel while the source is not found.

3. Make a Zip of Excel File

Converting the excel file into a zip file allows us to tweak the inside part of the Excel file. It will enable us to directly delete the external links folder to break the links between them.

Steps

  • We can change the file type to ZIP by renaming the Excel file.
  • In the file explorer menu, right-click on the file and click on Rename from the context menu.
  • Then change the extension to zip from xlsx.

Make a Zip of Excel File to Break Links in Excel When Source Not Found

  • Then there will be a warning sign in which indicate that renaming this file can cause instability in the file.
  • Click Yes.

  • Now we can see that the file is now switched to a zip-type file.
  • Then right-click on that zip file and from the context menu, click on the 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 then delete the folder by clicking on the above Delete icon.

folder containing externan links deleted

  • Now you successfully remove all of those external links and break the links.

Read More: [Fixed!] Break Links Not Working in Excel


4. Change File Extension

The last resort considering all of those previous methods does not work, is to change the format of the Excel file. Switching back to the XLS extension can remove the existing connections between the file.

Steps

  • You can also break links by changing the file extension.
  • To do this, select the file and right-click on the mouse.
  • Then from the context menu, click on Rename.
  • Then change the file extension from xlsx to xls.

Change File Type to Break Links in Excel When Source Not Found

  • There will be a warning message box stating that changing the file extension could make the file unstable.
  • Ignore the warning box by clicking on Yes.

  • The file extension is now xls.
  • All of the existing links in the previous version of the file have now vanished.

file extension changed to xls

Read More: How to Break Links in Excel Before Opening File


Download Practice Workbook

Download this practice workbook below.


Conclusion

To sum it up, the issue of how we can break lines in Excel when the source is not found is answered here in 5 different ways.

For this problem, two separate workbooks are available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.


Related Articles

<< Go Back To Excel Break Links | Linking in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo