Identify external links in Excel without using VBA

Sometimes deliberate and sometimes inadvertent, links to external data sources often work their way into our Excel workbooks. To understand what they are and why they exist, we need to first know which external sources are referenced, because we can't find what we are looking for if we don't know what we are looking for. I feel a U2 song coming on. That's right, I can't live with or without my links — of which there may be more than one. Many years ago, I wrote an article that explained how a macro could be employed to list all external links in an Excel workbook:

If you are terrified of Visual Basic for Applications (VBA) and hate programming, don't worry — feel free to ignore the above: I have no plans to explain it. I reproduce it for effect only.

Whilst working on a client project recently, I came across this wonderful trick that combines old, "outdated" Excel with one of the very latest features in Excel. It is so simple and just blows the above solution out of the water. I would love to take the credit for this, but I cannot: Longtime Excel Most Valuable Professional (MVP) Bob Umlas, please take a bow, because the crux of this trick belongs to you.

Before VBA came to Excel, coding was undertaken using what were known as Excel 4.0 (xl4) macro functions. These old xl4 macro functions are still doing the rounds because Microsoft cannot get rid of anything, because the software giant knows that some spreadsheets still in use probably were developed before the wheel was.

I have written before about EVALUATE, which is a very useful function (it essentially converts text strings into formulas that may be, er, evaluated). For example, consider the complex spreadsheet shown in the screenshot "Simple Example".

Simple example

Theoretically,

=EVALUATE(A1&A2&A3)

would be EVALUATE(1+2), which is 3. That's all good, except it doesn't work unless you use it via a range name definition.

You won't find it in Excel Help ("That function isn't valid."), but as I say, it is recognised as long as you use it inside an Excel range name. And its sister function, LINKS, which recognises external links in an Excel workbook, behaves very similarly.

Thus, the process for identifying and listing external data links in an Excel workbook is very easy.

First, let's define a range name, as shown in the screenshot "Define Name", which shows Define Name in the Formulas tab of the Ribbon.

Define Name

This opens the box shown in the screenshot "New Name Dialog".

New Name dialog

Here, I have created a new range name, called listlinks, which refers to the formula:

=LINKS()

If I were to type this formula straight into a cell, I would get the following (aforementioned) error shown in the screenshot "Invalid Function Error Screen".

Invalid function error screen

But if instead I were to type in =listlinks (ie, my freshly minted range name), I wouldn't get an error if the model contains links to external data sources (as shown in the screenshot "Range Name With External Links") and you have an Office 365 version of Excel.

Range name with external links

However, if the model doesn't contain links, I would receive a prima facie error, as shown in the screenshot "Range Name With No External Links Error".

Range name with no external links error

In the first instance, Office 365 has spilled the references; ie, it has listed the references in adjacent cells along the same row. Good old Excel: It does like to default to the incorrect choice. To counter this great default "feature", if I were to use TRANSPOSE, suddenly things become much more readable, as shown in the screenshot "Range Name Transposed".

Range name transposed

Voilà! All your links are presented dynamically.

This is just so much simpler than convoluted VBA code or using third-party software. All you need is the ability to spill your results; ie, your version of Excel supports dynamic arrays (presently, this means using an Office 365 version of Excel).

That's all there is to it.

Word to the wise

Excel 4.0 functions stored in defined names may only be saved in macro-enabled workbooks (.xlsm or .xlsb). If you are using this feature in conjunction with dynamic arrays, the file will have to be generated using Excel 365, too, so do be aware of these limitations when incorporating this functionality into existing workbooks.

It should also be recognised that the VBA approach will identify cell locations, whereas the new trick detailed above does not. However, links may occur for other reasons that the macro may not locate. Therefore, it's a case of using the right approach for the right scenario.

Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling and Continuing Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Oliver Rowe at Oliver.Rowe@aicpa-cima.com.