TUESDAY TIPS: This welcome new feature in Excel helps find all External Links

External links in Excel are exactly what the name suggests: links to data outside or external to the active file, usually in another Excel file. These references are not dynamic in the same way as links within a workbook.  If you insert or delete rows in the referenced workbook without having both linked workbooks open, the references will become misaligned. External links are also prone to breaking if the source file is moved, deleted, renamed or shared with someone who does not have access to the source file. Additionally, External links introduce potential security concerns as they allow data to be pulled into a file automatically from external sources.

To avoid these issues, when transferring data between workbooks we usually recommend creating a duplicate interface sheet in both workbooks. Data can then be copied from the live linked interface sheet in the source workbook and pasted as values in the interface sheet of the destination file.

However, despite our best efforts to avoid them, often external links still manage to creep into files. This can occur when we receive files from others who have intentionally used external links, or unintentionally when we copy data between files. Unhelpfully, Excel does not immediately warn us when an external link has been created – it is usually only after we have closed and re-opened the file that a pop-up message alerts us to the existence of these links.

Until very recently, finding External Links involved a painstaking, multi-step process of searching through cells (using the Find function), reviewing the Name Manager, manually inspecting Data Validation, Conditional Formatting, Graph source data and Objects. For large workbooks, this could be a time-consuming and frustrating process.

The good news is that Microsoft has finally heeded our calls and incorporated an excellent new feature in the latest version of Excel, allowing users to track down links in both the cells and the back-end of a workbook at the click of a button. Another (slightly more technical!) change we have noticed is that when cells containing Data Validation or Conditional Formatting with direct references to cells within a workbook are copied to another workbook, Excel now removes this validation or formatting automatically instead of creating an External Link. While this is a step in the right direction, the downside is that users can find these features unexpectedly missing as there is no warning message.

Like many other useful new functions (watch this space for an upcoming blog on this topic), these changes have crept into Excel for Microsoft 365 unnoticed to most. We hope that the below guide will increase awareness of this valuable time-saver.

Identifying whether a workbook contains External Links
The first step to addressing External Links is of course awareness of their existence. When opening a workbook containing such references, a Security Warning flagging that the automatic update of links has been disabled will usually appear at the top of your workbook. (For some users, this may not pop up as it is dependent on customised security settings).

This message will disappear as soon as ‘Enable Content’ is selected and will only appear again when a workbook is closed and re-opened, i.e. it does not appear as soon as a reference to an external source is created.

The most reliable way to check for External Links is therefore to go to the Data tab on the ribbon. If the Workbook Links button is greyed out, your workbook has no external links. If clickable, your file contains links to other workbooks.

Finding External Links using the Find next button

While Excel gives the option to Break Links (this button appears in the right-hand margin when clicking the Workbook Links button), this option will convert all external links to hard-coded values. Instead, you may need to replace some of these references with links to cells within your model. We therefore recommend avoiding this blanket approach and rather addressing the links individually or in clusters.

You can use the Find next button on the Workbook Links sidebar to find and resolve external links within your workbook one at a time. Clicking on the name of the source file will expand details of where links exist and reveal the Find next option as shown below.

You can now use the Find next button to work through and resolve all External Links in your file. Clicking on the green “name” or “conditional formatting” links will take you directly to these menus in the back-end of your workbook, allowing you to easily view and address these links. If an external reference is linked to an object (such as a shape or graph), the Find next button will activate the relevant object. By right-clicking on the activated object, you should then be able to identify the linked external source.

Final thoughts

With the emergence of Microsoft 365, most users now receive regular automated updates to their Excel application without even realising it. While the big advantage is that users always have access to the latest features – gone are the days of migrating to a new desktop version every few years! – this means that sometimes great changes sneak in unnoticed. With this in mind, we will share some more examples of useful new functionality in an upcoming post.