Locate cells containing external links

By Anna Louie on April 28 2011

I’m sure that as you have worked with various financial models, you have no doubt linked formulas to other models and workbooks for various reasons; from a quick fix solution, copying cells across to other workbooks or linking to other models that contain data dumps.

Modelling with external links is not recommended as

• It diminishes the transparency of calculations and assumptions in the model
• Excel’s features for editing links are not modelling-friendly
• The source model or file is often not easily accessible
• The source of the linked cell may have been renamed
• The source is most likely out of date

I suggest that the cell that contains the external link is replaced with an assumption cell in the Inputs sheet. The trouble is that the dreaded Excel feature to “break links” will only sever the ties without actually uncovering the cell that contains the external link. So a big rule in modelling - do not break links! This action only hardcodes the cell which contains the external link, which causes hardcoded cells scattered throughout the model increasing the model risk. Instead, I present other methods to locate the cells in Excel that contain external links so that you can replace them in a controlled manner.

Three ways to find cells with external links

Use these three simple ways to identify the cells that contain external links in Excel, which you can then replace with the appropriate assumption cell.

1. Search the workbook for “[“ or “]”
   The file path of the external link will contain the square brackets so all cells that contain this will be found.
   If you tend to use square brackets rather than parentheses this search will be time consuming, so alternatives are to search for “C:” or any letter drive the external file is saved on, or “\” which is part of file names, example shown below.
   
2. Look in the list of named ranges
   Use the Excel keyboard shortcut “ctrl + F3” and search for names with file paths. This is often missed and names can be imported into a model if you have copied and pasted the cells with names across.
   Below is an example of names that have been copied across but have #REF errors as the cells have been deleted.
   To go to the cell that contains the external link, click on the name.
   
3. Use the Compatibility Checker
   When saving a model in Excel 2007 there will be a prompt box with a message similar to the one below.
   Click on the “Find” hyperlink and it will automatically take you to the cell that contains the link. This option is worthwhile if there are only a few external links as it will identify one cell at a time, and the next cell is identified by repeating this process of Saving --> Find.
   

External links and other best practice guidelines

Producing a financial model that is transparent, flexible and well presented are the cornerstones of best practice modelling. Avoiding cells with external links is one of the many tips and guidelines that Navigator uses and teaches in our Project Finance Modelling (A) training course.

Comments