Finding and Deleting Unwanted and Orphan Links in Excel 2007

In this article, we will learn how to find the orphan links and then how to delete it from the Excel workbook.

What is Orphan links?

In Excel, when we create reports, templates, dashboard then we have to create many Named ranges. After some time, we do some changes in the report and at that time, we just want to keep only the used ranges in ‘Named Manager’.

What are the reasons to create the orphan links?

When we move sheets containing the range of defined names from one workbook to another workbook. By selecting tab, unwanted links can be created in the new workbook if the copied sheet contains named ranges.
Broken links are created when the location of the precedent workbook is changed on the hard disk. If you save a workbook that is linked to another workbook and then move or copy the linked workbook to another folder on the hard disk, the first workbook will incorrectly refer to the original reference location.

How to find unwanted links by range names?

Let’s take an example to understand:-

We have data in which we have created range names.

To find unwanted links created by range Names:

Select a cell in the sheet.
In the Formulas Tab > Use in Formulas (in Named Cells Group) under the Defined Names group.

Click on Paste Names or Press F3, and click Paste List.

Check the list, and identify any defined Name you want to delete.
Formulas -> Name Manager (in Named Cells Group)orPress Ctrl+F3.
Select one Name or more (use Shift or Ctrl keys), and click Delete.

To delete unwanted or orphan links:

Select Data -> Edit Links to Files (in Manage Connections Group).

Select the source workbook whose links you want to delete, and then click Break Link or Press Ctrl+F.

In the Find box, type the square bracket ([ ) symbol (every externally linked formula has a square bracket).
Start the search and delete any unwanted formula (carefully!).

Conclusion: - You can easily find out and extract the links and define name in the Excel worksheet, and if you want to delete the external link you can use break link option in Microsoft Excel.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com