[Tags: guide, excel, accounting]
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 Formulas 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)
- Bank of England provides best historical foreign exchange rates data
- Free Microsoft Excel Master Help Provided
Reader Comments (Page 1 of 5)
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.
Thursday July 4, 2013 @ 11:21 AM
Thanks, I achieved my objective with the step 4.
Thanks
Saturday August 17, 2013 @ 09:23 AM
Hi, Still I could not delete my external link using Name manager. When I select the link, it is showing disable all link. How to solve this problem. Pls help.
Thursday August 22, 2013 @ 09:47 AM
After all else failed for me (Name Manager & Find) your "Edit Link" suggestion did the trick. And I was so close to just starting all over again. Thanks a million!
Monday September 9, 2013 @ 10:59 AM
Thanks, Bill Manville's Find Link add-in is very helpful.
Wednesday October 2, 2013 @ 04:59 PM
Thanks for the help! Sometimes the link to another file is located within Conditional Formatting. Check the formulas in those references as well.
Friday November 8, 2013 @ 12:28 PM
Right on, totally forgot to check Names. Thanks!
Tuesday November 19, 2013 @ 10:58 AM
I will burn a candle in your name at the Excel altar.
Thank you TJ.
Monday January 6, 2014 @ 04:35 AM
Thank you so much! It was crazy trying to figure out what went wrong as my files are so big for hours!
Monday January 27, 2014 @ 11:52 PM
Ditto for me. Thanks for the tip!
Wednesday February 19, 2014 @ 06:37 AM
Don't forget about objects (shapes etc.), these can contain links too and can be completely hidden. You can use the "Find & Select" option: "Selection Pane" or "Select Objects" to help find them.
Wednesday May 28, 2014 @ 07:44 PM
Awesome explanation bro.
may you be rewarded with better things and blessings...
Keep rocking..
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.