ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week

Excel Tip of the Week #387 - Revisiting fixing broken links

Author: David Lyford-Smith

Published: 30 Mar 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week we have a General User post in which we’re going to look at broken workbook links – how they happen, why they’re a problem, and how to fix them. This is an expansion of the topic first covered back in TOTW #226.

Cross-workbook links and why they can go wrong

Before we get too embedded in the details, let’s look at cross-workbook links more generally and the problems these can create.

Write a formula reference between two open workbooks and you’ll see something like this:

Excel screen shot of cross-workbook link
The syntax is clear enough and easy to read, but does depend on the other workbook being open. Close it, and you’ll see this instead:
Excel screen shot of a cross-workbook link
This is informative but hardly succinct – the full filepath of the source workbook is spelled out. Finally, here’s what happens if we then move the source workbook elsewhere and then reopen the file with the formula at a later date:
Screen shot of an error box

Notably, the cell value remains as 1 – the last known value for that source – but there’s no longer any way to know if this is accurate to whatever’s in the source workbook. This behaviour can mean that an innocent user ends up working with expired data without realising it – especially as most people are trained to dismiss error messages without reading or thinking about them.

How to fix or remove links

The first and best option when dealing with a broken link is to un-break it.  If you know where the file has moved to, then clicking Edit Links, either from the above error prompt or from the Data Ribbon, will let you see what the issue is:

Excel screen shot of edit links

Update Values will try and get the latest values from the source (even if it is closed), which can be useful, but in our case we want to Change Source. Update to the latest location and the value will be updated and the proper connection restored.

If the source workbook is lost, or we no longer want the link to exist, instead we can use Break Link to replace the cross-workbook link with a hardcoded value matching the most recently available data from the source workbook. This is quick and automatic and will apply to all formulas in the current workbook… But only formulas. This causes plenty of issues where the “Break Link” button appears not to function because the cross-workbook link appears somewhere other than a formula.

Here's a full inventory of where a cross-workbook link could be hiding and how to fix it:

Named ranges

You can include a cross-workbook reference in the definition of a named range:

Excel screen shot of edit name

While Break Links will convert any cells using this named range to values, the named range itself will persist. You can detect these by opening the Name Manager from the Formulas Ribbon, where external links are easy to see, and can also amend or delete the names from there.

Data validation rules

While you can’t create direct references to other workbooks using data validation, the INDIRECT function can be used in a custom rule to call upon an external workbook:

Excel screen shot of data validation error

These can be detected by finding cells with data validation rules and then inspecting them from the Data Ribbon.

Hidden worksheets

While formulas on hidden worksheets will be updated when using Break Links, if instead you are just trying to find these links, this is a common way to miss some. You can find cross-workbook links by e.g. doing a Find on “.xls” or “[“ – but hidden worksheets will be skipped.

Conditional formatting rules

Much like data validation, you can’t create direct references to other workbooks in custom conditional formatting rules, but you can use INDIRECT:

Excel screen shot of the edit formatting rule

These can be found using Home => Conditional Formatting => Manage Rules, and amended or removed from there.

Macro buttons

This is another one where the Break Links functionality will work – although it might break your source workbook based on my testing! – but finding these links can be harder. You can at least see which functions exist from Developer => Macros.

Chart elements

One final example where Break Links works, but you might struggle to find a cross-workbook link – did you know that you can set chart titles and other elements to be equal to cells?

Excel screen shot of chart titles

This is another place that you can only really find through checking.

Hopefully this all gives you some insight into how cross-workbook links work, how they go wrong, and how to find and fix them!

You may also like

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel polaroid
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

}
}