Usually when you want to break or amend a link in Excel you use the Edit Links feature under the data ribbon. You can either re-point the link or choose to break it, at which point the last updated values are left intact.

Sometimes these external links prove to be very resistant to breaking or changing in any way.

The resistant ones may be hidden within data validation ranges, named ranges or chart source data and are a b~**¬! of a job to locate.

I’ve scoured the web for macro ideas to break the links but the ones I’ve found are only as good as the Edit Links feature and that has already failed me.

I have found a solution for breaking resistant links (see answer from Outjet).

It works but is quite a task.

I had a number of spreadsheets that required de-linking so I saved copies of them all in a folder called “Breaklinks”. Here are the instructions for breaking the external links for one workbook.

• Make a copy of your linked spreadsheet.
• Change the file extension to .zip (instead of .xlsx). You will probably need to amend the view options and untick the option that says to hide extensions. You will get a warning that your file may not work if you change the extension – just press OK.

• Double click the file to view in winzip

• Sort in file name order![winzip break links 1] (/assets/images/posts/2015/11/winzip-break-links-1.jpg)

• Highlight and delete all files beginning with external links

• Close winzip
• Amend the file extension to the original .xlsx (or in my case .xlsm)
• Open spreadsheet in Excel - choose to repair sheet

• Save and enjoy an unlinked spreadsheet

The only problem with this technique is that its quite a blunt tool and will remove all external links. I’d quite like to be able to pick and choose, thereby allowing me to remove the problematic links and leave the ones I want. If anyone has a clue how to identify and remove the resistant external links I would appreciate your comments.

**UPDATE** I’m going to answer my own question now. When you get to the “Double click the file to view in winzip” stage. If you sort the files in name order so that all the external link files are together you will see some with the extension .rels If you dble click to open these you will be able to see details of the file it links to. If you go through each of the .rels files you can identify which external links you should delete and which ones you can leave untouched.

So we now have a very precise way to delete external links.