Tom’s Tutorials For Excel: Finding and Deleting Phantom Links If you’ve ever wondered why this message pops up after you are sure you’ve deleted all your workbook’s links, well, join the club. It’s a common problem because some links can be difficult to find. In my 20 years working with Excel, I have not yet seen a single case when this message appeared and there was not a link; Excel is very good at recognizing an existing link. So, how do you find a link when you have looked throughout your workbook and cannot locate what Excel says is there? Here are some tactics in no particular order for searching and destroying phantom links: Tactic 1 Right-click on any sheet tab, left-click Select All Sheets, press Ctrl+F and in the Find what field enter =! The idea is to look through all sheets for formulas referencing external workbooks, so be sure to unhide any hidden sheets with this tactic.
Tactic 2 Look closely at your list of defined names, not just in the name box but in the Define Name dialog, and make sure you do not have any named ranges refering to an outside Workbook. In Excel version 2003 or before, from the worksheet menu click Insert Name Define.
In version 2007 or 2010, from the Ribbon click the Formulas tab, and in the Defined Names section click Name Manager. Tactic 3 Open a new workbook, create a link to it, and save that workbook. Now go to Edit Links (version 2003) or the Name Manager (version 2007 or 2010), click the Edit button, and use the “Change Source” to refer the link to the new workbook.
Save again and then delete the link you created. Tactic 4 If you have pivot tables or charts, they may contain source data that is derived from outside workbooks and hence being the links culprit.
![For For](/uploads/1/2/5/6/125629857/751078530.png)
Take a look at the chart series and the pivottable source ranges. I had a table in which a whole column had all been linked with an identical formula to an external workbook.
Paste links for data, charts, and formulas in Excel, Word, PowerPoint that update between source and destination files. Paste Links for Data, Charts, and Formulas in Excel, Word, PowerPoint. Note: The information in this article applies to Excel versions 2019, 2016, 2013, 2010, and Excel for Mac. Official direct download links to all Microsoft Office 2016 for Mac editions on Microsoft's own servers. The only Office Trial version currently available is Office 365, which runs on both PC and Mac.
When I had picked up the data I wanted I did a Copy and Paste Values on the whole column. When I came back to it some time later It said on opening that there were external links present and I spent ages trying to find them.
No cell any longer had the formula with the link in it that Find could locate but the Table still remembered the formula which had been there! I tracked it down in the end by adding a row and finding the formula in one of the new cells but there really should be a more intuitive way of finding it.
Better still the Table should not try to remember an old formula when no cell is any longer using it. Then I found this Tutorial which would have saved me a lot of time! Thanks This was on the latest version of Excel for Mac running under High Sierra.