This post will explain that how to find all links or external references in your current worksheet or workbook in Excel. How do I find out and list all links and external references in Excel.

When you want to link other workbooks, you need to create an external reference. It may be a very common task in Excel. And the Excel do not provide an automatic way to find all links and external references that are used in your workbook. The below methods you can use to find links.

Find Links using Find and Replace Feature

Any Excel workbook that you have linked to will contain that workbook’s file name with .xlsx/.xls/.xlsm.xlb extension. So you can use the Find and Replace feature to look for all reference with the .xl partial file extension. Just do the following steps:

#1 go to the Home Tab, click Find & Select command under Editing group, then select Find… from the drop down list. The Find and Replace window will appear. Or you can directly press Ctrl+F to launch the Find and Replace dialog.

#2 In the Find what text box, enter .xl

#3 click Options button, select Workbook in the Within drop down list. Choose Formulas in the Look in drop down list.

#4 click Find All. All links and external references are displayed in the list box.

Find Links using Edit Links Option

You can also use the Edit Links option an inbuilt tool to find all links and external references in Excel. Just do the following steps:

#1 go to Data Tab, click Edit Links command under Connections group.

#2 The Edit Links dialog will appear. Then click Break links to convert all linked cells to values.

Find Links using VBA Macro

You can also write an Excel VBA macro to find all links and external references quickly in excel. Just do the following steps:

#1 click on “Visual Basic” command under DEVELOPER Tab.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub FindLinksAndExternalReference()
    Dim wsheet As Worksheet
    Dim dR As Range
    Dim cR As Range
    Dim wCount As Long
    Dim linkS() As String
    On Error Resume Next
    For Each wsheet In Worksheets
        Set dR = wsheet.UsedRange.SpecialCells(xlCellTypeFormulas)
        If dR Is Nothing Then GoTo gNext
        For Each cR In dR
            If InStr(1, cR.Formula, "[") > 0 Then
                wCount = wCount + 1
                ReDim Preserve linkS(1 To 2, 1 To wCount)
                linkS(1, wCount) = cR.Address(, , , True)
                linkS(2, wCount) = "'" & cR.Formula
            End If
        Next
        gNext:
    Next
    If wCount > 0 Then
        Sheets.Add(Sheets(1)).Name = "Links"
        Range("A1").Resize(, 2).Value = Array("Link Location", "External Reference")
        Range("A2").Resize(UBound(linkS, 2), UBound(linkS, 1)).Value = Application.Transpose(linkS)
        Columns("A:B").AutoFit
    End If
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 lets see the result.