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.
Although 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. 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. When you open the file, it asks you to update links. What the? There ARE no links. 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. So you delete it again but it’s still there! Try as you might, it simply won’t delete.
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. 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. It’s always best, where possible, to have a stand-alone model which does not rely on the outputs of other files. Sometimes, however, it can’t be avoided, especially from others’ models and we need to know how to deal with this issue.
Here’s a summary of the main causes of phantom links, and how to fix them:
- Named Ranges
By far, the most common cause of phantom links is named ranges. You may have copied and pasted a sheet or cells containing a named range into your model and the link comes with it. A list of all name links can be found in the Name Manager which is on the Formulas Tab. Take a look at the example in Figure 1. If you want to try this out for yourself, this file can be downloaded here. The file contains external links which can be found in the named ranges. To find these links, go to the Name Manager as shown in Figure 2. Here you might well find a whole list of named ranges which link to cells which have since been deleted, so causing #REF! errors. Tidy up the name manager, and delete any named ranges that are no longer being used. Removing any named ranges that contain external links will fix the phantom links issue.
Figure 1: Breaking Links through Edit Links
Figure 2: Edit or Delete Links with the Name Manager
- Conditional Formatting
If you’ve used a formula with Conditional Formatting, this may also be referring to external cells. Select Conditional Formatting from the Home Tab and select Manage Rules. Instead of searching the current selection, search for rules in the entire sheet as shown in Figure 3. You can see which cells have the conditional formatting applied to them in the Applies to field. The external link can be removed by either deleting or editing the conditional formatting rule here in the Rules Manager.
Figure 3: Searching for External Links in Conditional Formatting
Charts can also contain external links that are difficult to find. In order to locate and eliminate these phantom links click on each data series or text box of the chart and examine it whether it relates to another workbook or not. Look at the formula and see if you can see any external links. If you find any, either remove the chart or copy or move the chart data into the current workbook and then rebuild the chart.
Objects are shapes or pictures that have been embedded into the file which can also contain external links you might not be aware of. Sometimes these objects can be well hidden but there’s an easy way to find any objects on the page. Go to Find & Select on the far right-hand side of the Home Tab and select Go To Special. Alternatively, press F5 or use the Control+G shortcut and select Special and OK. Once you get to the Go To Special dialog box as shown in Figure 4, select Objects and press OK. This will select every object on the sheet (whether you can see it or not) so then press delete to remove it. You will need to do this on every single sheet of the entire file. Remember that some sheets might be hidden, so right-hand click on the tab name and select Unhide to find any hidden sheets.
Figure 4: Finding Objects with Go to Special
Check for PivotTables and make sure they are not referring to data that is contained in an external file. If you have any PivotTables, click on the table, and on the Analyze Tab, click on Change Data Source. This will take you to the source data for the PivotTable.
If you’ve tried all of the above and are still having issues, here’s another trick you can try. Any external link will look something like this: =’G:\Company\PlumSolutions\Phantom Links\[Cash Flow Financial Model.xlsx]IS Cash Flow’!$C$5 so you can try searching for one of the parts of this statement, such as “[“, “]” or “’G\” for example. Use the shortcut Control+F to find. Make sure you’re looking in Formulas, not just Values as shown in Figure 5.
Figure 5: Searching for External Links with Find and Replace
I hope the above tips will save you some time and effort when trying to drive out these unwanted demons from your Excel workbooks! If you have any additional tips to add, please tell us about them in the comments below.