How to find external links in excel

Answer

An external link is a cell formula, named range, chart, query, or object that references data located in a different workbook, external file, or data source.

Why find external links?

Finding external links prevents broken references, speeds workbook loading, and ensures data integrity when sharing or archiving files.

Where do external links commonly hide?

1. Formulas on worksheets.

2. Named ranges in Name Manager.

3. Data connections and Power Query queries.

4. Charts, shapes, text boxes, and pivot tables.

5. Conditional formatting rules.

6. Data validation lists.

7. Objects linked by OLE (embedded files) or external images.

8. VBA code.

How to find external links in Excel?

A direct answer: Use Excel’s built-in tools plus targeted searches (Find, Name Manager, Edit Links, Data Connections, and VBA) to locate all external references. Follow the numbered procedures below.

1. Use the Edit Links dialog (fast check) — Windows only

1. Open the workbook.

2. On the Ribbon select Data → Queries & Connections → Edit Links.

3. Review the list of source workbooks shown in the dialog.

4. Select a source to Open Source, Change Source, or Break Link.

Note: The Edit Links command appears only when Excel detects linkable external references. Mac Excel may not show this dialog in older versions.

2. Search worksheet formulas (works on Windows and Mac)

1. Press Ctrl+F on Windows or Cmd+F on Mac.

2. Click Options.

3. In Find what enter [ (left square bracket).

4. Set Within to Workbook.

5. Click Find All.

Explanation: External workbook references include the workbook name inside square brackets (for example, [Sales.xlsx]Sheet1!A1). The search finds formulas containing [, which is a reliable indicator.

3. Inspect Name Manager for hidden links

1. On the Ribbon select Formulas → Name Manager.

2. Sort or scan the Refers to column for [ or paths like C:\ or \\server\.

3. Edit or delete any named range that points externally.

4. Check Data Connections and Power Query

1. Select Data → Queries & Connections.

2. In the Queries & Connections pane right-click each query → Edit to view the source.

3. Select Data → Connections to view legacy connections and their sources.

5. Search for conditional formatting and data validation

1. For conditional formatting: Home → Conditional Formatting → Manage Rules → set Show formatting rules for: to This Workbook and inspect rules for [ or external paths.

2. For data validation: Select a sheet, press F5 → Special → Data Validation → All. Inspect each rule for formulas referencing external workbooks.

6. Search objects, charts, shapes, and text boxes

1. Press F5 → Special → Objects and press OK.

2. Click each object and, for formulas (for example, in linked chart series), check the formula bar for [ or a path.

3. For charts: right-click the chart → Select Data → inspect series formulas.

7. Search within VBA

1. Press Alt+F11 to open the VBA Editor.

2. Press Ctrl+F and search the entire project for [ or file path fragments like .xls, .xlsx, C:\, \\, or http.

3. Edit code to remove or update external references.

8. Use a VBA macro to list all external links (comprehensive)

Use the macro below to generate a worksheet that lists found external references across formulas, names, shapes, and links.

Sub ListExternalLinks()

    Dim wsOut As Worksheet

    Dim wb As Workbook

    Dim sh As Worksheet

    Dim nm As Name

    Dim c As Range

    Dim found As Range

    Dim r As Long

    Dim formula As String

    Dim obj As OLEObject

    Dim shp As Shape

    Set wb = ThisWorkbook

    On Error Resume Next

    Application.ScreenUpdating = False

    ' Create output sheet

    On Error Resume Next

    Set wsOut = wb.Worksheets("ExternalLinks_Found")

    If Not wsOut Is Nothing Then wsOut.Delete

    On Error GoTo 0

    Set wsOut = wb.Worksheets.Add

    wsOut.Name = "ExternalLinks_Found"

    r = 1

    wsOut.Range("A1:D1").Value = Array("Location","Type","Address/Name","Formula or Source")

    r = 2

    ' 1. Formulas in workbook

    For Each sh In wb.Worksheets

        On Error Resume Next

        Set found = sh.Cells.Find(What:="[", LookIn:=xlFormulas, LookAt:=xlPart)

        If Not found Is Nothing Then

            firstAddress = found.Address

            Do

                wsOut.Cells(r, 1).Value = sh.Name & "!" & found.Address

                wsOut.Cells(r, 2).Value = "Formula"

                wsOut.Cells(r, 3).Value = found.Address

                wsOut.Cells(r, 4).Value = found.Formula

                r = r + 1

                Set found = sh.Cells.FindNext(found)

            Loop While Not found Is Nothing And found.Address <> firstAddress

        End If

        On Error GoTo 0

    Next sh

    ' 2. Named ranges

    For Each nm In wb.Names

        If InStr(1, nm.Formula, "[") > 0 Or InStr(1, nm.RefersTo, "://") > 0 Then

            wsOut.Cells(r, 1).Value = "Name Manager"

            wsOut.Cells(r, 2).Value = "Named Range"

            wsOut.Cells(r, 3).Value = nm.Name

            wsOut.Cells(r, 4).Value = nm.RefersTo

            r = r + 1

        End If

    Next nm

    ' 3. OLEObjects and Shapes

    For Each sh In wb.Worksheets

        For Each obj In sh.OLEObjects

            If InStr(1, obj.progID, "Linked") > 0 Or InStr(1, obj.Object, "[") > 0 Then

                wsOut.Cells(r, 1).Value = sh.Name

                wsOut.Cells(r, 2).Value = "OLE Object"

                wsOut.Cells(r, 3).Value = obj.Name

                wsOut.Cells(r, 4).Value = "Possibly linked"

                r = r + 1

            End If

        Next obj

        For Each shp In sh.Shapes

            On Error Resume Next

            formula = shp.LinkFormat.SourceFullName

            If Err.Number = 0 Then

                If Len(formula) > 0 Then

                    wsOut.Cells(r, 1).Value = sh.Name

                    wsOut.Cells(r, 2).Value = "Shape"

                    wsOut.Cells(r, 3).Value = shp.Name

                    wsOut.Cells(r, 4).Value = formula

                    r = r + 1

                End If

            End If

            Err.Clear

            On Error GoTo 0

        Next shp

    Next sh

    ' 4. Workbook Links (Edit Links)

    Dim links As Variant

    links = wb.LinkSources(xlExcelLinks)

    If Not IsEmpty(links) Then

        Dim i As Long

        For i = LBound(links) To UBound(links)

            wsOut.Cells(r, 1).Value = "Workbook"

            wsOut.Cells(r, 2).Value = "LinkSource"

            wsOut.Cells(r, 3).Value = links(i)

            wsOut.Cells(r, 4).Value = "From LinkSources"

            r = r + 1

        Next i

    End If

    Application.ScreenUpdating = True

    MsgBox "Scan complete. See sheet: " & wsOut.Name, vbInformation

End Sub

1. Press Alt+F11.

2. Insert a new Module.

3. Paste the macro.

4. Run ListExternalLinks.

5. Open the created sheet ExternalLinks_Found.

9. Use Replace to find hard-coded paths

1. Press Ctrl+H.

2. In Find what enter C:\ or \\ or .xlsx.

3. Set Within to Workbook.

4. Click Find All.

5. Inspect each result before modifying.

How to remove or break external links

A direct answer: Use Edit Links to break links where possible, replace formulas with values, edit named ranges, update queries to local data, and remove or relink objects.

1. Use Data → Edit Links → select link → Break Link to convert formulas to their current values.

2. Replace formulas with values: select range → Ctrl+C → Paste Special → Values.

3. Edit or delete named ranges that contain external references via Formulas → Name Manager.

4. For Power Query: Data → Queries & Connections → right-click query → Edit → update source.

5. For charts or objects: edit series formulas or object link properties to remove external paths.

6. In VBA: remove or update code lines that open or reference other workbooks.

Caution: Breaking links converts formulas to values permanently. Save a backup before breaking links.

What is the difference between Mac versus Windows?

1. The Edit Links dialog may be missing or limited on older Mac versions. Use search and Name Manager instead.

2. VBA behaves similarly across platforms, but ActiveX controls and some OLE features are unavailable on Mac.

3. Power Query availability on Mac is more limited; recent Mac versions include improved support.

4. Keyboard shortcuts differ: use Cmd instead of Ctrl on Mac.

How to verify you removed every external link

1. Repeat the worksheet formula search for [ across the workbook.

2. Check Name Manager for [ or external paths.

3. Inspect Queries & Connections and Connections.

4. Run the VBA macro again to confirm that ExternalLinks_Found is empty or contains only expected items.

How to prevent external links from going forward

1. Use structured data tables and internal references.

2. Use Power Query parameters rather than hard-coding full file paths.

3. Convert shared data into a controlled central workbook on a shared drive and reference it consistently.

4. Use relative paths where appropriate and supported.

5. Keep named ranges and formulas checked before distributing files.

Common pitfalls and troubleshooting

1. Links in hidden sheets remain searchable. Unhide all sheets before final checks.

2. Links in conditional formatting and data validation need manual inspection.

3. External links inside chart series might not appear in simple Find results. Inspect charts directly.

4. Linked images and OLE objects sometimes show links only in object properties. Check shapes and OLEObjects on each sheet.

5. Excel can cache old link sources. Close and reopen the workbook after breaking links to confirm.

Quick checklist — run this before sharing a workbook

1. Search workbook for [ using Find.

2. Open Formulas → Name Manager and review all names.

3. Open Data → Edit Links and inspect listed sources.

4. Review Queries & Connections and Connections.

5. Use the provided VBA macro to compile a comprehensive list.

6. Save a backup, then break or fix links as needed.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report