It is exceptionally easy to link Excel spreadsheets 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 (without causing REF errors), 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.
Table of contents
YouTube Find cells with external links in excel
Watch, like, subscribe
List all the External Excel links and where they are
At the moment within Excel you need to use the next few steps which are a little bit manual in that you can only do them one at a time. There are some tools and Excel AddIns that do it for you. As this is an important step in any Excel spreadsheet review, it is part of the free Reviewing an Excel spreadsheet course.
As a simple example see below the report that shows where the links are and you can click on the hyperlinks and be taken directly there. Also useful note that the 2 links identified here are very similar but one is v5 and the other v6. That is strange and should be investigated.
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. Have a look at finding External Links with Excel Analyzer and finding errors in Pivots, Charts, Conditional Formats and Data Validation.
Related
Excel asking for a password on a spreadsheet that does not have a password
How to stop Excel from asking to update links
Remove phantom links from Excel spreadsheets