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

1. Press Ctrl+F to launch the Find and Replace dialog.

2. Click Options.

3. In the Find what box, enter .xl.

4. In the Within box, click Workbook.

5. In the Look in box, click Formulas.

6. Click Find All.

7. 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.

   

8. To select the cell with an external reference, click the cell address link for that row in the list box.

   Tip: Click any column header to sort the column, and group all of the external references together.

1. On the Formulas tab, in the Defined Names group, click Name Manager.

2. 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].

   

   Tips: 

   • 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.

1. Press Ctrl+G, the shortcut for the Go To dialog, then click Special > Objects > OK. This will select all objects on the active worksheet.

   

2. 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].

   

1. Click the chart title on the chart that you want to check.

2. In the formula bar , look for a reference to another workbook, such as [Budget.xls].

1. Select the chart that you want to check.

2. 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.

   

3. In the formula bar , look for a reference to another workbook, such as [Budget.xls] in the SERIES function.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Fix broken links

Break a link to an external reference in Excel

Control when external references (links) are updated