Remove Phantom Workbook Links
External links are links that reference another workbook. Unexpected external linking can occur for various reasons, many of them stemming from moving or copying charts, chart sheets, or worksheets into another workbook. Knowing why they're there doesn't always help you find them, though. Here are a few ways to deal with the spooky phantom link problem.
First, you need to see whether you have any real external links (nonphantom) that you forgot about. If you are not sure whether you have real external links, start looking in the most obvious place: your formulas. You can do this by ensuring no other workbooks are open and then searching for [*] within the formulas on each worksheet. Close all other workbooks to ensure that any formula links will include [*], where the asterisk represents a wildcard string.
Excel 97 doesn't provide the option of searching the entire workbook, but you can search all worksheets in a workbook by grouping them. You do this by right-clicking any Sheet Name tab and choosing Select All Sheets. In later versions of Excel, Find... and Replace... provide the option of searching within the sheet or workbook.
Once you find the formula links, simply change the formula accordingly or delete it altogether. Whether you change the formula or delete it depends on the situation, and only you can decide which route to take. You also might want to consider going to the Microsoft Office Download Center (located at http://www.microsoft.com/downloads/Search.aspx?displaylang=en) and downloading the Delete Links Wizard. The Delete Links Wizard is designed to find and delete links such as defined name links, hidden name links, chart links, Microsoft query links, and object links. However, in our experience, it does not find phantom links.
Once you're confident there are no formula links, you need to ensure that you don't have any nonphantom links lurking somewhere else. To do this, we like to start from within the Excel workbook containing the phantom links. Select Formulas → Name Manager under Defined Names options and check in the Refers To: column to make sure none of the names are referencing a different workbook (pre-2007, select Insert → Name → Define and scroll through the list of names, clicking to highlight each one and looking in the Refers To: box at the bottom).
Pre-2007 users, instead of clicking each name in the Define Name dialog, can insert a neww orksheet and select Insert → Name → Paste. Then, from the Paste Name dialog, click Paste Link. This will create a list of all the names in your workbook, with their referenced ranges in the corresponding column.
Excel 2007 users can see all the names in the Define Name dialog easily, but if you wish to paste to a workbook, select Formulas → Use in Formula → Paste Names, then select the Paste List button from the Paste List dialog.
F3 will also bring up the Paste Name dialog and works in all versions.
If any of the names are pointing outside your workbook, you've found the source of at least one link that would prompt the updating question. Now it's up to you to decide whether you want to change this range name to refer only to the workbook itself or leave it as it is.
Another potential source of links is in your charts. It's possible that your charts have the same problem we just explained. You should check that the data ranges and the X-axis labels for the chart aren't referencing an external workbook. Once again, you get to decide whether the link you've found is correct.
Links also can lurk in objects, such as text boxes, autoshapes, etc. Objects can try to reference an external workbook. The easiest way to locate objects is to select any single cell on each worksheet and then select Home → Find & Select → Go to Special or click F5 and check the Objects option, then click OK (pre-2007, Edit → Go To... → Special, check the Objects option, and click OK). This will select all objects on the worksheet. You should do this on a copy of your workbook. Then, with all objects selected, you can delete, save, close, and reopen your copy to see whether this has eliminated the problem.
Finally, the last not-so-obvious place to check for real links is in the hidden sheets that you might have cleverly created and forgotten about. Unhide these sheets by selecting View → Unhide under Windowoptions (pre-2007, Format → Sheet → Unhide). If the Unhide option on the right-click Sheet submenu is grayed out, that means you have no hidden sheets.
Nowthat you have eliminated the possibility of real links, it's time to eliminate the phantom links. Go to the haunted workbook with the phantom links and select Data → Edit Links under Connection options (pre-2007, Edit → Links...). Sometimes you can simply select the unwanted link, click Change Source, and then refer the link back to itself. Often, though, you will be told that one of your formulas contains an error, and you will not be able to do this.
If you can't take the easy way out, note to which workbook Excel thinks it is linking (we'll call it the well-behaved workbook). Create a real link between the two by opening both workbooks. Go to the problem workbook and, in any cell on any worksheet, type =. Nowclick a cell in the well-behaved workbook and press Enter so that you have a true external link to the other workbook.
Save both workbooks, but don't close them yet. While in the problem workbook, select Data → Edit Links (pre-2007, Edit → Links...) and use the Change Source button to refer all links to the well-behaved workbook to which you just purposely created a link. Save your workbook again and delete the cell in which you created the true external link. Finally, save your file.
This often eliminates the offending phantom link, as Excel nowrealizes you have deleted the external link to the workbook. If this does not solve the problem, however, try these next steps, but make sure you save a copy of your workbook first.
The following process involves deleting data permanently. Therefore, before you begin, create a backup copy of your workbook. Neglecting to do so could create new problems for you.
With the problem workbook open, delete one sheet, save, and then close and re-open the workbook. If you are not prompted to update your missing links, the sheet you deleted contained the phantom link. This should solve the problem, but if it doesn't, repeat the first step for each sheet in the workbook. You will need to add a new sheet before you delete the last sheet, as any workbook must have at least one sheet.
If this technique worked, here's what you should do next. Open the copy of your workbook (the one that still has data in it) and make another copy. You've got to work with the problem worksheet (or worksheets) and use the process of elimination to discover where the problem is in the worksheet.
With the problem worksheet active, select a chunk of cells (about 10 x 10) and then select Home → Clear → Clear All under Editing options (pre-2007, Edit → Clear → All). Are you absolutely sure you saved a copy? Save, close, and reopen the problem worksheet. If you are not prompted to update those links, you found the problem and your reward is to redo that block of cells.
If you are prompted to update the links, continue deleting cells until you are no longer prompted. Then redo the badly behaved cells. We hope these techniques will save you some of the frustration that arises when those dreaded phantom links appear in your workbooks. They're not easy or fun to perform, but they can get you out of trouble.