Finding Unknown Links

by Allen Wyatt
(last updated June 20, 2017)

It can be frustrating to open an Excel file and be continually asked if you want to update linked information, particularly if you are not sure what information is linked. If you want to get rid of links in a workbook, there are several things to try.

First, choose Links from the Edit menu, if the option is available. (It will only be available if Excel recognizes explicit links in the workbook.) From the resulting Links dialog box you cannot delete links, but you can change the links so that they point to the current workbook. When you later save and again open your workbook, Excel will recognize the self-referent links and delete them.

Another way you can find links is to search for either the left bracket ([) or right bracket (]) in your workbook. The brackets are used by Excel when putting together the links to other files. For instance, this is a link to an external file, as it would appear in a cell:

=[Book1.xls]Sheet1!$D$7

When you find links similar to the above, all you need to do is delete them. Make sure that you search each worksheet in your workbook.

You can, if desired, try to use the Auditing tools to locate links in your cells. This can be done manually using the Auditing toolbar, and it works great--for a few cells at a time. If you have many cells and many sheets, a tiny macro may be more efficient.

The following macro will loop through all sheets in the workbook, selecting only those cells which contain a formula. Once selected, the Auditing feature is run against the cells, then processing continues to the next sheet.

Sub DisplayPrecedents()
' Loops through all sheets and selects any Formula cells
' then displays the Precedents of those cells before moving
' on to the next sheet.

' When finished, the focus is returned the first sheet

Dim c As Range
Dim sht As Worksheet

On Error Resume Next

For Each sht In ActiveWorkbook.Worksheets
    sht.Activate
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    For Each c In Selection
        c.ShowPrecedents
    Next c
Next sht

ActiveWorkbook.Worksheets(1).Activate
End Sub

When the macro is completed, you can examine the different Auditing symbols placed in your workbook. The cells that have an external link will have an icon which looks like a spreadsheet with an arrow head pointing to the cell containing the formula creating the link. You can then examine the cell and delete the link.

Another place to look for links (and which this macro will not look) is in the defined range names maintained by Excel. This is a particularly common place for links if you are working with a workbook that contains worksheets that were copied or moved from other locations. The defined names, rather than pointing to a cell range in the current workbook, could be pointing to a range in a different workbook. Choose Insert | Name | Define to display the proper dialog box. Then step through each defined name, examining the address to which it refers. Delete or change any that refer to other workbooks.

Another place to check is your macros. It is possible to assign macros to toolbar buttons or to graphics in a worksheet. Click on any custom toolbar buttons or graphics and see if you get an error. If you do, this is a good indication that the button or graphic is linked to a macro contained in a different file. If you delete the button or graphic, or change the macro assignment, the link problem should go away.

Still another possible location for wayward links is in PivotTables. When you create a PivotTable, it can refer to data on a different worksheet in your workbook. If you later move that source worksheet to a different workbook, your PivotTable will be linked to the external data source. The only solution here is to delete the PivotTable, copy the source data back to the current workbook, or move the PivotTable to the external workbook.

Finally, you should check graphs and charts. If you recently moved worksheets out of your current workbook into another workbook, it is possible that charts and graphs remaining in your current workbook now refer to data on a worksheet you moved to another workbook. If this is the case, you will need to remove the graph or chart, move it to the other workbook, or copy the source data back into the current workbook.

Since links can hide in so many places, there are special tools you can use to help track down links in a workbook. One such tool is described in the Microsoft Knowledge Base:

http://support.microsoft.com/default.aspx?scid=KB;en-us;q188449

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2141) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Counting Dates in a Range

Excel makes working with a list of dates relatively easy. If you have a list of dates, you may need to know how many of those ...

Discover More

Missing Top and Bottom Margins

You get your document set up just the way you want it, and then notice that all of a sudden Word doesn't show any top or ...

Discover More

Speeding Up Mail Merges

The Mail Merge tool in Word is a great way to create new, customized documents. If you are doing a lot of merging, you may ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (menu)

Adjusting a Range's Starting Point

Select a range of cells, and one of those cells will always be the starting point for the range. This tip explains how to ...

Discover More

Automatically Breaking Text

Want to convert the text in a cell so that it wraps after every word? You could edit the cell and press Alt+Enter after each ...

Discover More

Deleting Old Data from a Worksheet

If you keep on-going data in a worksheet, some of your data—over time—may need to be deleted. If you have an ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 3?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.