Excel Links – How to Remove Phantom External Links in Excel

microsoft excel

I have an excel spreadsheet with some an external link in that I can't get rid of.

I have read up on the internet quite a lot and Microsoft recommend the following:

  • For references in Cells: Using search and replace for [*] to find all references in worksheet cells.
  • For references in names: Opening up the name manager and making sure there are no external references there.
  • For references in Objects (this is really painful): Select each object individually and look in the formula bar.
  • For references in Charts (also painful): Check the formula bar the title and data series for every chart.

This still leaves me with a phantom external reference that I can't find, so I try some more things

  • The "Data – Edit Links" feature in Excel followed by clicking on "Break link" (nothing happens)
  • Used a "FindLink" plugin (didn't work for me, but was friendly to use)
  • Used Microsoft's DeleteLinks plug in (also didn't work and isn't friendly to use)

Best Answer

For anyone else that's spent hours combing their file, the problem can also exist if you've copied a data validation range over from another workbook.

To fix it :

Ribbon-->File-->Check for Issues-->Check Compatibility

This will bring up the checker that will tell you if Validation points to an external sheet.

Most importantly, it will tell you which sheet it is on.

Anyway once you know the sheet(s), go to the sheet. Now

Ribbon-->Home-->Down arrow next to Find and Select-->Data Validation.

This will select the cells that have Data Validation applied.

Now

Ribbon-->Data-->Data Validation

and fix the broken reference in the "Source" field, or click "Clear All" if you don't need it.

Related Question