How to Fix If Excel Break Links Not Working

Microsoft Excel is a powerful tool for managing data and making calculations. It’s ability to link data between various sheets and workbooks which is a handy feature to access with ease. Also, it helps to visualize the changes to the data of the source file. But sometimes you may just need the data to demonstrate and that’s the reason why you need to break links between them.

Sadly, most users encounter an issue with the Break Links where the function fails to work as expected. If you find yourself in such a situation, don’t brood, we’re here to help you. This is quite a frustrating problem for regular Excel users, especially those who are trying to access break links on their sheets and charts.

In this article, we will let you know how you can easily troubleshoot break link problems. With the right approach and accurate solutions, you will surely fix the problem. But before jumping to straight solutions, let’s have a look at the primary reasons why break links aren’t working.

Why Does Excel Break Links Not Working in Microsoft Excel?

There could be a number of reasons why the Excel break links are not working in Microsoft Excel. External links with conditional formatting, defined names, data validation, and chart sources cannot be broken from the Data Ribbon using the Edit links. In such a case, you have to manually search for external links if you want to add. In addition to this, it could be possible that your Excel workbook is protected and that’s the reason why you are unable to access break links other than the specified links.

Generally, the protected workbook prevents the Excel users from modifying the parts of the Excel sheet. Also, it doesn’t allow the user to access other features on the sheet, and this is why you can’t break links in Excel. Luckily, there are some effective ways that surely help you to fix the problem you are facing on the Microsoft Excel.

Solution to Fix Excel Break Links Not Working Issue

Try these below troubleshooting solutions if you are having an issue with the break links on the Microsoft Excel. Make sure to follow each and every solution carefully on your device.

Solution 1: Check if Your Excel Sheet is Protected

If the break link is not working on your Excel sheet you are trying to, then it could be possible that your sheet is protected with your password, or you have enabled the protection. Protecting the Excel sheet means it won’t allow the user to access any features without authorization. Make sure to check whether your Excel sheet is protected or not. In case it’s protected, then you have to unprotect it to access the break links option. Here’s what to do.

Open the Excel on your device and click on the Review option at the top bar.
Click on the Unprotect Sheet at the top right bar of the screen.
You will ask for the password you have set.
Simply type your password and click OK.
After that, click on the Data tab at the top bar.
Now click on the Edit Links under the Queries & Connection group option.
Finally, click on the Break Links to check if it’s working on your sheet.

Solution 2: Delete All Named Ranges

The next thing you have to do is to delete your named ranges on your Excel sheet. However, if the external Excel file has a properly defined name, this might be causing the issue with the Break Links on your Excel sheet. Therefore, make sure to delete all the named ranges on your Excel sheet. Follow the below steps to do.

Launch your problematic Excel sheet and click on the Formulas tab at the top.
Click on the Name Manager option at the top header.
On your screen, the Name Manager box will pop-up. Select the name and click on the Delete option.
Finally, click on OK to save the changes.

Solution 3: Break Data Validation Links

Oftentimes, in the Data Validation field there are external files which have formulas associated with the source file and there may be a chance that it will cause the problem when you try to break links between the Excel workbooks on your Excel sheet. In such a case, you have to remove the source link from the data validation.

To remove the source link from the data validation, follow the steps below:

Open your Excel sheet and click on the Data tab from the data tools section.
Select Data Validation. (You can also directly launch the Data Validation by searching it).
Under the Data Validation settings, you will be able to see the source link.
Remove the source link under the Source section.
Alternatively, under the Data Validation, click the Validation Criteria and select Any value.
Finally click on the OK to save the changes.

Once you have removed the source link and set the validation criteria to Any value, then try checking if the issue has been resolved or the Break Links are working. If the issue persists, jump over to the other methods.

Solution 4: Remove External Links in the Charts

If you have created external links in the charts on your Excel sheet or your workbook sheet, then it might cause problems with the break links while using it. So, it is suggested that you remove the external links in the charts, and it will surely help you to solve the problem. Here’s what to do.

On your Excel, right click on the Charts and click on the Select Data.
Now you will see the chart that it linked to the Source Workbook.
Head over to the Source workbook and copy the complete dataset.
After that, paste the copied data set to the xlsx file in the latest worksheet.
Select and right-click on the chart.
Again, modify the reference to the new worksheet data in the Chart data range box.
Now your chart is linked to the latest workbook and check if the break links are working on your sheet.

Solution 5: Remove External Links of Conditional Formatting

It is possible that the external links in the conditional formatting might be the reason for causing an issue with the break links in Excel. Make sure to check for hidden conditional formatting and remove it. Here’s what to do.

Open the Excel sheet and head over to the Home section.
Click on the Conditional Formatting and select Manage Rules.
Here you will see the external links. Select the link and click on the Delete Rule.
Finally click on the OK to save the changes.

Solution 6: Try Converting Your Excel to a ZIP File

If you are having an issue with the break links in Excel sheet, then it is suggested you try converting your Excel to a ZIP file. This may help you to delete the external links and solve the problem you are facing in your Excel sheet. To convert your Excel to a ZIP file, follow the steps below:

First open the folder where you saved your external file.
Right-click on the file and select Rename.
Change the file extension from .xlsx to .zip and your Excel will become a ZIP file after that.
Next, open the zip file and again launch the xl folder.
Now select the externalLinks folder and delete it.
Once done, change the file extension type from .zip to .xlsx.

Here it will convert it from a zip to an Excel file. By following this way, you may easily break all the links without hassle. Hopefully, the issue has been resolved in your Excel sheet and you are able to access break links.

Solution 7: Change the Excel File Type

If you have tried the above workarounds and it doesn’t work for you to solve the break links in Excel sheet, then you should try changing the Excel file type which generally fixes the problem you are facing. Here’s how you can change the Excel file type by following the steps below.

On your Excel sheet, click on the File at the top left corner of the screen.
Choose Save As option and change the file type .xlsx to .xls in the end and click on Save.
After that, click on the File option again at the top left corner and choose Save As option.
Change the file type .xls to .xlsx and click on Save.
Now launch your Excel file and check if the Break Links is working.

Final Words

Break Links not working in Excel sheets are really frustrating when you want to access them. But if you are really stuck trying to access break links in Excel sheets, then you can try the effective solutions that we have mentioned in this article. Make sure to pay attention to every single solution and one of them will definitely help you to resolve the problem. We hope you have found this article helpful for you. If you have any other questions or queries, write them in the comments section. Thanks for reading. Cheers!