Excel cannot find link to break

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 some changes the data of the source file. But, sometimes you may need just the data to demonstrate. That’s why you need to break linksbetween them. You may find yourself in a situation where the break links option not working in Excel. In this tutorial, you will learn to fix this problem quite effectively.

Download Practice Workbook

Download these practice workbooks.

What Does It Mean to Break Links in Excel?

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.

You can find external links from the formula bar. Take a look at the following screenshot:

Here is our source file. We have some sales data here. Now, we want to add the total sales based on the products. So, we did it in another workbook.

Here, you can see from the formula bar, our Total Sales.xlsx has external links to the Source file.

But, if you make any changes to the Source file, you will see the changes of the output in the Total Sales workbook.

Nevertheless, the disadvantage is that you are always required to have that linked workbook open. If you delete the related workbook file, change its name, or modify its folder location, the data would not update.

If you’re operating with a workbook that possesses external links and you have to share it with other persons, it’s better to delete these external links. Or You can say you have to break the links between these workbooks.

7 Ways to Fix If Break Links Not Working in Excel

Before we start, we must know what it is like to Break Links not working in Excel. First, go to the Data tab. Then, from the Queries & Connections group, click on Edit Links. You will see this:

You can see, the Break links button is dimmed out. It was not supposed to be like that. So, there are some problems that we need to fix.

In the following sections, we will provide you with eight methods that might help you to fix this. We recommend you learn and try all these methods in your workbooks. Surely, it will improve your Excel knowledge.

1. Unprotect Your Sheet to Break Links Excel

The first thing you need to make sure of is if your sheet is protected or not. Sometimes we try to protect our sheets from unnecessary actions. So that no one can edit these without authorization.

📌 Steps

• First, go to the Review.
• Then, from the Protect group, click on Unprotect Sheet.

• After that, it will ask for the password. Then, type the password.

• Next, click on OK.
• Now, go to the Data Tab. Then, from the Queries & Connections group, click on Edit Links.

Here, you can see that your Break Link button is working in Excel. Click on that to break the link.

Read More: Find Broken Links in Excel (4 Quick Methods)

2. Delete All Named Ranges to Fix Break Links

Now, it is one of those common problems. Your external file may have some defined names. Sometimes, it creates a problem that may dim out your break link button. So, you have to delete all the defined names from the workbook.

📌 Steps

• First, go to the Formulas Tab.
• From the Defined Names group, select Name Manager.

• After that, you will see the Name Manager dialog box.

• After that, click on Delete.
• Next, click on OK.

Finally, it may work for you if your Break Link button is not working in Excel.

Read More:How to Remove Hyperlink from Excel (7 Methods)

3. Break Data Validation Links in Excel

Sometimes, the external files have some formula linked to the source file in the Data Validation field. This may create problems to break links between the workbooks. So, in that case, you have to remove those links from the source.

📌 Steps

• First, go to the Data tab.
• From the Data Tools group, select Data Validation.
• Now, if your Break Links not working, you may see this in the dialog box:

• Just, remove the source and link with the corresponding worksheet.
• Another way is to allow Any Values in Validation Criteria.

In the end, I hope this method may help you if your break links button is not working in Excel.

Read More: How to Remove Hyperlink for Entire Column in Excel (5 Ways)

Similar Readings

• VBA to Add Hyperlink to Cell Value in Excel (4 Criteria)
• [Fixed!] Hyperlinks in Excel Not Working After Saving (5 Solutions)
• How to Link a Website to an Excel Sheet (2 Methods)
• [Fix:] Hyperlink to Website Not Working in Excel
• Excel VBA: Open Hyperlink in Chrome (3 Examples)

4. Remove Charts External Links If break Links Not Working

Now, you may have some chats that you have created on external files. You have created a phantom link in this case. It may make an issue to break links not working problem.

📌 Steps

• First, right-click on the chart and click on Select Data.

• After that, you will see, this chart is linked to the Source workbook.

• Now, go to the Source workbook.
• Then copy the whole dataset.

• Now, paste it to the Total Sales.xlsx file in a new worksheet.

• Again, select the chart and right-click on it.

• Now, in the Chart data range box, change the reference to your new worksheet data.

Now, finally, your chart is linked to the new workbook. It may now fix the problem of your break links button not working in Excel.

Read More: How to Remove External Links in Excel

5. Delete External Links of Conditional Formatting in Excel

Another thing that may create this problem is External Links in Conditional Formatting. There might be some hidden conditional formatting rules.

📌 Steps

• Go to the Home tab.
• Then, from the Styles group, select Conditional Formatting > Manage Rules.
• Now, you can see any external links here:

• Now, click on Delete Rule to delete the links.

In this way, you can remove any external links that may fix your break links problem in Excel.

Read More:Find External Links in Excel (6 Quick Methods)

6. Make a Zip of the Excel File

Now, I think this method is the ultimate method that you should try on if your break links not working in Excel. You can delete any external links from this method.

📌 Steps

• First, go to the folder where you saved your external file. Here, our external file is Total Sales.xlsx.
• Now, right-click on the file. Then, select Rename.
• Next, change the file extension from .xlsx to .zip.

• Now, your Excel file will become a zip file.
• Next, open that zip file.

• After that, open the xl folder.

• Now, select the externalLinks folder and delete it.
• After that, change the file extension from .zip to .xlsx.

After that, it will convert it from a zip file to an Excel file. In this way, you can break all the links. So, if your break links button not working, try this method.

7. Change the File Type If Break Links Not Working

Now, we don’t use this method too often. But, it may fix the break links problem that you are facing.

📌 Steps

• First, click on the File
• Then, select the Save as
• Now, change the file type from .xlsx to .xls.

• After that, click on Save.
• Again, click on the File Then, select the Save as option.
• Now, change the file type from .xls to .xlsx. Then, click on save.

In the end, it may fix the problem of break links not working in Excel.

Basically, we are converting our Excel file to an older version. So, if your worksheet has any feature that is not compatible with the older version, it will remove all of them. So, make sure to create a backup of your file.

Read More:[Fix]: Excel Edit Links Change Source Not Working

💬 Things to Remember

✎ You should always create a backup of your external Excel file before making any changes.

✎ Remember, break links will remove all the formulas linked to the source file. You will see your data as only values.

✎ Collect the password from the author for protected sheets.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to fix the problem of Break Links not working in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Related Articles

• [Fixed!] This Workbook Contains Links to One or More External Sources That Could Be Unsafe
• How to Create a Drop Down List Hyperlink to Another Sheet in Excel
• How to Get Hyperlink from an Excel Cell with VBA (3 Methods)
• Why Do My Excel Links Keep Breaking? (3 Reasons with Solutions)
• [Fixed!] ‘This workbook contains links to other data sources’ Error in Excel
• How to Remove Hyperlink Permanently in Excel (4 Ways)