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

  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.

    Find and Replace dialog

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

    Name Manager dialog

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

    Go To > Special dialog

  2. Press the Tab key to move between each of the selected objects, and then look in the formula bar Button image for a reference to another workbook, such as [Budget.xlsx].

    Shape selected to show link name in the Formula bar


Find links used in chart titles
  1. Click the chart title on the chart that you want to check.

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


Find links used in chart data series
  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.

    Select a Series option in Chart options > Format > Current Selection

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


For more information, please visit the Excel help center.

If you have any questions, come by the Help Desk at Hardman & Jacobs Undergraduate Learning Center Room 105, call 646-1840, or email us at help@nmsu.edu.




Keywords:microsoft excel find links external references workbooks   Doc ID:75295
Owner:John B.Group:New Mexico State University
Created:2017-08-04 09:12 MDTUpdated:2017-08-04 09:13 MDT
Sites:New Mexico State University
Feedback:  7   2