SharePoint Online Excel Web Access - links not working In my organisation we have set up several Excel Web Access pages on SharePoint 2010 (on premises), and on these we frequently use the =HYPERLINK formula to single cells in order to navigate around different parts of the workbook. Merged cell links may not work or only work in one area and not others where the text is or is not. Be sure your link is in a unique cell for best results. Using PC, Excel 2016.
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.
Excel Hyperlinks Stopped Working
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
- 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.
Excel Links Not Working In Teams
So we now have a very precise way to delete external links.