While breaking the formula links or demonstrating the data between the workbook can’t break links in Excel, this tutorial is for you.
Microsoft Excel allows break link functions to validate the data easily by breaking normal formula links. But recently, some reports have been seen where the Excel break links not working.
This is quite problematic for regular Excel users; therefore, we have done our research and found many different reasons ranging from minor glitches to major technical issues.
So, here in this article, we put together the potential solutions that work for you to fix break links in Excel not working problem. But before jumping straight to the solutions, look at the reasons triggering the problem.
To recover corrupted Excel file data, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
What Causes “Excel Break Links Not Working”?
Breaking links from Data Ribbon via Edit links won’t work for the external links which have conditional formatting, Data validation, defined names, and Chart sources. In this case, you need to search manually for external links.
Another reason has the protected Excel workbook won’t break links rather than the specified links. The protected workbook prevents other users from editing or modifying some parts of the Excel sheet, and this is why you can’t break links in Excel.
Luckily there are multiple ways that help you to fix the problem; below, follow them depending cause you are dealing with.
How to Fix “Excel Break Links Not Working”?
Before starting with the fixes listed, confirm if the break links button is grey or dimmed out. To do so, go to the Data tab and from the Queries & Connections group, click on the Edit Links option.
If you find the break links button is dimmed, try the solutions listed.
Solution 1 – Check if your Excel Sheet is Protected
If the Excel sheet you are using is protected, it won’t allow you to use the break links option. Protecting the Excel sheet means it won’t allow you to carry out pointless actions without authorization.
Follow the steps to check and unprotect your Excel sheet:
- Open your Excel file and click on the Review option
- Now in the Protect group, click on the option Unprotect Sheet.
- You will ask for the password, type your password and click on OK
- Click on the Data Tab and then on the Queries & Connections group option, and click on the Edit links.
Now click the Break Link button to confirm if the Excel break links not working problem is resolved.
Solution 2 – Delete the Named Ranges
If the External Excel file has the defined names, then this might be causing the problem and cause the Break Links not working problem in Excel. So, deleting the entire defined workbook names may work for you.
Follow the steps to do so:
- Open your problematic Excel sheet and click on the Formulas Tab
- And click on the Defined Names group, then choose Name Manager.
- Here you will see the Name Manager box. Select it and click on Delete.
- Click on OK to save the changes
Check if the problem is resolved or not.
Solution 3 – Remove External Links in the Charts
If on the external files you have created charts, the phantom link is created, which sometimes causes a problem when using the Excel Break links button.
So here are the steps to delete charts in External links
- Right-click on the charts and then click on the Select Data
- Here you will see this chart is linked to the Source Workbook
- And go to the Source workbook and copy the complete dataset.
- Then paste it to the xlsx file in the latest worksheet.
- Choose and right-click on the chart.
- Next, modify the reference to the new worksheet data in the Chart data range box.
Your chart is linked to the latest workbook, and fix the Excel break links button is not working problem.
Solution 4 – Remove the Conditional Formatting of External links in Excel
The external links in the Conditional Formatting in Excel might be causing problems. As hidden conditional formatting rules might be causing problems.
So, check for the hidden conditional formatting and remove it in Excel.
- Open the Excel sheet and click on Home.
- And from the Styles group, choose Conditional Formatting and click on Manage Rules.
- You will see the external links and then click on the Delete Rule the deleting the links.
You need to remove the external links, which will fix the break links that are not working.
Solution 5 – Delete Data Validation Links
In some cases, in the Data Validation field, the external files have formulas associated with the source file. And this will cause problems when you try to break links between the Excel workbooks. Therefore, here it is suggested to delete the links from the source.
- Click on the Data tab in the Excel file and then from the Data Tools group.
- Choose the Data Validation
- And under the Source, remove the formula-like content.
- Alternatively, under the Data validation, click on the Validation Criteria and click on Any Values.
Now check if this works for you to solve the problem.
Solution 6 – Check the Workbook
If your Excel workbook faces compatibility issues, it starts causing various problems. In this case, the compatibility checker will show problems in the workbook. Additionally, it can be prompted for locating and highlighting the problematic areas in the Excel workbook.
Follow the steps to run the compatibility checker in MS Excel:
- Launch the Excel file to open the linked document.
- Then from the menu bar, click on the File option
- Choose Info from the left side
- Click on Check for Issues option next to the Inspect Workbook.
- And from the menu, click on the Check Compatibility option
- Then in the Check Compatibility window, Excel starts displaying entire workbook problems.
- Now to find every problem click on Find and remove the problems from the workbook.
Solution 7 – Convert Excel to a Zip File
If none of the above-listed fixes works for you, make a zip of your Excel file. This helps you to delete the external links and solve the Excel break links not working issue.
Follow the instructions to do so:
- Open the folder where the external file is saved and right-click on the file
- Then choose the Rename option
- And change the extension to .zip
- Launch the zip file and open the Zip folder as your Excel file becomes a zip file.
- And here, delete the externalLinks folder
- Now modify the file extension from .zip to .xlsx
And it will be converted from a zip file to an Excel file, and you can now break links in Excel.
Solution 8 – Change the Excel File Type
Lastly, if none of the above-listed fixes works for you, the problem might be with the Excel file type. In this case, changing the file type might work for you to fix break links not working problem in Excel.
Follow the steps to do so:
- Click on the File option
- And click on the Save As option.
- Then alter the file type, for, eg. .xlsx to .xls.
- And click on Save.
- Now click on the File option again and choose Save As an option
- Then change the file type .xls to .xlsx and click on Save
Launch the Excel file and check if the break links not working problem is resolved.
Recommended Way to Repair & Recover Corrupted Excel File Data
While performing the task or fixing Excel errors, if, unfortunately, your Excel file data gets deleted or corrupted, then you can use Excel Repair Tool.
This professional tool can easily repair and recover corrupted, damaged, or inaccessible Excel files. This unique tool is used to repair multiple excel files in one repair cycle and recover the entire data in the preferred location.
It can fix Excel file corruption issues and various errors frequently encountered by users. It allows us to easily restore data, including charts, worksheet properties, cell comments, and other data. You can try this tool’s free demo version.
Steps to Utilize Excel File Recovery Tool:
Conclusion
So, these fixes work for you to fix the Excel Break Links not working problem. Here I tried my best to list the potential fixes to solve the problem and start using the Break links option in Excel.
I hope our guide works for you to solve the problem. Follow the steps listed carefully to avoid further issues.
If you are facing any other issue related to this specific one, let us know by sharing your experience on our social media FB and Twitter Pages.
Priyanka Sahu
Priyanka is an entrepreneur & content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.