Professional and collaborative Microsoft Excel workbooks can accumulate references to external workbooks and website links over a long period. Read this ultimate Excel tutorial to learn how to find external links in Excel to efficiently and securely manage all such external links.

You often create long-term dashboards in Excel for sales reports, operations performance reports, student score reports, or lab research reports. To save time, you refer to external workbooks containing reference datasets either created by a collaborator or downloaded from a database application.

Depending on the technique to add such external links, it could be easy or challenging to locate such links. Some external references easily show up in Workbook Links (formerly Edit Links) and some don’t.

When sharing such workbooks with extensively linked datasets, you must also share the reference workbooks or the dashboard won’t work. Also, if you download an Excel workbook from known or unknown sources, you must check the workbook for external links before using it for device and network safety.

Below, I’ve shown different ways to find Excel external links so that none of the references go past your cautious eyes.

Find Excel External Links Using Workbook Links

I’d say you should use the Workbook Links command if you’re wondering how to find external links in Excel.

The tool is available inside the Queries & Connections block of the Data tab in the Excel ribbon. In dated Excel editions, you’ll find Edit Links. That’s the same tool.

So, to locate all external workbook references in the target workbook, open it and press the Alt + A + K keys to open the Workbook Links navigation panel on the right side of the Excel app.

In the navigation panel, you must see a list of all available external workbook links.

This method should show all possible external links except those created using the right-click context menu and by choosing Link > Insert Link.

Find Excel External Links Using Excel 4.0 Macros

First, you need to create a named range with the following formula:

=LINKS()

To do this, call the Name Manager tool by pressing the Alt + M + N keys one by one. The Name Manager dialog shall pop up.

There, click on the New button and create a named range as shown above. Here are the fields you must fill up:

• Name: Listlinks
• Scope: Workbook
• Comment: Describe the function
• Refers to: =LINKS()

Once done, click OK to save the new named range.

Now, go to a blank cell in your source workbook. There, enter the named range nested in the TRANSPOSE formula as shown in the screenshot above. Here’s an example of the formula you can use:

=TRANSPOSE(LISTLINKS)

Hit Enter. Excel shall list all of the accessible external workbooks as a list.

Find Excel External Links Using Name Manager

Open the target workbook where you want to find external Excel file links. Press Ctrl + F3 to open the Name Manager dialog.

Look for Refers to column values as given in the following examples:

• =’C:\Users\Name\Downloads[sales.xlsx]sales_data_sample’!
• ='[sales.xlsx]sales_data_sample’!

These entries indicate that there are external references in the target workbook.

If you wish to remove the references, double-click on an entry, and remove the reference in the Refers to field.

You must replace the Refers to field with an alternative dataset so that the relevant calculations work in the target workbook.

Find Excel External Links Using Find & Replace

You can search and locate various characters in the Find & Replace tool to pinpoint different types of external links in an Excel workbook. Find below the search terms for different types of links:

Find Website Links

Bring up the Find & Replace tool by pressing Ctrl + F on your worksheet.

Type HTTP in the Find what field and click Find All.

The tool shall list all the hyperlinks that have HTTP in its URL.

To instantly highlight one such cell, left-click on any search result and Excel shall navigate the cursor to that cell.

This method shall only work if the cell containing an external link uses the HYPERLINK formula.

If there’s no mention of the HTTP protocol in the Formula bar of Excel, you can’t use the Find & Replace tool to locate external links.

Find Workbook Links

You can simply follow the same steps as mentioned above. Just, replace the search term for Find what with XLS, XLSX, DOCX, DOC, etc., to locate external links in your worksheet.

The above screenshot shows how to find external links in Excel using the XLS search term.

Find above how to locate website URLs linked to a cell using the HYPERLINK formula. The URL has DOCS as in Google Docs and got highlighted.

You must closely look below the Formula column in the Find & Replace tool results to understand what’s the link type, such as an external workbook or website URL.

Find Hyperlinks

Hyperlinks added via the Link option or Ctrl + K hotkey don’t easily show up on an Excel workbook via the Find & Replace tool. That’s because the Formula bar only contains the display text of the URL and not the actual website or external workbook link.

To overcome this bottleneck, you can use the Format search feature of the Find & Replace tool in Excel.

Access the Find & Replace dialog on the target worksheet. Then, click the Format button and select Choose Format From Cell option.

Click on any one of the hyperlinked cells using the dropper cursor. This is ideally the most easily noticed cell containing an external link in your worksheet.

Now, click the Find All button. Excel shall list all the cells with hyperlinking formatting in the results dialog below.

This method only works if the Excel worksheet contains URL links in the conventional format, which is blue font color and underlined font. If someone edits the font formatting of the linked content, you won’t be able to decide which formatting to search for.

Find Excel External Links in Objects

Shapes, pictures, icons, SmartArt, etc., are objects of an Excel worksheet. You can link an external workbook or website URL to these elements in two ways.

The first one is through the right-click menu and by choosing Link from the context menu.

In the second method, you can use the equals symbol to place the external workbook directory address or website URL in single quotes.

Now, if your worksheet has many such objects, manually scrolling down and scrolling right using the mouse or keyboard is bound to waste your time locating these objects.

Instead, press Ctrl + G to bring up the Go To dialog. Click Special on the dialog to convert it to the Go To Special box.

There, select the Objects option and click OK.

Excel shall immediately highlight all the objects in the worksheet.

You can now use Tab on the keyboard to cycle through all the objects one at a time.

For some objects, you should see the external workbook or URL link in the Formula bar.

If you don’t then hover the mouse cursor over an object to see if there are any hyperlinked references.

Find Excel External Links in a PivotTable

You or your colleagues can link external datasets to a PivotTable using any of the following two ways:

• Select a table or range
• Use an external data source

Now, if you’re auditing an Excel worksheet for external references in a PivotTable, click anywhere on the PivotTable you’re investigating.

Now, click on the Change Data Source command button inside the Data block of the PivotTable Analyze tab. This should open the Change PivotTable Data Source dialog.

There, if you see that the Choose Connection button is active and the Use an… option is the selection, then you’ve definitely got an external link in your worksheet for the PivotTable.

Click on Choose Connection to find a detailed explanation of the external workbook or database link.

Alternatively, if the Table/Range field is active and Select a table… is the selection, then you can see the exact external workbook that has been linked to the PivotTable.

Using the above dialog boxes, you can also change the datasets of a PivotTable.

Find Excel External Links in Excel Charts

If your Excel workbook has multiple charts, here’s how you can locate External links on those chart objects:

Find Links in Chart Titles

If there’s an external link in the chart title of an Excel graph, it doesn’t show any different formatting as cells do.

So, you need to click once on the chart title. Then, look at the Formula bar above the column header characters.

If there’s any external link, you should see it as a website URL or a local PC directory.

Find Links in Chart Series

Just like chart titles, chart series can have external references. These references also aren’t easily distinguishable.

You can click on the series bar, line, or graphics and check the Formula bar for external references.

Find Excel External Links Using VBA

The methods you’ve learned so far are mostly manual ways to locate links in Excel. You must perform these methods on all the worksheets to find all the external references. While you’re at it, you can easily forget where are the external links if you don’t note down the details.

What if you want to automatically locate all the external references in your worksheet and list those in the first sheet as an index table or anywhere else? You can do that by creating an Excel VBA macro.

Creating Excel VBA macros requires script writing in VBA programming language. If you don’t know how to write a VBA script or don’t have the time, you can use the scripts mentioned below:

Create a List of External Workbook Links

A VBA macro created using the following script would create a new worksheet in the workbook and list all the external workbooks linked in various worksheets:

Sub ListWorkbookLinks()
  Dim links As Variant
  links = ActiveWorkbook.LinkSources(xlExcelLinks)

  If Not IsEmpty(links) Then
  Sheets.Add
  For i = 1 To UBound(links)
    Cells(i, 1).Value = links(i)
    Next i
  Else
    MsgBox "External links aren't available.", vbInformation, "Find Links"
  End If
End Sub

When you run the VBA macro using the given script, you should see a new worksheet in your workbook showing all of the connected external workbooks.

Create a Table of External Links With Cells

Find below another script that creates an itemized list of external workbooks and hyperlinks in the target workbook in a new worksheet. You’ll find details like worksheet names, cell addresses, external workbook references, and website URLs.

Sub ListExternalReferencesAndHyperlinks()
    Dim ws As Worksheet
    Dim hlink As Hyperlink
    Dim newSheet As Worksheet
    Dim nextRow As Long
    Dim formula As String
    Dim i As Integer

    ' Create a new sheet named "External References"
    On Error Resume Next
    Set newSheet = Sheets("External References")
    If newSheet Is Nothing Then
        Set newSheet = Sheets.Add(After:=Sheets(Sheets.Count))
        newSheet.Name = "External References"
    End If
    On Error GoTo 0

    ' Initialize the next row
    nextRow = 1

    ' Loop through each worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "External References" Then
            ' Loop through each cell with a formula
            For Each cell In ws.UsedRange
                If cell.HasFormula Then
                    formula = cell.Formula
                    ' Check if the formula contains an external reference
                    If InStr(formula, "[") > 0 And InStr(formula, "]") > 0 Then
                        ' List the external reference in the new sheet
                        newSheet.Cells(nextRow, 1).Value = ws.Name
                        newSheet.Cells(nextRow, 2).Value = cell.Address
                        newSheet.Cells(nextRow, 3).Value = formula
                        nextRow = nextRow + 1
                    End If
                End If
            Next cell

            ' Loop through each hyperlink
            For Each hlink In ws.Hyperlinks
                ' List the hyperlink in the new sheet
                newSheet.Cells(nextRow, 1).Value = ws.Name
                newSheet.Cells(nextRow, 2).Value = hlink.Range.Address
                newSheet.Cells(nextRow, 3).Value = hlink.Address
                nextRow = nextRow + 1
            Next hlink
        End If
    Next ws
End Sub
 

Find the result after running this VBA macro in the above screenshot.

To learn how to create a VBA macro using a VBA script, read the following article:

📒 Read More: How To Use The VBA Code You Find Online

Conclusions

These are all the tried and tested methods to find external links in Excel. Depending on the type of referencing, like linked through the hyperlinking tool, formulas, or named ranges, you can choose accordingly. Also, you can easily find external workbooks and URLs in Excel objects by following the relevant methods outlined above.

I’ve also outlined the steps and illustrations for finding out links in Excel objects like Shapes and Charts.

If the article helped you or you’ve got suggestions to improve the Excel tutorial, comment below.