Locate Phantom Links in Your Excel Workbooks

You may have encountered the infamous "phantom link" phenomenon. I've never known Excel to be wrong about identifying links, so there's an excellent chance that your workbook does contain one or more links -- but they are probably not formula links.

Follow these steps to identify and eradicate any links in a workbook.

  • Select Edit, Links. In many cases, this command may not be available. If it is available, the Links dialog box will tell you the name of the source file for the link.
  • Click the Change Source button and change the link so it refers to the active file.
  • Select Insert, Name, Define. Scroll through the list of names in the Define Name dialog box and examine the Refers to box (see the figure below). If a name refers to another workbook or contains an erroneous reference such as #REF!, delete the name. This is, by far, the most common cause of phantom links
  • If you have a chart in your workbook, click on each data series in the chart and examine the SERIES formula displayed in the formula bar. If the SERIES formula refers to another workbook, you've identified your link. To eliminate the link move or copy the chart's data into the current workbook and recreate your chart.
  • If your workbook contains any custom dialog sheets, select each object in each dialog sheet and examine the formula bar. If any object contains a reference to another workbook, edit or delete the reference.
  • Next, save your workbook and then re-open it. It should open up without asking you to update the links.

You may like these other stories...

Accountants who specialize in forensic and valuation services point to electronic data analysis, or big data, as the most pressing issue they’ll face in the coming months, according to results of a new survey released...
As complex as federal tax can get, at least you're only dealing with one agency: the IRS. But when you get into state and local sales tax, you're coordinating hundreds of jurisdictions that are constantly changing....
All that was needed on Tuesday was a voice vote for the House of Representatives to pass a bill that would prevent state and local governments from taxing access to the Internet.Now the ball is in the Senate’s court....

Upcoming CPE Webinars

Jul 24
In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.
Jul 31
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.