Keeping track of all external references in a workbook can be challenging. This tutorial will teach you a few useful techniques to find links to external sources in Excel formulas, objects and charts and shows how to break external links.
When you want to pull data from one file to another, the fastest way is to refer to the source workbook. Such external links, or external references, are a very common practice in Excel. After completing a particular task, however, you may want to find and probably break those links. Astonishingly, there is no quick way to locate all links in a workbook at once. Depending on exactly where the references are located – in formulas, defined names, objects, or charts – you will have you use different methods.
Working with Excel workbooks often involves using external links to reference data from other workbooks or websites. This allows you to pull in live data from sources while keeping the file sizes of your Excel workbooks smaller
However, too many external links can also cause problems. For example, if the source files get moved or deleted, your workbook will display #REF! errors. Or if you share your workbook with others, the external links may not work on their computer if they don’t have access to the source files.
That’s why it’s important to be able to find, check and manage all external links in your Excel workbooks. In this article, I’ll show you 8 different ways to do this.
1. Use the Edit Links Command
The Edit Links command on the Data tab is the easiest way to view and check all external links in a workbook
To use it:
- Go to the Data tab > Queries & Connections group > Edit Links button.
- The Edit Links dialog box shows a list of all linked files. You can check the file path, update status, etc.
- To break a link, select it and click Break Link. This converts the external data to static values.
However, Edit Links doesn’t show every type of external link, so use the other methods below as well.
2. Search for External Links Using Find & Replace
The Find & Replace tool can quickly search for external links based on parts of the file path. Here are some examples:
- Find “.xlsx” to locate links to other Excel files
- Find “url.com” to find website links
- Find “c:/” to find local file links on your PC
To use Find & Replace:
- Press Ctrl+F > Options button > Look in: Formulas
- In Find what box, enter search term e.g. “.xls”
- Click Find All to see a list of results
This method only works if the link is visible in the cell formula. Other types of links, like linked pictures or charts, cannot be found this way.
3. Scan for Links in Defined Names
Defined names can also contain external links. To check for these:
- Go to Formulas tab > Defined Names > Name Manager.
- Review the Refers To column for links like =[Book1.xlsx]Sheet1!Range
- To remove a link, edit the Refers To path and remove external workbook references.
4. Look for Links in Excel Objects
To check if shapes, images, text boxes or other objects are linked externally:
- Go to Home tab > Find & Select > Go To Special > Objects
- This will select all objects on the active sheet.
- Press Tab to cycle through the selected objects. The external link (if any) will appear in the formula bar.
5. Identify Links in Charts
Charts can be linked to external data as well. To check:
- Select the chart and click on the specific chart element you want to inspect.
- Check the formula bar for any external links to another workbook or website.
6. Find Links in PivotTables
Here’s how to check the data source for a PivotTable:
- Click any cell in the PivotTable and go to Analyze tab > Data > Change Data Source.
- Look at the Table/Range field to see if it refers to another workbook.
7. Use the LinkSources Method in VBA
You can use VBA code to get a list of all external links in a workbook. Paste this macro in a module:
Sub ListLinks() Dim LinkSources As Variant LinkSources = ActiveWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(LinkSources) Then Sheets.Add For i = 1 To UBound(LinkSources) Cells(i, 1) = LinkSources(i) Next i End IfEnd SubRunning this macro will place a list of all external links onto a new sheet.
8. Use the FindLinksInWorkbook Macro
Here’s another VBA macro that gives more details about each external link:
Sub FindLinksInWorkbook() Dim link As Variant Dim links() As String Dim count As Integer Dim i As Integer links = ActiveWorkbook.LinkSources(xlExcelLinks) If UBound(links) > 0 Then Sheets.Add count = 1 For i = 1 To UBound(links) Cells(count, 1) = links(i) Cells(count, 2) = "'" & links(i) & "'" count = count + 1 Next i Else MsgBox "No links found" End If End SubThis macro lists the external links along with the full path in quotes, making it easy to copy and remove the links.
Check Link Status
Once you have a list of all external links in a workbook, it’s important to check their status to make sure they are not broken.
The Edit Links dialog (covered in method 1) shows link status.
- Click Check Status to update. Broken links will show as “Source not found” or “Not updated”.
- Click Break Link to convert external data to static values and remove broken links.
Remove Personal Information from Links
If your links contain file server paths or network drive references with personal information, you may want to remove this before sharing your workbook externally.
The Find & Replace tool can quickly remove your username or other private info from external links that appear in formulas.
Being able to find, check and manage external links is an important Excel skill. Use the methods in this article to take control of external links, remove risks and ensure your workbooks remain reliable and safe to share or distribute to others.
The key steps I recommend are:
- Check Edit Links to view external references
- Scan for links using Find & Replace
- Verify link paths do not expose personal/private data
- Check link status and break any broken links
- Remove confidential info from links before sharing your workbook
Following these steps will help avoid #REF errors, keep your data updating properly and prevent external links from disclosing your personal information unintentionally.
How to find cells with external links in Excel
External links in cells are the most common case. They are also the easiest to find and remove. For this, you can utilize the Excel Find feature:
- In your worksheet, press Ctrl + F to open the Find and Replace dialog.
- Click the Options button.
- In the Find what box, type .xl. This way, you will search for all possible Excel file formats including .xls (older workbooks) .xlsx (modern workbooks) .xlsm (macro-enabled workbooks), etc.
- In the Within box, select either Workbook to search in all tabs or Sheet to look in the current worksheet only.
- In the Look in box, choose Formulas.
- Click the Find All button.
Thats it! Youve got a list of cells that have any external references in them. 
And these useful tips will help you manage the results:
- To select a cell that contains an external link, click the cell address in the Cell
- To group the found links the way you want, click the corresponding column header, for example, Sheet or Formula.
- To select all cells with external references, place the cursor anywhere within the results and press Ctrl + A. This will select both the results in the Find and Replace dialog box and the cells in the workbook.
Note. With Find and Replace you can only identify external links in cells. If youve removed all external references from formulas but Excel still says there are links to external workbooks, then check other possible locations discussed below.
How to find links in Excel named ranges (defined names)
Excel pros often name ranges and individual cells to make their formulas easier to write, read, and understand. Data validation drop-down lists are also easier to create with named ranges, which in turn may refer to outside data. To take care of such cases, check for external links in Excel names:
- On the Formulas tab, in the Defined Names group, click Name Manager or press the Ctrl + F3 key combination.
- In the list of names, check the Refers Tocolumn for external links. References to other workbooks are enclosed in square brackets like [Source_data.xlsx].
How to Find Excel 2010 external Link
How to remove external links in Excel?
External links in cells are the most common case. They are also the easiest to find and remove. For this, you can utilize the Excel Find feature: In your worksheet, press Ctrl + F to open the Find and Replace dialog. Click the Options button. In the Find what box, type .xl.
How do I find external references in Excel?
You can use the Find dialog box to find workbook links in values, notes, and comments, too. Just choose one of those options instead of Formulas in the “Look In” drop-down list. Another common location to have external references in Excel are cells with defined names.
How do I find external links in Excel?
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.
How do I find all external links in a file?
However, there are often cases where you have many linked cells and it would be time-consuming to go through the whole file to select them one by one. You can find all external links in the file at once by using the Find and Replace or Edit Links features. In the Ribbon, go to Home > Find & Select > Replace.
