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.