There are many reasons why we need to break source, Excel external links. In most cases, it is merely source, external data referenced in cells of a financial model. In these instances it is quite simple to break these external links.

However, in other cases breaking external links will not be so obvious and easy. The reason being other elements of the spreadsheet are referencing such links.

This article and video will identify five, including the four less obvious sources of links, which many financial model users will be unaware of pinpointing.

① Excel External Links (References) in Cells

The most basic type of external reference. Even beginner spreadsheet users are aware of Excel’s capability to link from other workbooks. Go to the Editing section on the Home tab, select Find & Select, and click Find. In the Find what: window type “[“.

Choose Options. Select “Workbook” for the Within: parameter and “Formula” for the Look in: parameter. Click on Find All.

② Excel External Links (References) in Defined Names

The most common source; after external references in cells, for a financial model to contain such links. It can be embarrassing if financial modellers have created a model for Company A, and then decided to use the same model as the template financial model for Company B.

A spreadsheet might contain legacy, external links as defined names, which unless you view the Name Manager in Defined Names, will not appear as the link on the Edit Links window.

To clear the link, simply delete these defined names and if necessary, re-create the Defined Name using the Name Manager or Define Name. Go to the Formulas tab, and on the Defined Names section, select Name Manager or Define Name.

③ Excel External Links (References) in Objects such as Text Boxes or Shapes

A more innocuous source of links. In more customized, unique or complex financial models, external links might be located in such objects as text boxes or shapes.

Again, go to Editing section on the Home tab, select the arrow beside Find & Select and click Go To Special. Select the Objects radio button and click OK, which will guide you to each Object in the financial model.

④ Excel External Links (References) in Chart Data Series

This could represent a serious threat to a best practice financial model, where model users “think” an Excel Chart is referencing data from the workbook, but where it is actually sourcing data from another financial model.

Again, close all other spreadsheets, and systematically look at the Source Data for each Excel Chart to ascertain, whether the data is referencing the financial model or another workbook. Right-click the mouse, choose Select Data and edit the Data Source to the current financial model.

⑤ Excel External Links (References) in Chart Titles

A common occurrence where a Chart from one spreadsheet is copied into another workbook. Close all other workbooks, and go to each Chart and inspect the Chart Title to verify whether it is referencing another workbook.

Again make the necessary changes to the source Chart title, if it is referencing another financial model workbook.

In summary, here is the example workbook, which the video showcased how to write the macro.

Download SpreadsheetVault – Why Can’t I Kill The Excel External Links (solution).xlsb