[Fixed!] Break Links Not Working in Excel (7 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 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 links between 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.

Fix If Break Links Not Working in Excel

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:

Fix If Break Links Not Working in Excel

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.

Fix If Break Links Not Working in Excel

  • 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.

Fix If Break Links Not Working in Excel

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


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.

Delete All Named Ranges to Fix Break Links

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

Delete All Named Ranges to Fix Break Links

  • 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.


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:

Break Data Validation Links in Excel

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

Break Data Validation Links in Excel

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


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.

Remove Charts External Links If break Links Not Working

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

Remove Charts External Links If break Links Not Working

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

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

Remove Charts External Links If break Links Not Working

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

Remove Charts External Links If break Links Not Working

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

Remove Charts External Links If break Links Not Working

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.


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:

Delete External Links of Conditional Formatting in Excel

  • 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.


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.

excel break links not working

  • After that, open the xl folder.

excel break links not working

  • 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.

Change the File Type If Break Links Not Working

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.


💬 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!

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo