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

 
img1
 

  • The Name Manager dialog box will appear

 
img2
 

  • 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

 
img3
 

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

 
img4
 

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

 
img5
 

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

 
img6
 

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

 
img7
 

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

 
img8
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.