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:
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.
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.
Content seriesView full content series
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:
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:
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).
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
- 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
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!
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:
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.
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.
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|
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.
About Liam Bastick
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their strategic valuations team in London. He was also a senior member of the UK Post Office’s M&A and strategy teams and has worked for / assisted various other Australian modelling companies including BPM, Corality, Navigator Project Finance, PKF and SumProduct.
He has worked in the UK, Australia, Belgium, Denmark, France, Germany, Hong Kong, Indonesia, Malaysia, New Zealand, United States, Switzerland and Vietnam, with many internationally recognised clients, constructing and reviewing strategic, operational and valuation models for many high profile IPOs, LBOs and strategic assignments. Liam is a Fellow of the Institute of Chartered Accountants (ICAEW), a Fellow of the Institute of Chartered Management Accountants (CIMA) and is a professional mathematician.