ExcelInsider Team
While breaking links in Excel is usually straightforward, it can become challenging when the Break Links tool fails to work. This often happens when broken external links are hidden within formulas, named ranges, or other elements that Excel’s default Break Links tool cannot detect.
Although Excel does not have any built-in tools to deal with external broken links when the Break Links feature fails, we can get around this problem using a few simple tricks and techniques.
Follow the steps below to efficiently deal with external broken links in Excel when the Break Links tool does not work:
➤ Highlight the column containing the broken links in your dataset and press Ctrl + C to copy it.
➤ Next, in a different column, right-click to open the context menu, head to Paste Options >> Paste Special >> Paste Values and then paste the data.
➤ The external links will now be replaced with plain values, effectively breaking the links.
In this article, we will learn seven effective methods of fixing Break Links not working issue in Excel.
Use Paste Special to Convert Formulas to Values
In the sample dataset, we have a worksheet called Sales Data containing information about Product, Quantity, Unit Price and Total Cost. Column D of the dataset contains multiple external broken links that reference unavailable source files, causing errors in the calculations.
By using the Paste Special tool, we will eliminate the external references and effectively break the links from the dataset. The updated dataset will be stored in a separate worksheet called “Paste Special”.
Paste Special in Excel is a useful tool that allows users to paste specific elements of copied data, like values, formats, or formulas, without bringing over the entire cell content.
Steps:
➤ Open the Paste Special worksheet, select all the cells in column D, and press Ctrl + C to copy the contents.
➤ Next, select column E, right-click, and from the context menu head to Paste Options >> Paste Special >> Paste Values to paste the data as static values.
➤ Column E should now display the values from column D, with all external links removed.
Change File Format to Zip for Breaking External Links
Unlike previous methods, this method requires a high level of technical knowledge to execute. This technique involves renaming the Excel file with a .zip extension, editing its internal files to remove external links, and then restoring the original Excel format.
Working again with the same dataset, we will use this method to remove all external links and display the updated dataset in a separate “Change File Format” worksheet.
Steps:
➤ From the main menu, head to File >> Save As >> Browse and save the Excel file at your desired location in .xlsx format.
➤ Next, navigate to the folder where you saved the file and change the extension from .xlsx to .zip.
➤ Click Yes when prompted with the rename confirmation dialogue box.
Note:
Make sure the file is closed in Excel before proceeding, as files currently open in the application cannot be modified.
➤ Now, open the ZIP file, navigate to xl >> externalLinks folder, and delete it.
➤ Close the ZIP file and rename it back to .xlsx from the .zip format.
➤ When you open the dataset in Excel now, all external file links should be successfully broken.
Manually Reformat Cells to Break Links
This method is quite lengthy and time-consuming. In this method, we will reformat cells manually to break external links and remove any references that are causing issues.
We will again work with the same dataset and manually edit each cell to break links. We will display the modified dataset in a separate worksheet called “Reformat Manually”.
Steps:
➤ Open the Reformat Manually worksheet double-click cell D6, and remove “=” sign from the formula.
Note:
Removing the “=” sign converts the formula into a static value, effectively breaking any external link.
➤ Repeat the same process for cells D8 and D13.
➤ Your dataset should now be displaying values without any external link references.
Unprotect Sheet to Break Links
Excel has a built-in safety feature called Protect Sheet, which allows users to lock the worksheet and restrict any unauthorized changes. However, if a sheet is protected, the Break Links tool will not function and cannot perform its job.
Using the same dataset, we will break the external links by first unprotecting the sheet and then display the updated dataset in a separate worksheet named “Unprotect Sheet”.
Steps:
➤ Open the Unprotect Sheet worksheet, right-click on the sheet tab to open the context menu and select Unprotect Sheet option.
Note:
You may need to enter a password if one was set to unprotect the sheet.
➤ Next, from the main menu, head to Data >> Workbook Links.
➤ Click on Break all button from the Workbook Links pane to successfully break links.
Note:
Click on Break Links when the dialog box appears to confirm the removal of external links.
➤ All external links in the dataset should now be broken.
Use Name Manager to Remove External Links
Name Manager in Excel is a useful tool that allows users to create, edit or delete named ranges for easier formula management and data manipulation. However, if named ranges are linked to external workbooks, the Break Links tool may fail to remove those links effectively.
We will again work with the same dataset, and this time use Name Manager tool for removing all external references from the dataset. The modified dataset will be stored in a separate “Name Manager” worksheet.
Steps:
➤ Open the Name Manager worksheet, from the main menu, navigate to Formulas >> Defined Names >> Name Manager.
➤ Next, from the Name Manager window, select all listed named ranges and click on Delete to remove them.
➤ Now, head to Data >> Workbook Links and click on Break all to remove all external references.
➤ The dataset should now be free of any external references.
Remove External Links Through Data Validation
Excel’s Data Validation tool is a useful feature that allows users to control the type of data or values that can be entered within a range of cells. But when validation rules are linked to external workbooks, the Break Links tool may fail to remove those links and show an error.
We will again work with the same dataset and remove external links from the dataset. We will display the updated dataset in a separate “Data Validation” worksheet.
Steps:
➤ Open the Data Validation worksheet and head to Data >> Data Tools >> Data Validation from the main menu.
➤ In the Data Validation dialogue box, set the Allow criteria to Any Value, and then click OK.
➤ Next, go to Data >> Workbook Links, then choose Break Links to remove all external references.
➤ The dataset is now cleared of all external links.
Use Conditional Formatting Rules to Delete External Links
Conditional Formatting is another powerful tool in Excel that lets users apply specific formatting to cells within a dataset based on defined criteria. If any external workbook is referenced in Conditional Formatting rules, the Break Links tool may fail to remove those links and cause errors in the dataset.
Using the same dataset, we will now remove external links from conditional formatting rules and display the updated dataset in a separate “Conditional Formatting” worksheet.
Steps:
➤ Open the Conditional Formatting worksheet and from the main menu, navigate to Home >> Conditional Formatting >> Manage Rules.
➤ In the Conditional Formatting Rules Manager, select the rule that references external dataset, click Delete, and then choose OK to apply the change.
➤ Then, navigate to Data >> Workbook Links, and select Break Links to eliminate all external references.
➤ The dataset should no longer contain any external links.
Frequently Asked Questions
Can I Apply These Methods to Multiple Workbooks at Once?
No, since Excel does not provide any built-in option to break links across multiple workbooks at once, all the methods described in this article need to be applied individually to every workbook.
Which Method Should I Use For Large Datasets?
If you are dealing with large datasets, the Paste Special method would be the most efficient. This method quickly removes external links without requiring manual editing of individual cells, Conditional Formatting rules, or named ranges.
Concluding Words
Knowing how to break links in Excel, especially when Break Links tool doesn’t work, is essential for effective data management. In this article, we discussed seven effective methods to fix Break Links not working issue in Excel, including using the Paste Special tool, Changing File Format, Manually Reformating Cells, Unprotecting Sheet, Name Manager, Data Validation and Conditional Formatting. Feel free to try out each method and choose one that best fits your specific needs.
