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...

Regulatory compliance, risk management and cost-cutting are the big heartburn issues for finance execs in the C-suite. Yet financial planning and analysis—a key antacid—is insufficient.That's just one of the...
Continuing its efforts to simplify accounting procedures, the FASB has issued a proposed Accounting Standards Update on customer fees paid in a cloud computing arrangement. The newly-proposed update (Intangibles—...
How are you planning? What tools do you use (or fail to use) for forecasting? PlanGuru is a business budgeting, forecasting, and performance review software company based in White Plains, N.Y. AccountingWEB recently spoke...

Already a member? log in here.

Upcoming CPE Webinars

Aug 26
This webcast will include discussions of recently issued, commonly-applicable Accounting Standards Updates for non-public, non-governmental entities.
Aug 28
Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.
Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.
Sep 11
This webcast will include discussions of commonly-applicable Clarified Auditing Standards for audits of non-public, non-governmental entities.