The haunted External Links that won’t go away…

Have you ever bothered by this never-go-away warning message?

You’d searched all worksheets (visible or hidden and very hidden too) in order to locate the external links and remove them.  Somehow, you just could not find any cells containing formula linked to external workbooks.  You are totally lost as this warning message just popped out every time you opened the workbook.  So the question in your mind… Where are the External Links?????????

Before locating where external links reside in your workbook, let’s answer the basic question first.

What is External Link?

Simply put, it is a formula referring to another (external) workbook.

To create one, type = in the formula bar and then use mouse to point to any other cell on any sheet of an external workbook.  Excel creates the formula you see above.

The automatically generated formula consists of [The workbook name] (enclosed by square brackets) followed by SheetName! and finally the Cell Reference.

When the workbook you linked to is not opened, you will see the File Path of it too.

p.s. I am not a fan of external links.  Indeed I try best to avoid external links whenever possible.

How to locate where External Links are?

You may download a Sample File to follow along.

Given the special structure of the formula, we can use “FIND” to locate them quickly.

Press Ctrl+F to open the Find dialog box:

1. Find what: .xls  ‘tip: don’t miss the dot; for a more robust find, try .xl*]
2. Within: Workbook (as you want to find in the entire workbook)
3. Look in: Formulas
4. Find All

Note: Click Options in order to perform step 2 and 3

There the results are! 🙂  Quick and easy BUT incomplete.

• First, hidden sheets are ignored;
• Second, Objects like shapes, chart titles are not included either:

Apparently, FIND only looks into the grid, not above the grid.  @_@

Worst still, the FIND method only helps locate the cells with external link.  It doesn’t remove the link for you.  If we want to remove the link, we still need to go to the cells, do the Copy and Paste value one by one…  Not quite efficient if we want to remove all the links while keeping current values.

No worry!  It’s Excel.   It should have a smarter way.

Break Link – To remove all external links while keeping current values

This “Break Link” is quite hidden in the ribbon:

1. Data Tab –> Queries & Connections group (it used to be Connections group in earlier versions of Excel)
2. Edit Links
3. Select the source (Note: there could be more than one sources if your workbook contains links to multiple workbooks)
4. Break Link

Since this action is not undoable, we will be prompted to confirm:

WOW!  All the external links on worksheets (hidden or not) and objects are gone with existing values kept.

It works perfectly…

… except one case: When the same source file resides in Name Manager too.

Now download this Sample File v2 and repeat the steps of Break Link stated above.  You will find that the Break Link works only partially: It breaks links on Objects, but not on cell…. @_@

Then what to do?

To make it work, we need to fix the external link(s) in Name Manager manually.

1. Go to Formulas tab
2. Name Manager (Tip: Ctrl+F3 is the keyboard shortcut to open Name Manager)
3. Select the Name that refers to external workbook.  (Tip: if there are many names defined, you may sort the names by clicking on Refers To for grouping names together.)
4. Either Edit the name so that it refers to current workbook or Delete the Name.  (Warning: If you Delete the Name, you may get #NAME? error for formulas using the Name.)

Once this hidden External Link is fixed, the magical Break Link function works again! 🙂

Wait…

What if, we don’t want to Break Link.  We do not want to change any formulas in the workbook.  Can we still get away the warning message when we open the workbook?

Why not?

Let’s rewind a little bit to the screenshot of Edit Links:

Do you see the Startup Prompt… icon sitting quietly on the lower left corner?

Let’s click on it and see:

By default, “Let users choose…” is selected.  Now we have two more options to choose from.  And the options are self-explanatory.  Aren’t they?

Now we can say goodbye to the warning message for the Haunted External Links. 🙂