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:
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:
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:
You can include a cross-workbook reference in the definition of a named range:
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:
These can be detected by finding cells with data validation rules and then inspecting them from the Data Ribbon.
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:
These can be found using Home => Conditional Formatting => Manage Rules, and amended or removed from there.
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.
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?
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
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.
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.