So having said that, here are useful tips for finding and removing external links.
1) The easiest way to remove links in excel is using the "Edit Link" option on the Data ribbon. Here excel will list all the external documents that a spreadsheet is linking to. You can select each file listed then click "break links" and excel will replace the formulas that contain external links with values. The problem with this feature is you will not always know which formulas you are editing and the feature does not work on some links.
2) If you want to view each external link before editing it another option is to search for all external links. This can be achieved by using the "Find and Select" option on the Home ribbon. If you click the "option" button you can select to search the worksheet or the whole workbook. Now in the "find what" box type "[" or ".xls" without quotes.
3) Now if you tried the above and you still have external links this may be due to defined names that exist in workbook. You can see if you have defined names by clicking the "Name Manager" on the Data Manager ribbon. Delete any names here that have errors or that are linking to external sources (make sure you correct any of the formulas using these names first, of course).
4) If you still can't find a link there is a handy tool created by Bill Manville that will find all hidden links and give you the option to delete each. You can download the file here: http://www.oaltd.co.uk/MVP/Default.htm
- Related Articles:
- Too Many Cell Formats in Excel (Resolved)
- Free Microsoft Excel Master Help Provided
- RSS Script for your friends Facebook Status Updates - [Updated 06-28-11]
Reader Comments (Page 1 of 1)
Tuesday November 6, 2012 @ 12:11 PM
Thank you!!! This was driving me CRAZY! I duplicated a template spreadsheet and could NOT break the links. With this info I was able to delete the two names that were pointing to the other spreadsheet and solved the problem. Thanks!
Wednesday November 28, 2012 @ 09:31 AM
Links are great for SQL data source, so agree with Tim. What was getting me was the links in the Name Manager which were not showing up in the Connections for some reason. Good post and thanks for the tip.
Friday November 30, 2012 @ 11:40 AM
Thank you so much! I had a file that had no reason to be linked to anything outside of the file and i don't know how it became linked but using the "Name Manager" i was able to delete the link that i couldn't otherwise find.
Tuesday December 11, 2012 @ 04:57 AM
+1 from me definitely, very helpful as I had gone through all formulae and charts trying to find these links to other files and was close to pulling my hair out!
Thursday December 13, 2012 @ 06:19 AM
At last, a solution that works. This has been bugging me for ages, I have been through dozens of help files, blogs, etc before I found you. You will be on my favourite list from now on.
Monday January 28, 2013 @ 06:52 AM
Great, thanks!
Monday February 11, 2013 @ 02:24 AM
Yippeee its gone. Thankyou so much
Monday April 29, 2013 @ 10:10 AM
Step 4 worked fantastically for me...even Microsoft's own tool (http://support.microsoft.com/default.aspx?kbid=188449) didn't find the link but this link did:
http://www.oaltd.co.uk/MVP/Default.htm
Thank you so much for posting this up.
Tuesday November 8, 2011 @ 04:34 AM
I think "should be avoided at all costs" is a little bit extreme, and simply throws the baby out with the bathwater. I would say "should be carefully planned, and future distribution considered prior to use". Certainly the pitfalls that you have highlighted should be considered prior to any use of external references, and external named ranges are definitely to be avoided. The only time I have used them is in documents that will never need distribution, or in one particular environment where I de-linked using a macro.