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.

Voice of the Editor

What would you do if one of your clients won the lottery? We asked several accountants to weigh in with their advice for the lucky Powerball winner, and the tips we received are useful for anyone who receives a windfall, whether it's a lottery win, an inheritance, a big bonus on the job, or a killing in the stock market.
ADVERTISEMENT

This Week on AccountingWEB

CPAs Mira Finé, Scott Hitchcock, Rob Keasal, Kathy Scorcio, and Ken Travis offer ten pieces of financial advice for the newest Powerball winner.
Hang Bower of BDO USA and Dan Black of Ernst & Young share their perspectives on why their firms made the Best Places to Work for Recent Grads 2013 list.
Herbein + Company, Inc. firm members talked with AccountingWEB about their year-round employee wellness program.
Bill Walter of Gross, Mendelsohn & Associates and Harold Gaar of TravisWolff LLP weigh in on mobile technology use while employees are at work.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT