Find And Break All External Links In Excel File [Guide 2022]

By Chris Newman •  Updated: 09/10/18 •  5 min read
Excel Guide to Breaking External Links

So you're on a mission to break/remove external links from your Excel workbook, huh? Seems like it should be easy, but as you are probably finding out, it sometimes isn't as easy as clicking the "break links" button (unfortunately). 

In this guide, I am going to walk you through all the hiding places where those pesky little external links may be lurking.

Guide Contents

External links in cells are typically the easiest to find and remove. You should always start by using the Edit Links Dialog. You can quickly break links to external Excel files by using the following steps:

  1. Navigate to the Data Tab in the Excel Ribbon
  2. Within the Queries & Connections button group, select the Edit Links Button
  3. Select 1 or more Source Files from the Edit Link Dialog’s Listbox
  4. Click Break Link
  5. When you see a warning message that this action cannot be undone, click the Break Links button
Remove External Links From Cells

When you see the Edit Links dialog appears, you will see a listing of all the external Excel files that are getting data pulled from them. To remove/break the link, simply select the rows you wish to remove and click the Break Link button.

You will get a prompt (shown below) asking if you are sure you want to break the links as this action is irreversible. 

Break External Links In Excel Spreadsheet File

Click Break Links and all your links "should" be broken. In a perfect world, the Edit Links button will be grayed out and all your external links will be removed.

How to find External Links in Excel Spreadsheet Files

However, this is far from a perfect world! Sometimes certain links cannot be broken via the Edit Links dialog. In other cases, you will still get prompts stating that there are external links in your workbook.

If you are still thinking there are external links in your workbook, continue reading on to learn where else those pesky links may be hiding.

VBA Code Solution To Automate This

If you would like to automate this process with a VBA macro, you can check out a small macro I have put together to automatically remove links from your Excel Workbook.

External Links can reside inside any textbox of a Chart object. This includes the:

  • Chart Title
  • Axis Labels
  • Data Labels

Click on each Chart object that could have a formula linked to it and look in the Excel Formula Bar to see if the reference is outside the workbook.

If you have shapes with formulas connected to them, there could be a possibility that the formulas have external links.  You can easily check by clicking on the shape in question and looking at the contents of the Formula Bar.

Remove Shape External Links

If you have a lot of shapes to look through, take the following steps to quickly cycle through all the shapes:

  • Hit the F5 key to open the Go To dialog box
  • Click on Special
  • Only check Objects
  • Click OK.

You will now have all the shapes in the spreadsheet selected. To cycle through each shape, just hit the Tab key and keep your eye on the Formula Bar for any formulas that may appear.

Remove Named Range External Links

The Source Data for a Pivot Table can be linked to an outside file. Follow these steps to check your Pivot Table's Source Data connection.

  1. Select a cell within your Pivot Table
  2. Navigate to the PivotTable Tools Analyze Tab
  3. Click the Change Data Source button
  4. Look inside the Change PivotTable Data Source dialog box and confirm your data is not linked externally
Remove Pivot Table External Links

External Links can reside in Data Validation rules. This can occur as the Source input for a List rule. You can manually search through each of your Data Validation rules within your workbook however, that may be a daunting task if you have a lot of tabs to search through. An easier way is to use the Compatibility Checker to search for you.

Using The Compatibility Checker to Find Data Validation Errors:

  1. Select the File tab
  2. In the Info section, select the Check for Issues drop-down
  3. Select Check Compatibility
  4. In the Compatibility Checker dialog box click the Copy to New Sheet button
  5. You should see a new sheet with all the issues listed. Use the keyboard shortcut ctrl + F to bring up the Find dialog and search for instances of "data validation"
  6. If you have any external links or errors in your data validation rules, you'll find sections on the sheet with hyperlinks taking you to the cells with the data validation that was flagged
  7. Click on each hyperlink and check the data validation rule while the cell range is still selected
  8. If you see any external references in the Source field, you'll most likely want to hit the Clear All button to get rid of the external link
Remove Data Validation External Links

Any Other Areas?

Have you found any other areas in your spreadsheets where External Links were hiding? Let me know in the comments and I'll keep this article updated so we have a nice comprehensive list!

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.