Found the external link from my excel file but I cannot remove the link

Question

Hey everyone,

When I open a particular excel file I get a security warning message about a workbook link (external link).

So I started investigating by using the Edit Links Dialog

Data Tab >> Queries & Connections Group >> Edit Links button

I break the link which should normally remove and grey out the edit links button but it doesn't work all the time and this time is one of those times. I still get prompts that there are external links in my workbook.

Then I followed this support page about how to find and delete external links https://support.microsoft.com/en-us/office/find-links-external-references-in-a-workbook-fcbf4576-3aab-4029-ba25-54313a532ff1

I started with the find and replace dialog box and searched for the filename shown above in the edit links dialog.

Super, I found the external link. They were hidden in rows that I had reduced.

However, what I don't understand is that there was no formula linking to an external workbook (template.xlsx).

There were only values in those cells. (find in replace looked in formulas, if I changed "look in" to something else, for example "value" the find and replace search wouldn't return anything)

I delete the value in these cells hoping it will do the trick but unfortunately the I still get the security warning prompt about an external workbook external link. When I go to the edit links dialog the 2 external links are still shown despite the fact that I deleted the cells.

Then I followed this support page https://support.microsoft.com/en-us/office/find-links-external-references-in-a-workbook-fcbf4576-3aab-4029-ba25-54313a532ff1 and looked for links in

• Defined names
• objects
• chart titles
• chart data series

I'm running out of ideas, if someone could help it would be greatly appreciated.

Thanks

Answer 1

Hi,

This link should help - Make an Excel Worksheet Very Hidden - Excel University.

Answer 2

Sometimes it is really hard to find and remove the link, try this Add-In.

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

Andreas.

Answer 3

Thank you for your help, really appreciate it.

Here's what findlinks returned.

What does it tell us?

• Column Sheet refers to the external workbook sheet or the current workbook? Because my file doesn't have a tab named "content"
• Object = "range" Does that mean the link is in a chart?
• What does Name & Property refer to?

How can I localize the above element now?

Thank you

Answer 4

Ok it seems like the "content" tab is actually on my excel file.

I've unhid all the tabs but I just read about very hidden tabs via VBA. The external link must be on that very hidden "content" tab

I'm trying to find a tutorial that explains how to unhide very hidden tabs.

If anyone knows an easy way please let me know

Thanks

Answer 5

Thanks.

I unhid the super-hidden tab and I was still not able to find external links somehow.

I deleted the tab altogether and the external link prompt was gone.

I can't explain it but the link is hidden in a cell in that tab but it's not visible to me.

If I copy-pasted the data on that tab to another tab and delete the original tab, the external link prompt would still be there meaning that I was copying that invisible formula to my new tab.

So I decided to copy-paste values but not formulas to a new tab, deleted the original tab and miracle, the external link was gone.

I just had to do a bit of manual work to redo all the formula manually and now the file is working again and without that mysterious external link.

Appreciate everyone's help, thank you!