Make sure you have saved the file that you are working in (the one that has the link you need to clear).
From the pull-down menus select Edit\Links.
In the Links pop-up window select Change Source.
The Change Links window will allow you to change the link to another file. Presumably, you will want to link to the same file you are working in so find where you saved it and double click on it. This should clear your links to the other file.
If more than one link shows up in the Links pop-up window you will have to repeat the process.
On occassion, I've had problems clearing links this way. Seems that I wanted to change the link from an external file to the current file and it doesn't let me so I attempt to find the link within the file I'm working to clear it directly. I'm a little weak on this point and have not always had luck finding the link. You can try using the Edit\Find feature and look for the characters [ or ] or ! which typically are part of the link string. If you find it, you can then edit the cell contents as needed.
In some cases, I've been unable to use Find to find the link. Of course it's a little more difficult when you have multiply worksheets that might contain the link though it seems Find can be used across multiple sheets. Sometimes I wonder if links occur in places (other than cells) that I'm not aware of. It will be interesting to see how others work this.
Rats, I tried changing the link to the current file, got this message:
"Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again."
Links can also be contained in NAMES, or defined ranges, styles and sometimes even in deleted graphs, which the methods described above can not detect.
To find the offending links in NAMES, navigate Insert, Names, Define which then shows a window with defined names. Pick each name and see at the bottom what the name refers to. Most of the references will normally be valid references, but sometimes you can see the #REF which indicates that the reference is invalid. This particular name should then be deleted.
To find the offending links in STYLES, navigate Format, Style and then in the Style Name box you can see a style name with a name of another spreadsheet appended. These should be deleted.
To check whether you've found all the bad links, navigate to Edit, Links. If Links is greyed out, it means that you've found all the bad links already. If Links is not greyed out, well, then you use the sledgehammer.
Microsoft has a utility available that identifies bad links in Excel. Go to support.microsoft.com, search the knowledgebase for dellinks.exe and follow the instructions. This utility has always worked for me.
As I mentioned, see if you can find the link using the search feature in Excel and kill it directly in the cell.
Seems like there was another trick I found that would work but it's not too pretty. I may have to get back to you about this as it has been a while since I've had to deal with this. Seems I had to link to another file or copy the file and link to it then try to Edit\Links to clear the link. Maybe someone else will post a more appropriate solution. I would like to know a better way myself.
Didn't realize that Wickus had posted before my last post. I got the dellinks download from MS and the dellinks add-in for Excel seems to work really nice. Makes you wonder why MS didn't include dellinks add-in as part of their standard package for Excel.
Running the add-in will actually find the links you select and replace the link with the current cell value so you will lose any formulas by doing so. If that is not what you want to happen, the add-in does create a copy of your original file unchanged and generates a summary report of the offending links. This is really nice since you don't have to manually search for the bad link. Just look in the dellink report, find where the links are and go from there.
