[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 4 of 5)
Wednesday March 30, 2016 @ 04:10 AM
Smashing, thanks
Thursday April 21, 2016 @ 07:54 AM
Thank you very much. your article helped me in resolving the issue
Friday May 13, 2016 @ 03:50 AM
Thanks! Could not figure out why a ink error kept appearing every time the file was opened. #3 helped me track it down!
Wednesday June 8, 2016 @ 11:45 AM
Amazing, this was very helpful
Friday June 10, 2016 @ 02:16 PM
This is great info! Thanks so much! i spend a full day trying to figure out which cell was linked but i couldn't! It was the name manager! you are great!
Tuesday July 12, 2016 @ 02:00 PM
Count me in as thanking you for a solution. But what I don't understand is why this started happening out of the blue. Never had the problem before, not doing anything different. oh well. THANKS!
Monday August 1, 2016 @ 07:32 PM
Thank you for the post. I had to go a little further to sort out my external dependency problem. By using Name Manager I found two tables (Table1 and Table2) which had their definition greyed out and also delete button greyed out. Googling on "excel greyed out table" found instructions to convert a table to a range. After saving the workbook and re-opening the "Update Links" message was gone!
Tuesday September 20, 2016 @ 02:50 PM
Step 3! Thank you. I was really struggling...
Monday February 27, 2017 @ 10:50 AM
Thank you!!! I never thought to look at the name manager and have been confounded for months as to where these "external links" were coming from.
Wednesday May 3, 2017 @ 11:06 AM
NONE of the above worked for me (Excel 2016 Mac)... however, I finally found the solution. It was because of defined names... and some of those had been copied from another spreadsheet. I found the 4 offending links by "Data->DataValidation->Circling Invalid Data". I then copied up the unaffected rows over the offending rows and that cured the problem!
Thursday May 11, 2017 @ 04:23 PM
If you have hidden tabs. Unhide them and then check Name Manager. Names on hidden tabs will not show in Name Manager.
Friday June 9, 2017 @ 08:40 AM
Omg, thanks for your help!! I read so much about looking for links, but first time I heard about name manager and removed all and it worked! thanks!
Tuesday June 13, 2017 @ 02:48 AM
Thank you soooo much, those hidden links were like ghosts that kept slowing me down... finally they are gone!
Thursday August 31, 2017 @ 11:22 AM
Thanks for a solution to a maddening problem that could not be solved using Microsoft's own documentation!
Friday December 29, 2017 @ 01:15 PM
Thanks!3rd point helped me
Tuesday March 6, 2018 @ 11:03 PM
Hello I am at a complete loss at to how to remove a link. I have checked Ctrl F, data validation, conditionals formatting and Name Manager but cannot find anything. Any suggestions.
Thursday April 12, 2018 @ 04:32 AM
Thanks for this. The post and the comment thread led me to look into data validation formulas and that's where I found that bloody external link that I had be searching for months.
Thursday October 4, 2018 @ 01:58 PM
5)Other places to look for broken links
- Data validation lists
- Conditional formatting rules
Friday February 15, 2019 @ 10:22 PM
Perfect update of captcha regignizing software "XRumer 16.0 + XEvil":
captchas regignizing of Google (ReCaptcha-2 and ReCaptcha-3), Facebook, BitFinex, Bing, Hotmail, SolveMedia, Yandex,
and more than 8400 another categories of captchas,
with highest precision (80..100%) and highest speed (100 img per second).
You can use XEvil 4.0 with any most popular SEO/SMM software: iMacros, XRumer, GSA SER, ZennoPoster, Srapebox, Senuke, and more than 100 of other programms.
Interested? You can find a lot of impessive videos about XEvil in YouTube.
FREE DEMO AVAILABLE!
Good luck ;)
Monday March 28, 2016 @ 09:54 PM
ALSO DATA VALIDATION ENTRIES
You should add to this list to look for Data Validation entries. This has caught me out a few times.