Finding and Deleting Unwanted and Orphaned Links in Microsoft Excel 2010

In this article, we will learn how to find and delete unwanted and orphaned links in Microsoft Excel 2010.

While working on reports, templates & dashboard, you create many Named ranges & after few days, you want to keep only the used ranges in ‘Named Manager’.

Links are created unintentionally by copying or moving sheets containing range in Defined Names from one workbook to another. When moving or copying a sheet by selecting its 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.

Let us take an example:

• We have Employee data, Emp. ID, Name & Age in columns A, B & C respectively.
• To track & remove all the ranges that you have made unintentionally (unwanted links) in ‘Name Manager’,you need to follow the below steps:
• Click on FORMULAS ribbon
• Click on Name Manager or Press CTRL + F3 shortcut key

• The Name Manager dialog box will appear

• You will see 3 lists. To track the unwanted named ranges. Follow the below given steps:
• Select any blank cell & Press F3, and then click on Paste List

• You will be able to see all the named ranges with sheet names & the linked cells

• To delete unwanted or useless named ranges
• Click on Name Manager or Press CTRL + F3 shortcut key

• Click on the list for deletion & click on Delete. You can delete multiple lists at a time using SHIFT & CTRL keys.

• Excel will pop up a message: click on OK to delete

• The named range will get deleted from Named Manager. Refer below screenshot