Home

"Ghost links" in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-55129%22%20slang%3D%22en-US%22%3E%22Ghost%20links%22%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-55129%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20link%20in%20my%20excel%20(visible%20in%20data%2Fedit%20links)%20but%20I%20cannot%20find%20in%20which%20cell%20this%20link%20exists.%20I%20have%20already%20tried%20to%20search%20for%20it%20with%20Ctrl%20F%2C%20but%20no%20result.%20If%20I%20try%20to%20relink%2C%20I%20get%20no%20error%20message%2C%20but%20the%20link%20is%20not%20replaced.%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20find%20the%20cell%20that%20contains%20this%20link%3F%3C%2FP%3E%3CP%3EI'd%20rather%20not%20%22break%20the%20link%22%20to%20avoid%20wrong%20data%20in%20my%20file.%20It%20is%20quite%20a%20large%20calculation%20file%2C%20so%20not%20easy%20to%20look%20for%20it%20manually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20any%20input%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewith%20kind%20regards%2C%3C%2FP%3E%3CP%3EWendy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-55129%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-269502%22%20slang%3D%22en-US%22%3ERe%3A%20%22Ghost%20links%22%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-269502%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20was%20the%20issue%20for%20me%20and%20the%20named%20ranges%20seem%20to%20have%20accumulate%20and%20persist%20from%20years%20old%20work.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-61364%22%20slang%3D%22en-US%22%3ERe%3A%20%22Ghost%20links%22%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-61364%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20utility%20that%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3Blinked%20to%20is%20very%20useful%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.manville.org.uk%2Fsoftware%2Ffindlink.htm%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.manville.org.uk%2Fsoftware%2Ffindlink.htm%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-60758%22%20slang%3D%22en-US%22%3ERe%3A%20%22Ghost%20links%22%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-60758%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI've%20experience%20of%20such%20when%20there%20are%20name%20range%20setting%20contain%20reference%20from%26nbsp%3Bother%26nbsp%3Bfile.%20%26nbsp%3BUse%20'Defined%20Names'%20functions%20under%20'Formulas'%20tab%20and%20check%20if%20there%20are%20range%20name%20addressed%20from%20other%20files.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EClaudia%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-56735%22%20slang%3D%22en-US%22%3ERe%3A%20%22Ghost%20links%22%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-56735%22%20slang%3D%22en-US%22%3E%3CP%3EI%20use%20Spreadsheet%20Compare%202016%20from%20Office%202016%20package.%20Quite%20often%20(but%20not%20always)%20it%20helps%3C%2FP%3E%3CP%3E-%20save%20file%20with%20another%20name%3C%2FP%3E%3CP%3E-%20break%20links%20in%20it%3C%2FP%3E%3CP%3E-%20compare%20with%20above%20tool%20initial%20and%20latest%20files%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20shows%20the%20difference%20%26nbsp%3Bcell%20by%20cell%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-55151%22%20slang%3D%22en-US%22%3ERe%3A%20%22Ghost%20links%22%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-55151%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Wendy%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPossible%20ways%20to%20look%20for%20links%20in%20workbooks%3CBR%20%2F%3E-%20Search%20for%20file%20types%20(e.g.%20xls%2C%20xlsx%2C%20xlsm).%3CBR%20%2F%3E-%20Name%20Manager%3CBR%20%2F%3E-%20Conditional%20formatting%20(%26gt%3B%3D%20XL2010)%3CBR%20%2F%3E-%20Data%20validation%20(%26gt%3B%3D%20XL2010)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELinks%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fblog.contextures.com%2Farchives%2F2013%2F11%2F12%2Ffind-external-links-in-an-excel-file%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fblog.contextures.com%2Farchives%2F2013%2F11%2F12%2Ffind-external-links-in-an-excel-file%2F%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22http%3A%2F%2Fwww.exceldashboardtemplates.com%2Fhow-to-fix-security-links-warnings-in-an-excel-spreadsheet%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.exceldashboardtemplates.com%2Fhow-to-fix-security-links-warnings-in-an-excel-spreadsheet%2F%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22http%3A%2F%2Fnhsexcel.com%2Fhow-to-break-resistant-external-links-in-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fnhsexcel.com%2Fhow-to-break-resistant-external-links-in-excel%2F%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22http%3A%2F%2Fsuperuser.com%2Fquestions%2F915270%2Fforce-external-link-in-excel-to-break-if-the-file-cant-be-found%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fsuperuser.com%2Fquestions%2F915270%2Fforce-external-link-in-excel-to-break-if-the-file-cant-be-found%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22http%3A%2F%2Fwww.manville.org.uk%2Fsoftware%2Ffindlink.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.manville.org.uk%2Fsoftware%2Ffindlink.htm%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776211%22%20slang%3D%22en-US%22%3ERe%3A%20%22Ghost%20links%22%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776211%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3Bthank%20you%20very%20much%20for%20referring%20back%20to%20the%20specific%20link%20by%20the%20other%20user%20that%20worked%20for%20you.%26nbsp%3B%20It%20worked%20perfectly%20for%20me%20and%20reduced%20my%20file%20size%20and%20time%20to%20open%2C%20drastically.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20worked%20fine%20on%20my%20files%20that%20were%20listed%20under%20Data%2FEdit%20Links.%26nbsp%3B%20I%20had%20other%20links%20or%20references%20that%20were%20hidden%20and%20the%20only%20way%20the%20add-in%20would%20delete%20them%20is%20if%20when%20opening%20the%20workbook%2C%20I%20had%20to%20ask%20Excel%20to%20try%20to%20open%20each%20file%20since%20it%20would%20prompt%20me%20as%20to%20whether%20I%20wanted%20to%20continue%20with%20these%20links%20or%20not.%26nbsp%3B%20I%20could%20hit%20cancel%20on%20each%20one%20but%20that%20allowed%20me%20to%20record%20the%20file%20names%20from%20the%20pop-up%20so%20that%20I%20could%20then%20tell%20the%20add-in%20to%20delete%20them.%26nbsp%3B%20They%20didn't%20show%20up%20for%20me%20any%20other%20way.%26nbsp%3B%20It%20took%20a%20little%20time%20to%20do%20this%20but%20it's%20worth%20it%20since%20now%20when%20I%20open%2C%20no%20more%20links%20and%20they%20open%20much%20much%20much%20faster%20and%20the%20file%20size%20is%20greatly%20reduced.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20problem%20I%20have%20is%20that%20sometimes%20after%20using%20the%20add-in's%20capability%26nbsp%3Bsuccessfully%20on%20multiple%20files%2C%20even%20after%20I%20close%20and%20reopen%20Excel%2C%20I%20have%20to%20add%20the%20add-in%20back%20in%20by%20retrieving%20it%20from%20my%20folder.%26nbsp%3B%20I%20could%20see%20the%20add-in%20in%20the%26nbsp%3Badd-in%20list%20in%20Excel%20and%20it%20is%20check-marked%2C%20but%20in%20order%20to%20get%20it%20to%20reappear%26nbsp%3Bon%20the%20top%20menu%20in%20Excel%2C%20I%20had%20to%20manually%20retrieve%20it%20again.%26nbsp%3B%20It's%20not%26nbsp%3Ba%20problem%2C%20just%20strange.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Wendy Goeminne
Occasional Visitor

Dear all,

 

I have a link in my excel (visible in data/edit links) but I cannot find in which cell this link exists. I have already tried to search for it with Ctrl F, but no result. If I try to relink, I get no error message, but the link is not replaced. 

How can I find the cell that contains this link?

I'd rather not "break the link" to avoid wrong data in my file. It is quite a large calculation file, so not easy to look for it manually.

 

Many thanks for any input

 

with kind regards,

Wendy

6 Replies

I use Spreadsheet Compare 2016 from Office 2016 package. Quite often (but not always) it helps

- save file with another name

- break links in it

- compare with above tool initial and latest files

 

it shows the difference  cell by cell

Hi

I've experience of such when there are name range setting contain reference from other file.  Use 'Defined Names' functions under 'Formulas' tab and check if there are range name addressed from other files. 

 

Regards

Claudia

This utility that @Detlef Lewin linked to is very useful

 

http://www.manville.org.uk/software/findlink.htm

 

This was the issue for me and the named ranges seem to have accumulate and persist from years old work. 

 

@Wyn Hopkins thank you very much for referring back to the specific link by the other user that worked for you.  It worked perfectly for me and reduced my file size and time to open, drastically. 

 

It worked fine on my files that were listed under Data/Edit Links.  I had other links or references that were hidden and the only way the add-in would delete them is if when opening the workbook, I had to ask Excel to try to open each file since it would prompt me as to whether I wanted to continue with these links or not.  I could hit cancel on each one but that allowed me to record the file names from the pop-up so that I could then tell the add-in to delete them.  They didn't show up for me any other way.  It took a little time to do this but it's worth it since now when I open, no more links and they open much much much faster and the file size is greatly reduced.

 

The only problem I have is that sometimes after using the add-in's capability successfully on multiple files, even after I close and reopen Excel, I have to add the add-in back in by retrieving it from my folder.  I could see the add-in in the add-in list in Excel and it is check-marked, but in order to get it to reappear on the top menu in Excel, I had to manually retrieve it again.  It's not a problem, just strange.

 

Thanks again!