How to break all external links in Excel?
Working with Excel workbooks often involves referencing data from other files for analysis, reporting, or data consolidation purposes. These references create external links, which connect your workbook to sources outside the current file. Over time, links can accumulate or become outdated, especially if source files are renamed, moved, deleted, or if you simply want to remove dependencies for sharing or version control. Manually breaking links one-by-one is time-consuming and may lead to errors, especially in large or complex workbooks. Therefore, Excel provides several practical ways—ranging from built-in features, helpful add-ins, to VBA—that let you quickly break some or all external links, ensuring your data integrity and making your files more portable.
Break external links using Excel's "Edit Links" feature
Break all external links with VBA code
Break all / broken / specified external links with Kutools for Excel
Break external links using Excel's "Edit Links" feature (built-in method)
In many Excel scenarios, especially when collaborating across multiple workbooks or cleaning up templates, you may want a quick way to view, update, or break external links. Excel’s built-in Edit Links feature provides this functionality without the need for any VBA, formulas, or add-ins.
Applicable scenarios: This method works best when you want to manage links one-by-one or in bulk, with full transparency about which files are linked. It's particularly useful for users in standard office environments where add-ins or macros are restricted.
To break external links using the built-in feature, follow these steps:
1. Open the workbook containing the external links you want to break.
2. Go to the Data tab on the ribbon.
3. In the Queries & Connections or Connections group, click Edit Links.
4. In the Edit Links dialog box, you’ll see a list of all detected external links (source file names).
5. To break all links, select all source files in the list by holding Ctrl and clicking each one, or clicking the first and last entries while holding Shift.
6. Click Break Link.
7. Confirm your action in the warning dialog that appears.
8. After breaking, the references are replaced with their current values. A quick check is to save, close, and reopen the workbook; the Edit Links button should now be grayed out if all links were successfully broken.
Note:
- Always save a backup of your workbook before breaking links, as this operation is irreversible. After breaking, you cannot restore the link reference; only cell values remain.
- If the Edit Links button is unavailable or some links persist, they may be embedded in defined names, charts, objects, or conditional formatting. In such cases, consider additional cleanup (e.g., check Name Manager, chart data sources, or use VBA/third-party tools).
- After breaking links, reviewing formulas and critical data is recommended to ensure nothing essential was lost or converted incorrectly.
Advantages:
- No programming or add-in installation required.
- Lets you selectively update, check, open source, or break links.
- Comes built-in with Excel (Windows version).
- Some links may not appear (e.g., links in charts, defined names, or objects).
- The feature may be grayed out if no external links are detected.
- Not available in all Excel versions (e.g., some Mac versions).
If you encounter problems where the Edit Links feature doesn't show certain links or cannot break a link, you may need to use other methods (such as the VBA or add-in solutions) below to locate and remove hidden or complex references.
Break all external links with VBA code
If you want to break all detected external links in your workbook—including both valid and broken ones—the following VBA code offers a way to perform this task quickly and in bulk, rather than going through each reference individually. This approach is especially suitable if you have dozens or hundreds of links or if the built-in Edit Links feature does not list all of them.
1. Hold down the ALT + F11 keys together to open the Microsoft Visual Basic for Applications window.
2. In this window, click Insert > Module, then paste the following VBA code into the blank module window:
Sub BreakLinks()
'Updateby20140318
Dim wb As Workbook
Set wb = Application.ActiveWorkbook
If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
For Each link In wb.LinkSources(xlExcelLinks)
wb.BreakLink link, xlLinkTypeExcelLinks
Next link
End If
End Sub3. Press the F5 key or click the Run button to execute the code. The VBA script will iterate through all external links in the active workbook and break them. After completion, check that all links are removed. Save a backup before running to avoid accidental data loss, as this change cannot be undone.
Tips & error reminders:
- VBA solutions can usually remove most standard external link types, but some non-standard or hidden links (such as those embedded in names, shapes, or charts) might require separate handling.
- If you receive an error or a link is not removed, manually check Name Manager (Formulas > Name Manager), charts, or objects for other references.
- Always test VBA actions on a duplicated copy of your file.
Break all / broken / specified external links with Kutools for Excel
The above VBA code enforces the removal of all detected external links from the entire workbook. However, you may only need to target specific links—such as broken, valid, or particular source links. In such circumstances, the VBA approach may not provide the fine control or convenient interface you need.
The Find and Break broken Links feature from Kutools for Excel lets you flexibly scan for external links in the active worksheet, filter by type (all, broken, valid), and select particular items to break. This is ideal for selectively cleaning up links, reducing the risk of data loss from accidental bulk removal.
Kutools for Excel includes over 300 handy Excel tools, available as a free trial for 30 days. Get it Now
After installing Kutools for Excel, please do as follows:
1. Click Kutools > Link > Find and Break broken Links.
2. In the Find and Break broken Links dialog box, all external links in the active worksheet are displayed. You can filter them using the Filter dropdown list according to status (e.g., all, all broken, or all ok links). This allows you to conveniently focus on a specific type of link for batch operations. See screenshots:
 |
 |
 |
3. In this case, choose All broken links. Only the broken links will be listed in the box, as shown:
4. Click the Break link button. All the broken links you selected will be removed from the active worksheet.
Note: If the View cell option is checked, clicking any link in the list will highlight and navigate to the specific cell where that link is found in the worksheet, making it easier to audit or review before deleting.
Click here to know more about Find and Break broken Links feature.
Additional notes and troubleshooting:
- Kutools provides safer link removal compared to manual or VBA methods and is user-friendly for batch tasks.
- Always verify worksheet results after breaking links to confirm that formulas and values appear as expected.
Related article:
How to find and list all links (external references) in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in