Find And Break All External Links In Excel File [Guide 2022]
How To Break 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
- Break External Links In Named Ranges
- Break External Links In Pivot Tables
- Break External Links In Data Validation Rules
Removing External Links From Cells
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:
- Navigate to the Data Tab in the Excel Ribbon
- Within the Queries & Connections button group, select the Edit Links Button
- Select 1 or more Source Files from the Edit Link Dialog’s Listbox
- Click Break Link
- When you see a warning message that this action cannot be undone, click the Break Links button
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.
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.
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.
Removing External Links From Charts
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.
Removing External Links From Shapes
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.
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.
Removing External Links From Named Ranges
Removing External Links From Pivot Tables
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.
- Select a cell within your Pivot Table
- Navigate to the PivotTable Tools Analyze Tab
- Click the Change Data Source button
- Look inside the Change PivotTable Data Source dialog box and confirm your data is not linked externally
Removing External Links From Data Validation Rules
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:
- Select the File tab
- In the Info section, select the Check for Issues drop-down
- Select Check Compatibility
- In the Compatibility Checker dialog box click the Copy to New Sheet button
- 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”
- 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
- Click on each hyperlink and check the data validation rule while the cell range is still selected
- 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
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!
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
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.