How to find external links in Excel
The use of external links in Excel is very powerful, because you can reference and access data from databases, websites, other workbooks, all sorts of input sources! It’s a very efficient way to populate your Excel models.
The issues is, what happens when Excel can’t find your external source anymore? What happens when you get broken links? We’ll show you how to find the external links and references you’ve introduced in your Excel file.
Anyone that uses external sources with any frequency has probably come across this notification:
List of external sources
Now if you click “Edit Links…” you get an interface with the external links in your workbook. In many ways this is powerful! You can open external sources, change sources, update values, and much more. In this case there’s just one link, and I can’t find it is used in my workbook, so unfortunately this interface isn’t very helpful.
This is an obvious shortcoming of Excel. While I can track down the source, I can’t actually see where the link is. What is it even in? A cell? A chart? A pivot table? Is it part of a bigger formula or just some direct reference? Maybe it’s in a defined name somewhere, where is that defined name referenced? Is my whole model full of #REF errors now or is it unimportant?
Find the links to external sources in your Excel file
That’s where PerfectXL can really help. PerfectXL Explore shows you visually which sheets in your workbook are connected to external sources, where the actual links are, and how exactly they are referenced.
This is a visual representation showing which sheets are referencing which external sources. You can drill down by selecting the source to view more information, as well as which cell or object is actually containing the link. In real scenarios this often a cell but it can easily be a graph, a hidden defined name, or even a picture. To view the external connections separately you can also select External Sources from the objects menu on the left side:
You can even go to each individual external source and open exactly where it’s referenced in Excel from Explore!
My link is still broken…
Now that you have access to the source, and where it’s being referenced in Excel, you have all the context you need to make a decision. Whether you decide to remove the connection as it’s irrelevant, or to update the link to refer to the correct source, or you restructure the sheet to no longer need an external link, ultimately having the correct information allows you to build a better spreadsheet!
One tip
One tip we can give you is to centralize your external links as much as possible. Have clear descriptions of what data is being accessed and where in one clear sheet named something like “external connections” this is also where you can place and save any named ranges with external references. Two of our Excel Principles also reference this concept:
