Advanced Excel users often utilize external links or workbook connections to save data or referencing. External links can be hard to detect in a complicated spreadsheet, or a workbook you’re not familiar with. Finding these connections manually one by one can be a daunting task. Fortunately, there are several ways to identify these references to identify and manipulate such connections.

Mainly, there are 4 ways to find external links. In this tutorial, we’re going to cover each method and you can decide for yourself which one to use in your daily Excel tasks.

You will see a warning dialog like the one below when you open a workbook with external connections. Fear not! This is just Excel’s way of asking you whether it should check again for these connections. You can simply press Update to continue.

Let’s get started! You can download our sample workbook here.

Method 1: Find and Replace Dialog

Excel encloses external references, such as file paths or URLs, in long brackets “[“ and “]”. Therefore, looking up brackets in your file will lead you to external links.

Start by pressing Ctrl+F to open the Find and Replace dialog. Then, simply enter “[“ or “]” (without the quotation marks) to search. Next, click Options and under Within, select Workbook to apply your search to the entire workbook. Make sure that Formulas is selected for the Look in option and then press Find All to initiate search.

The results will be shown in the same window. In this table, you will find detailed information about external links. The column Formula shows the target of the references.

Note that the Find and Replace feature will also give you the strings that actually contain brackets. You should double check the results to verify.

Method 2: Edit Links Feature

The second method is a bit more direct and you won’t get other results in your search query as in the Find method. Edit Links feature can be used to find, edit, or delete links in a workbook. This method is quite powerful as it allows manipulating the references.

First, go to the Data tab on the Excel ribbon. Then, click Edit Links to open the Edit Links window. Source files will be shown here.

Select the source workbook to see its location and do many other things such as,

updating values from Update Values
changing the source file from Change Source…
open the source file from Open Source
terminate the connection with the source file from Break Link (note that this action replaces reference with static values)
check source file status from Check Status

Method 3: By VBA Macro

Another way to find references is by using good old VBA. You can write a simple macro to list source files linked into your workbook. Given the flexibility of VBA, advanced users might even want to add this macro into the toolbar and call it when necessary. The code user in our example essentially creates a new sheet, and prints all existing sources into that page.

First, go to the View tab on the menu ribbon, and press Macros. Select View Macros and enter a macro name such as “Find_Links”. Make sure you name the macro in accordance with VBA macro naming convention (https://en.wikibooks.org/wiki/Visual_Basic/Coding_Standards#Naming_Standards) When you’re done, press Create. Excel will run the Module Window.

Dim wb As Workbook

Dim lnks As Variant, lnk As Variant

Dim i As Integer

Set wb = Application.ActiveWorkbook

lnks = wb.LinkSources(xlExcelLinks)

i = 1

If Not IsEmpty(lnks) Then

wb.Sheets.Add

For Each lnk In lnks

Application.ActiveSheet.Cells(i, 1).Value = lnk

i = i + 1

Next lnk

End If

Paste the code below between Sub Find_Links() and End Sub

Press F5 or the Play button in VBA window to run the macro and get the list of source files on a separate sheet.

Method 4: By Delete Links Wizard Add-in

Last method involves using an Excel add-in developed by Microsoft. It’s worth noting that this add-in will only allow you to delete external references. You can download the add-in here.