It is exceptionally easy to link Excel spreadsheet together. However. sometimes you want to find these external links in Excel. Not only the link, but also which cells in Excel are using these links. This could be so that you can delete them, remove the link or just better understand what impact the external links are having on your spreadsheet. Below a description of how to find cells with external links in excel.
Edit Links to find the External References in Excel
The first thing to do is find which other spreadsheets are being linked to. This is critical as we need to know the exact name of the external link. This is easy to do in Excel. As shown below, with the file open, click on DATA and then External Links. You will see a dialogue box which tells you the name of every other workbook linked to.
There is much that can be done with this dialogue box, but for now we are trying to find the cells with external links. So looking at the Edit Links box, find the link that you are searching for. Remember or jot down that name. It is best to take the full name including the .xls or whatever at the end, or enough of the name so that it will be unique.
Use FIND to find the external links/ references
Now that you know the precise spelling, close the Edit Links box and open the FIND box.
In the FIND box, input the filename you remember from above. The more precise you can be the better the results.
The reason this works is that by default, the FIND looks into the formula of a cell and not the result (you can change this if you want, but for this purpose it works correctly).
Note below that you can choose to use FIND NEXT or FIND ALL.
FIND NEXT is great if you want to go to each cell individually. FIND ALL will list them all (this is the option we chose below) and you can then work through them.
Note that sometimes when you do the search it will tell you that it can’t find anything, even though it is shown in the External Links. In this case you are going to have to look (manually) into:
- Graphs/ charts
- Named Ranges (very common place)
- Data Validation
- Conditional Formatting.
There are some nice tools out there to help with this process. You can have a look at the FindLink addin