Find external references (links) in a workbook

There is no automatic way to find external references (also called links) that are used in a destination workbook. However, external references use brackets [ ] to enclose the source workbook name when the workbook is closed, for example =SUM([Budget.xls]Annual!C10:C25), so you can use those characters to locate external references.

To find all external references in a destination workbook, you need to look in cells, names, objects (such as a text box or shape), chart titles, and chart data series.

What do you want to do?

Find external references that are used in cells

Find external references that are used in names

Find external references that are used in objects, such as a text box or shape

Find external references that are used in chart titles

Find external references that are used in chart data series

Find external references that are used in cells

  1. Close all source workbooks, and then open the destination workbook.

  2. On the Home tab, in the Editing group, click Find & Select, and then click Find.

    Excel Ribbon Image

    The Find and Replace dialog box is displayed.

  3. Click Options.

  4. In the Find what box, enter [.

  5. In the Within box, click Workbook.

  6. In the Look in box, click Formulas.

  7. Click Find All.

  8. In the list box that is displayed, look in the Formula column for formulas that contain [.

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

  9. To select the cell with an external reference, select the row in the list box.

Top of Page

Find external references that are used in names

  1. Close all source workbooks, and then open the destination workbook.

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

  3. 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.xls].

    Tip   Click the Refers To column header to sort the column and to group all the external references together.

Top of Page

Find external references that are used in objects, such as a text box or shape

  1. Close all source workbooks, and then open the destination workbook.

  2. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.

    Excel Ribbon Image

  3. Click Objects, and then click OK.

  4. Press the TAB key, and then look in the formula bar Button image for a reference to another workbook, such as [Budget.xls]. The name of the object appears in the Name box.

  5. Repeat step 4 for each object in the worksheet.

  6. Repeat steps 2 through 5 for each worksheet in the workbook.

Top of Page

Find external references that are used in chart titles

  1. Close all source workbooks, and then open the destination workbook.

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

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

  4. Repeat steps 2 and 3 for each chart with a chart title in your workbook.

Top of Page

Find external references that are used in chart data series

  1. Close all source workbooks, and then open the destination workbook.

  2. Select the chart that you want to check.

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

    Excel Ribbon Image

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

  5. Repeat step 3 for each data series in the chart.

  6. Repeat steps 2 through 5 for each chart in the workbook.

Top of Page

Applies To: Excel 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language