Topics Map > Office 365 > Excel
Microsoft Excel - Find links (external references) in a workbook
Linking to other workbooks is a very common task in Excel, but sometimes you might find yourself with a workbook that has links you can’t find even though Excel tells you they exist. There is no automatic way to find all external references that are used in a workbook, however, there are several manual methods you can use to find them. You need to look in formulas, defined names, objects (like text boxes or shapes), chart titles, and chart data series.
Any Excel workbook you’ve linked to will have that workbook’s filename in the link with its .xl* file extension (like .xls, .xlsx, .xlsm), so a recommended method is to look for all references to the .xl partial file extension. If you’re linking to another source, you’ll need to determine the best search term to use.
Find links used in formulas
Press Ctrl+F to launch the Find and Replace dialog.
In the Find what box, enter .xl.
In the Within box, click Workbook.
In the Look in box, click Formulas.
Click Find All.
In the list box that is displayed, look in the Formula column for formulas that contain .xl. In this case, Excel found multiple instances of Budget Master.xlsx.
To select the cell with an external reference, click the cell address link for that row in the list box.
Note: Click any column header to sort the column, and group all of the external references together.
Find links used in defined names
On the Formulas tab, in the Defined Names group, click Name Manager.
Check each entry in the list, and look in the Refers To column for external references. External references contain a reference to another workbook, such as [Budget.xlsx].
- Click any column header to sort the column, and group all of the external references together.
- You can group multiple items with the Shift or Ctrl keys and Left-click if you want to delete multiple items at once.
Find links used in objects, like text boxes or shapes
Press Ctrl+G, the shortcut for the Go To dialog, then click Special > Objects > OK. This will select all objects on the active worksheet.
Press the Tab key to move between each of the selected objects, and then look in the formula bar for a reference to another workbook, such as [Budget.xlsx].
Find links used in chart titles
Click the chart title on the chart that you want to check.
In the formula bar , look for a reference to another workbook, such as [Budget.xls].
Find links used in chart data series
Select the chart that you want to check.
On the Layout tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the data series that you want to check.
In the formula bar , look for a reference to another workbook, such as [Budget.xls] in the SERIES function.