How to Find External Links and References in Excel

What are External Links or References?

When you create formulas in Excel and refer to a data point in an another workbook, Excel creates a link to that workbook.

So your formula may look like something as shown below:

Note that the part highlighted in yellow is the external link (also called external reference). This part of the formula tells Excel to go to this workbook (Score.xlsx) and refer to the specified cell in the workbook.

The benefit of having an external link in your formula is that you can automatically update it when the data in the linked workbook changes.

However, the drawback is that you always need to have that linked workbook available. If you delete the linked workbook file, change its name, or change its folder location, the data would not update.

If you’re working with a workbook that contains externals links and you have to share it with colleagues/clients, it’s better to remove these external links.

However, if you have a lot of formulas, doing this manually can drive you crazy.

How to Find Externals Links and References in Excel

Here are a couple of techniques you can use to quickly find external links in Excel:

• Using Find and Replace.
• Using Edit Links Option.

Let’s see how each of these techniques work.

Find External Links using Find and Replace

Cells with external links contain the name of the workbook to which it links. This would mean that the reference would have the file name with .xlsx/.xls/.xlsm/.xlb extension.

We can use this to find all the external links.

Here are the steps to find external links in Excel using Find and Replace:

• Select all the cells.
• Go to the Home tab –> Editing –> Find & Select –> Find.
• In the Find and Replace dialog box, enter *.xl* in the ‘Find what’ field.
• Click on Find All.

This will find and show all the cells that have external links in it.

Now you can select all these cells (select the first record, hold the Shift key and then select the last record) and convert the formulas to values.

Find External Links using Edit Links Option

Excel has this inbuilt tool that will find all the externals references.

Here are the steps to find external links using Edit Links Option:

• Go to the Data Tab.
• In the Connections group, click on Edit Links. It opens the Edit Links dialog box will list all the workbooks that are being referenced.
• Click on Break Links to convert all linked cells to values.

Be aware that once you break the links, you can undo it. As a best practice, create a backup before doing this.

Still Getting the External Links Prompt?

Sometimes, you may find and remove all the external links, but still get a prompt as shown below:

Don’t go crazy and start cursing Excel.

So if you’re getting the update links prompt, also check the following for external links:

• Named Ranges
• Conditional Formatting
• Data Validation
• Shapes
• Chart Titles

Using ‘Find and Replace’ or ‘Edit Links’ as shown above would not identify external links in these above-mentioned features.

Here are the steps to find external links in these places:

• Named Ranges: Go to the Formula tab and click on Name Manager. It will show you all the named ranges in the workbook. You can check the ‘Refers to’ column to spot external references.
• Conditional Formatting: The only way an external link can land up in Conditional Formatting is through the custom formula. Go to the Home tab –> Conditional Formatting –> Manage Rules. In the Conditional Formatting Rules Manager, check the formulas for external links.
• Data Validation: It is possible that the data validation drop down list refers to a named range that in turn has external links. Checking Named Ranges should take care of this issue as well.
• Shapes: If you’re using shapes that are linked to cells, check these for external links. Here is a quick way to go through all the shapes:
  • Press the F5 key. It will open the Go to Dialog Box.
  • Click on Special.
  • In the Go To Special dialog box, select Objects.
  • Click OK. This would select all the shapes. Now you can use the Tab key to cycle through these.
• Chart Titles: Select the chart title and check in the formula bar if it refers to an external link.

You can learn more about External Links from these tutorials:

• Finding External Links in Excel – Contextures Blog.
• Finding External Links – Microsoft Excel Support.

There is also an add-in available to find external links in Excel. Click here to learn more and download the add-in.

You May Also Like the Following Excel Tutorials:

• How to Lock Cells in Excel.
• Find and Remove Hyperlinks in Excel.
• Creating Dynamic Hyperlinks.
• Using FIND function in Excel.
• How to Reference Another Sheet or Workbook in Excel
• How to Find Circular Reference in Excel
• Extract URL from Hyperlinks in Excel