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.
You can try any of the following methods to unlock your protected sheet: Way #1. Make a right-click on the Sheet tab. Now from the context menu choose the Unprotect Sheet option. Go to the Review tab on your Excel application menu bar. Now from the Changes group, hit. Hence, Excel would essentially begin to break the links, then get hung up on something related to the named ranges, and give up. It should be obvious that this is extremely different than the usually-reported link breaking issue (links in formulas are all broken; user is unaware of other links). Improve this answer. To break links click DATA RIBBON – (CONNECTIONS sub menu) EDIT LINKS. You will see a list of external links that your current file is linking to. You can click each of the files and then click BREAK LINK. If BREAK LINK is grayed out then go back to step 1 as you must still have a protected sheet in your workbook.
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.
Excel Breaking Links Not Working Today
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.
Best mac for photographers 2017. Crepax valentina. I have found a solution for breaking resistant links (see answer from Outjet).
Coreldraw graphics suite x5 serial number cracker. 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.
Excel Links Will Not Break
- 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
- 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
Remove Broken Links In Excel
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.
Excel Breaking Links Not Working For A
So we now have a very precise way to delete external links.