Save content
Have you found this content useful? Use the button above to save it to your profile.
spreadsheet
istock_wombatzaa_sp

Modelling tips and tricks: Phantom links

by
14th Aug 2018
Save content
Have you found this content useful? Use the button above to save it to your profile.

As a professional modeller, FCA and Excel MVP Liam Bastick highlights some of the more useful functions for financial modelling and Excel spreadsheets. In this article, he considers the dreaded “phantom link”.

A common scenario

It’s late. You have been working on a spreadsheet all day and it’s time to acknowledge it’s time to go home and start afresh. There’s not much left to do and you will be able to finish in the morning.

The next day, you walk in, the sun is shining, the birds are singing. All is well with the world. You open up last night’s work and get the following above your formula bar:

1

Nooo... You didn’t link this workbook to any other file. You could have sworn there was no link in there last night. You need to seek and destroy. This is not what you needed.

Sounds familiar?

Assuming you wish to find the links and decide individually whether they should be retained or removed, there are several steps you may need to employ.

The first step to take is to ascertain what type of links you have. One way of doing this is to select Edit --> Links using the keyboard shortcut  ALT + E + K:

2

This command will not be available in all instances. If it is, you will probably have Formula Links. If Edit --> Links is available, a dialog box will appear:

3

There may be more than one file linked. Upon inspection, you may notice that one or more file may simply be an older version of the active workbook. If so, the active workbook can be substituted for each file in turn by clicking on the ‘Change Source…’ button (ALT + N) and following the directions. This will remove referencing errors.

Some files you may not have access to and may cause errors if the file is inadvertently updated. By selecting the Break Link button, these links can be replaced by their current values (N.B. this action cannot be undone – you may wish to save the file beforehand in order to rectify errors).

Formula links

If you do have formula links, it is relatively straightforward to search for these links:

  • Close all workbooks except the active workbook with the links in
  • Click on ‘Find & Select’ of the ‘Editing’ section of the ‘Home’ tab – or simply use CTRL+F

4
 

  • Click ‘Options’
  • In the ‘Find what’ box, enter [
  • In the ‘Within’ box, click Workbook
  • In the ‘Look In’ box, click Formulas
  • Click ‘Find All’
  • In the box at the bottom, look in the ‘Formula’ column for formulas that contain [
  • To select the cell with a link, select the row in the box at the bottom

5

Other “phantom” links

There are other types of links, often referred to as “phantom links” due to being difficult to locate, compared to formula links. But once you know, it’s easy!

Name links

This is probably the most common cause of phantom links: names that reference ranges in other workbooks.

Using Name Manager (simplest method of calling this dialog box: CTRL+F3), we can get a list of all the names in the workbook:

6

By scrolling through the list of names and examining the ‘Refers To’ section, names referring to other workbooks or containing erroneous references such as #REF! can be amended or deleted.

Chart links

If you have charts in your workbook, there are various places where hidden links could be lurking. Click on each text box or title and examine the formula bar for references to other workbooks. 

Further, click on each data series in the chart and examine the SERIES formula for external references. These links can be removed by copying (as values) the data located into the active workbook.

Object links

External references can be attached to objects also. The simplest way of reviewing objects in a workbook is to use the highly underrated Go To --> Special function (simply use the F5 function key and then click ‘Special’). In the next dialog box, simply select ‘Objects’, then click ‘OK’.

Go To dialog box

 

Go To Special: Selecting objects

7

 

8

 

By pressing the Tab key and examining the formula bar, each object can be reviewed in turn for external references. 

Upon completion of the above process, unless your workbook includes web queries containing parameters, data connections or copied in conditional formatting / data validation, all links should now have been reviewed. If the intention was to remove all such links, simply save and reopen once all deletions have been made. 

Word to the wise

So why is this an “error”?  Well, the links were not intended. The focus of this article has been on review and removal since this is more beneficial and because links can occur for a variety of reasons, e.g. copying range names, cells, worksheets, data validation or conditional formatting from another workbook. If you do have to copy from elsewhere, try to copy and paste special as values where possible.

Tags:

Replies (1)

Please login or register to join the discussion.

avatar
By Alf
17th Aug 2018 09:21

Another method of finding unwanted links (which I think worked for me when the above methods did not, although I can't remember the exact circumstances) is:
File > Info > [check for issues] (Inspect Workbook) > [check compatibility] > select [find] and it takes you to the error cell

Thanks (2)