Find External Links in an Excel File

Last week, I opened an Excel file, and was surprised to see a Security Warning. Apparently there were external links in the file, and I didn’t want to keep them.

externallinks01

To look for the links, you can use a free add-in – there is a link at the end of this article. Or, go through the file yourself, and try to fix the links.

The Manual Fix

For reasons that I can’t remember I decided to to a manual search for the links. First, I made a backup copy of the file – just in case things went wrong. Not that anything ever goes wrong in Excel!

Then, I opened the file again, clicked the Enable Content button, and started my search for those unwanted links.

Look in the Formulas

The first place I looked was in the workbook’s formulas. Maybe I had copied a formula from another workbook, and it was still linking back to that file. If that file is referenced in a formula, its name is enclosed in square brackets, so I used the Find command to look for those.

When I clicked the Find All button, Excel showed me a list of 18 cells that had square brackets. I clicked on the first item, and it had a reference to another file, so I’ll be able to fix that.

There were other cells with square brackets that aren’t external links, so I ignored those. Excel table references use square brackets too, like this formula:

=[@Amt]*0.07

externallinks02

It might be easier to search for “.xls” now, if the workbook has formatted tables.

Fix the Formulas

The formula that I want to fix is looking up prices in another workbook’s pricing table.

=VLOOKUP(C2,’D:\BlogCtx\TestFiles\[DateAmts.xlsx]Prices’!$B$2:$C$5,2,FALSE)

I wanted to get rid of the links, and change those cells to values, so I followed these steps, to break those links:

  • On the Ribbon, click the Data tab
  • In the Connections group, click Edit Links
  • Select the link that you want to break, and click Break Link.
  • A warning message will appear, so read it carefully – especially the part about making a backup copy. Then, click Break Links if you’re sure that’s what you want to do.

externallinks03

Unfortunately, that didn’t change anything – the links were still in the formula.

Copy and Paste As Values

The cells were all in one column in a table, so I decided to change them to values by copying them, then pasting as values.

That got rid of the link in those cells – now they just contain numbers, not links.

externallinks04

The Edit Links window was still showing a link to that workbook though, so maybe one of the workbook names had been copied too.

Check the Names List

To see then names, click the Formulas tab on the Ribbon, then click Name Manager. In the Refers To column, you might see references to other workbooks. You can delete those names, or change them, so they refer to ranges in the active workbook.

I didn’t need the Product name or LocationList (I hope!), so I deleted them. I’ll check on the LocationList name later.

externallinks05

When I checked the Edit Links window again, the link to the DateAmts.xlsx file was gone. Hooray!

However, there was still a link to another workbook – PartLocDBCombo.xls. I had deleted the LocationList, which referred to that workbook, so where could that link be?

Check the Buttons

Another place that links can hide is in the macros that are assigned to buttons. This workbook had 2 buttons, so I checked those next.

I right-clicked on the first button, and clicked Assign Macro, to see the macro that the button runs. In this example, it was still trying to run the macro in the PartLocDBCombo.xls workbook.

So, I selected a macro in the current workbook, for each button, to get rid of those links.

externallinks06

Close and Reopen the File

The Edit Links window still showed a link to PartLocDBCombo.xls, but I didn’t know of any other places to look. So, I saved the workbook, then closed and reopened it, and that got rid of the “ghost” link.

It can be a slow and frustrating process to remove external links, so be careful what you copy into a workbook!

Use the Find Link Add-in

For an automated way of checking for links, you can download Bill Manville’s free FINDLINK.XLA program, from his website.

After you install the add-in, it will appear as a menu item on the Ribbon’s Add-Ins tab. Click the Find Links command, to open the Link Finder window.

Select a file name from the drop down list, then select one of the options for working with those links.

externallinks07

Links Hidden in Other Places

Read the comments below, to see some other places that people have found links in their Excel files.  For example:

Also, see Rag’s comment with steps for finding error cells in Excel 2013.

______________________

You may also like...

32 Responses

  1. Bill also offers his FindLink on his own website: http://www.manville.org.uk

  2. Maxim Manuel says:

    Yeah, I use to use these tricks to find my links. Work really well. Thank you for the reminder. :-)

  3. Peter Albert says:

    Just another hidden place for links: any Conditional Formatting can contain links to other workbooks, too. These will not be broken – or found by the FindLinks addin…

    • Mark Zabel says:

      Peter,

      Thanks!!! You’re a total lifesaver. I would not have thought about Conditional Formatting. You saved me considerable hair loss!

      -Mark Z

  4. Andreas says:

    Very good pointers! I’ve been looking all over the place and finally found the external link in a name reference. Thank you so much :)

  5. Sam says:

    I managed to find links in the data associated with graphs I had copied and pasted but I cannot find a good way to replace them.
    For links in cell formulas, use Ctrl F, find […link address…] and replace with “”

  6. Ashish says:

    Thanks!!! Found out those links which caused huge delay in opening the excel, just in seconds. It was of great help.

  7. Bob says:

    Don’t forget to look for hidden worksheets that may contain the offending links or references!
    To find them, right click on the tabs at the bottom and select “unhide”.

    • Jim L says:

      Bob, THANK YOU. I tried everything under the sun but could not find why Excel was trying to update external links that I could not find. Turns out there were hidden tabs, not sure how they got there. You saved me from going insane! Thank you!

  8. Isaac says:

    One more possible hiding place: Data validation rules. Had trouble finding it…

  9. Charlie Hall says:

    One more hiding place – when copying table data from one workbook to another, if a list column has formulas and has a calculated column formula, the formulas will likely reference back to the source workbook. If you correct the column formulas (by removing the source workbook name) you will still have the source workbook name reference in the calculated column formula – which of course is impossible to access via VBA and hence impossible for addin based tools to find. You will have to redo the calculated column formula to get rid of the ghost external reference formula.

    –Charlie

  10. Rag says:

    Thanks for the tips on finding hidden cells. After reading and trying all the above, Finally, I deleted this unknown source file which has been appearing under Edit Link in my workbook . This is the steps.
    In Excel 2013
    – File-Info
    -[check for issues] (Inspect Workbook)
    -[check compatibility]
    -[significant loss of functionality],
    select [find] and it takes you to the error cell ,
    need to repeat this process until all the hidden error cells are corrected and the unknown external source file will disappear.

    • Alda says:

      There is a button at the bottom right that says “Copy to New Sheet” – this will categorize the errors for you and print them to a new worksheet, along with links to find the affected cells.

  11. Linda Gray says:

    Rag – Thanks. This works so well and so easy!

  12. Paul says:

    Searching in the formulas is something I did not consider.
    Thanks for your help!!!!

  13. Arthur L says:

    Just used the Add-In to remove several hundred links to a single source. Saved me an hour of manual search and delete.
    Thanks for sharing the utility.

  14. Mine was a chart axis on a chart on a sheet that I copies. I change the series, but not the axis.

  15. GR says:

    Look into conditional formatting formulas and data validation, if there is a link to another workbook, you have to manually delete/edit it. Good luck!

    • Jahid says:

      i have solved similar issue may time but today i spent 2 hours and try all that method above but no solution ,was about to give-up. then you says “data validation” where i was stuck. Thanks everyone.

  16. Ed Carter says:

    Awesome, was giving up hope and then discovered it was in my named ranges! Many thanks!

  17. NJS says:

    Thank you so much! I found some in the named ranges, others in the data validation.

    However, very annoying that there’s not an easy way to find these without going to all these different places. Suggestion to Microsoft! :)

  18. RiverBear says:

    Thank you !

  19. Tim J says:

    ALSO DATA VALIDATION ENTRIES
    You should add to this list to look for Data Validation entries. This has caught me out a few times.

  20. rodney says:

    Probably should be a new thread, but will try here first as it is related to the orig question.
    I have a workbook (call it oldfile.xlsm) with conditional formatting which references another tab ( e.g. call it “Lookups”) in the same oldfile.xlsm workbook.
    When I copy the entire sheet into another workbook, I want the conditional formatting in the new workbook to just refer to the “Lookups” tab in the new workbook. However, just now it converts the ref into e.g. [oldfile.xlsm]Lookups/
    How do I stop it adding in the name of the original workbook when I paste the sheet ?

    • In workbook A, can you name the ranges on the Lookups sheet, then refer to those ranges in the conditional formatting?
      Have ranges with the same names set up in workbook B.
      Then, when you copy the sheet to workbook B, go to the Data tab and click Edit Links
      Click “Change Source”, and select workbook B.
      Then, go to the Formulas tab, and open the Name Manager.
      Delete the worksheet level names on the imported sheet.

  21. Laurence says:

    Thanks so much for the info and the COMMENTS! This is the only site I found this info on – especially Rag’s guide.

  22. Mark P says:

    Just to add something else to the list of strange places to find External Links. I had an external link behind a merged cell.
    F27 & G27 were merged, so only the value in F27 was visible. When I unmerged the cells there was an external link in G27 which was not visible. No idea how it got there.

  23. Rick O says:

    Excellent article. More detailed than any other article I saw before this on. Finally eliminated links when deleting unneeded names in the names list. Thanks. Will keep you information as the first source to go to.

  1. March 29, 2017

    […] Finding External Links in Excel – Contextures Blog. […]

Leave a Reply to Linda Gray Cancel reply

Your email address will not be published. Required fields are marked *