By: Chris Newman
How To Break External Links
So you’re on a mission to 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 post, I am going to walk you through all the hiding places that external links may be lurking.
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 get to this dialog box by navigating as follows:
Data Tab >> Queries & Connections Group >> Edit 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.
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
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 out. Use 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!
Ref: https://www.thespreadsheetguru.com/blog/find-remove-external-links-from-excel-spreadsheet