[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 2 of 5)
Tuesday August 12, 2014 @ 10:56 AM
Thank you so much. The link was in Data Validation and I never would have found it. Thank you for the link to Bill Manville tool
Friday August 15, 2014 @ 05:47 AM
Just found another nasty one - Pivot table source data that was pointing to a linked workbook. Changed the source but it wasn't until I closed (and I guess flushed the pivot cache), and re-opened the workbook, did the link disappear
Monday August 18, 2014 @ 11:04 PM
[at]Bill Manville
AWESOME !! Everything I tried to find and remove links failed in spectacular fashion. Bill's routine worked a treat and in seconds, had resolved my problem.
Tuesday September 23, 2014 @ 11:04 AM
Excellent!!! Thank you for posting.
Thursday October 16, 2014 @ 04:42 PM
Sorry to bump thread but this might be useful:
Delete links in objects, conditional formatting, and names (all of these places can hide). If all else fails, make a copy of the .xlsx, rename to .zip, open, find the external links folder and delete the .xml files in there, then rename to .xlsx
Thursday November 13, 2014 @ 01:30 PM
Really pleased - I had almost given up trying to find 2 lost links. Thank you for the post. I had to close the spreadsheet and re-open it for the fix to stick! Thank you.
Tuesday November 18, 2014 @ 04:36 PM
You have saved me from going mad more than once now. Thanks! I'm glad Google keeps pointing me to your post here.
Thursday December 11, 2014 @ 11:49 AM
Worked perfectly, thank you!
Thursday January 8, 2015 @ 02:08 PM
This utility found an external link to another workbook but when I tried to use this utility to delete this link, it said it could not find the link. However Jared's suggestion about external references in conditional formatting fixed my problem. Deleted all conditional formatting, no more external links. I didn't need the conditional formatting anyway.
Thursday January 8, 2015 @ 02:25 PM
Step 1 solved the problem! Thank you so much!
Tuesday January 13, 2015 @ 09:58 AM
#2 worked for me! They were those darn conditional formatting cells, just 3 of them remaining from a copy+paste from another file. Thanks a bunch!
Tuesday January 13, 2015 @ 11:21 AM
Link was hidden in a validation formula copied from another worksheet. Without Bill's add-in I wouldn't have found it. Thanks!
Thursday January 22, 2015 @ 02:12 AM
Thank you! I really appreciate your help! #4 worked great!
Tuesday February 17, 2015 @ 08:33 PM
Woohoo! Thanks
Monday March 2, 2015 @ 07:34 PM
Couldn't find using Ctrl-F so downloaded add-in. Turns out that links were inside data validation list. Something worth checking next time.
Wednesday March 11, 2015 @ 04:50 AM
Very Helpful. Thank you. Option 3 worked for me
Wednesday April 1, 2015 @ 06:19 AM
The find a link tool saved me! thanks so much
Monday April 13, 2015 @ 12:11 PM
That's useful.
Saturday May 23, 2015 @ 01:01 PM
Thanks a million. Step 4 did it -- my links were hidden in the data validation: select complete worksheet, then data validation and remove all. THANKS AGAIN!!!
Thursday August 7, 2014 @ 07:07 AM
Well done. Really helped!!!