How To Find External Links and References in Excel3 Simple Methods

Written By:
Lisa Borga
Reviewed By:
FundsNet Staff

External Links, also called external references, in Excel are used sometimes when creating formulas in an Excel workbook , and you refer them to a data point in another workbook.

This workbook is an external link to the formula.

It can be useful to have an external link in a formula because you can update it automatically if the data in the linked workbook is changed.

But, external links have the disadvantage of requiring the linked workbook to be available.

There are three basic types of links used in Excel.

These are links inside the current worksheet, links that are from other worksheets but part of the same workbook, or links that are from another workbook.

Should you delete the linked workbook or change its name or folder location?

The data will not be able to update.

Some links simply link to another location in the current worksheet.

A workbook has many worksheets, and this type of link provides the cell name solely.

As an example, suppose we use cell G3, and in the formula bar, B2 is shown.

These links are in the same workbook but come from different worksheets.

As an example, suppose you have a workbook with three worksheets. We are currently on Sheet one and providing a link from Sheet 3. Links From Another Workbook

This link is from an entirely different workbook and is called an external link.

As an example, we could provide a link from a separate workbook named Workbook A. This link would show the name of the workbook, the sheet name, and the cell name.

There are several methods you can use to find external links in an Excel Workbook.

When you open a worksheet that has external links, you will see a dialog box like the one below before the workbook opens that indicates it has these external links.

Now, we will explain some different ways to find these external links as well as how to edit and remove them.

Find and Replace Method Containing the Operator Symbol

To use this method, the link must contain a link to the referring workbook in its path or URL if an external link exists.

An operator symbol “[“ will appear in all of the links.

Here is how to locate external links using the Find and Replace method.

  • Select the worksheet and press and hold Control and then press F.

  • Next, in the “Find what” box, enter [.

This will show all of the external links in the worksheet in the dialog box. Then, you can select these and convert all of the formulas to values by using the special paste option.

Find and Replace Method With File Extension

A cell that has external references will include a workbook book name along with a workbook type. There are a number of typical file extensions, including .xlsx, .xlsm, .xlb, and .xls.

  • To use this method, Select the worksheet and press and hold Control and then press F.

  • Next, in the “Find what” box, enter .xlsx and choose “Find All.”

This will list all of the external link cells.

If you use this method to find external links, it will not only highlight the external link. You will also be able to edit the link, break, or delete and remove them. In order to do this, you will:

  • Find the “Edit Links” option, which can be found in the “Data” tab.

  • Now, select “Edit Links,” and you will have a number of options from which to choose, including:
  • Update Values: You can use this to update any values that have changed in a linked worksheet.
  • Change Source: Use this for changing the source file.
  • Open Source: You can use this option to instantly open the source file.
  • Break Link: With this option, you can permanently delete a formula, remove an external link from your worksheet, and keep just the values. If you do this, you will not have the option of undoing it.

It is possible to remove all external links and still receive an external link prompt in some cases.

If this happens, there are some steps you can take to find links that the methods we described above will not find.

  • Shapes: If you have used shapes and they are linked to cells, be sure to see if these contain external links. You can do the following to check the shapes.
    • Press the F5 key.
    • In the dialog box, choose “Special.”
    • Next, in the “Go to Special” dialog box, choose “Objects.”
    • Click on “Okay.” Doing this will select the shapes, and you can cycle through them by using Tab.
  • Named Ranges: Find the “Formula tab” and select ”Name manager.” This will list the named ranges found in the workbook. Then, you can look at the “Refers to” column in order to see the external references.
  • Conditional Formatting: You will only find an external link here if there is a custom formula. You can check for this by going to the “Home Tab” and selecting “Conditional Formatting,” then choosing Manage Rules and checking for formulas in the Conditional Formatting Rules Manager.
  • Chart Titles: Go to “Chart Titles” and look in the formula bar to see if it shows an external link.
  • Data Validation: The data validation drop-down list might refer to a named range that has an external link, but if you check named ranges, you should have found these.

Final Thoughts

We have shown you three methods for finding external links, one of which will allow you to edit and remove these links as well.

This will be extremely useful when creating workbooks.