How to Find Cells Linked to External Sources in Excel

This tutorial demonstrates how to find external links in Excel.

 

 

Find External Links

In Excel, you can use data from another workbook (file). In that case, Excel creates an external link to that workbook. Consider the data set in the picture below, which contains external links to another workbook.

 

 

As you can see above, the value in B2 is linked to a worksheet called External file.xlsx (Sheet1, cell B2). Cells B5, B7, and B8 also contain similar links. Now look at that file and the value in cell B2.

 

 

Above, you can see that the cell B2 value in the file External file.xlsx is 55, and that value is linked to the initial file. When you link a cell to another workbook, values are updated in both workbooks whenever a linked cell changes. Therefore, you can run into a problem if the file you’ve linked to gets deleted.

Upon opening, the initial file warns you that some of the cells in that workbook are linked to a non-existent file. To solve that problem, you can find all cells with external links and replace them with some other value (or simply delete them).

However, there are often cases where you have many linked cells and it would be time-consuming to go through the whole file to select them one by one. You can find all external links in the file at once by using the Find and Replace or Edit Links features.

Find External Links Using Find and Replace

1. In the Ribbon, go to Home > Find & Select > Replace.

 

 

2. In the pop-up window, (1) enter “*.xl*” for Find what, (2) click Find All, and (3) press CTRL + A on the keyboard to select all cells found.

 

 

The linked file(s) must be in an Excel format (.xlsx, .xlsm, .xls), so you want to find cells containing “.xl” in the formula (link). The asterisks (*) before and after “.xl” represent any character, so the search finds any of the Excel file extensions.

3. As a result, all cells containing an external link (B2, B5, B7, and B8) are selected. To replace them with a certain value, enter that value in the Replace with box and hit Replace All. Leaving the box empty deletes the contents of all linked cells. Entering 55, for example, in the Replace with box yields the Values column in the picture below. Either way, the replaced cells are no longer linked to another workbook.

 

 

Find External Links Using Edit Links

Another option is to use the Edit Links functionality in Excel.

1. In the Ribbon, go to Data > Edit Links.

 

 

2. In the Edit Links window, you can see any workbooks that are linked to the current file. To delete the link, you can select the external file and click Break Link. As a result, all links to this file are deleted, and previously linked cells will contain the values they had at the time of the break.