Here you might well find a whole list of named ranges which link to cells which have since been deleted, so causing #REF! errors. To find these links, go to the Name Manager as shown in Figure 2. The file contains external links which can be found in the named ranges. If you want to try this out for yourself, this file can be downloaded here. A list of all name links can be found in the Name Manager which is on the Formulas Tab. You may have copied and pasted a sheet or cells containing a named range into your model and the link comes with it. Here’s a summary of the main causes of phantom links, and how to fix them:īy far, the most common cause of phantom links is named ranges. Sometimes, however, it can’t be avoided, especially from others’ models and we need to know how to deal with this issue. It’s always best, where possible, to have a stand-alone model which does not rely on the outputs of other files. Before I do, however, I’d just like to point out that links between workbooks should be avoided if possible as it can easily cause all kinds of issues and errors in your models not least of which is the phantom link issue. There might be a couple of reasons that these spectres are still haunting your workbooks so let’s look a few ways you can hunt down and exorcise these spooky apparitions from your models once and for all. So you delete it again but it’s still there! Try as you might, it simply won’t delete.

You look into Edit Links on the Data Tab (as shown in Figure 1), and sure enough, there’s a link in there, even though you already deleted it.

When you open the file, it asks you to update links. Usually this happens with models that have been in use for a while and that we have inherited from someone else, but sometimes it happens to your models that you’ve built yourself. Happy Halloween! I have been meaning to write about phantom links for a while, and Halloween is a good time to talk about hunting down and exorcise this ghoulishly annoying phenomenon from your financial models.Īlthough not exactly spine-tinglingly terrifying as formula errors, for example, any modeller who has been working with Excel for any length of time has probably come across the experience of being unable to delete external links.

Disable links in excel spreadsheet