Categories
Excel

Remove Phantom Workbook Links

You open your workbook and are prompted to “Update Links,” but there are no links! How can you update links when they don’t exist?

You may open a workbook and see a message asking whether you want to update links in a workbook. This message sometimes appears even when a workbook contains no linked formulas.

Phantom links are external 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.

Here are a few ways to deal with the spooky phantom link problem.

Find Phantom Links Manually

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, you need to check every cell, formula, name, chart, object, and comment in your workbook for any reference to an external file.

You can do this by ensuring no other workbooks are open. Press Ctrl+F (or select Home > Editing > Find & Select > Find) to start looking in the most obvious place: your formulas.

  1. Click Options to expand the Find and Replace dialog box
  2. Select Workbook in the Within: drop-down to search the entire workbook.
  3. Select Formulas in the Look in: drop-down to search only formulas
  4. Then search for [*], the asterisk represents a wildcard string.

Also search for common link indicators, such as:

  • The file path of the external file, such as C:\Users\BrainBell\Documents\
  • The file extension of the external file, such as .xlsx or .csv
  • The square brackets around the external file name, such as [Book1.xlsx]
  • The exclamation mark after the external file name, such as Book1.xlsx!

If you find any of these indicators, you need to delete them or replace them with valid references. You also need to check if any of your data validation rules, conditional formatting rules, pivot tables, or slicers are linked to external files and remove them if necessary.

Using Edit Links Feature

The Data > Edit Links allows you to see all the external links in your workbook and manage them easily. To use this feature, follow these steps:

  • Click Data tab and choose Edit Links in the Queries & Connections group.
  • In the Edit Links dialog box, you will see a list of all the external files that your workbook is linked to.
  • Select files and click on Check Status to see if the files are accessible or not.
  • If the Status column shows Error: Source not found, click Break Link to remove the link or click Change Source and browse for the workbook.

If you break a link, Excel will replace the link with the current value of the cell or object that contains it.

Using Name Manager

If previous methods don’t solve the problem, the phantom links may be caused by an erroneous name:

  1. Choose the Formulas tab and click on Name Manager in the Defined Names group.
  2. In the Name Manager dialog box, look for any names that have a reference to an external workbook. You can identify them by the square brackets around the workbook name, such as [Book1.xlsx].
  3. Select the name that contains a phantom link and click on Delete. Confirm your action by clicking on OK.
  4. Repeat steps 2 and 3 for any other names that have phantom links.
  5. Close the Name Manager dialog box and save your workbook.

Understanding Workbooks and Worksheets: