How to break an external link that won't break

Anonymous
2012-01-11T18:42:31+00:00

I have studied all the Excel help available and followed the advice, but I have encountered an external link in an Excel 2010 workbook that simply will not break. No matter how many times I select "Break Link," this zombie is always there. I have tried deleting range names that came from the source file, to no avail. I "break the link" in the destination file, save, close the file, reopen it -- and the link is still there. I have tried adjusting the options every which way, no luck.

How do I absolutely, positively, completely and forever, send this link to Davy Jones's locker?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2012-12-04T08:40:04+00:00

Dear Long_John_Silver

I had a similar issue and in addition to Raju S Das' recommendation, I have also checked the data validations for various fields; this is where I found some zombie links that were not required anymore.

Maybe you can find some in your file too and remove them:

  • Select the cells where you expect the zombie data validation (I could narrow it down to one section on a specific worksheet)
  • In the Data Tools section of the Data ribbon, select Data Validation from the Data Validation dropdown
  • Check if there is any list validation with a reference to a linked file location.

Hope this helps.

Best regards,

Roger

Was this answer helpful?

300+ people found this answer helpful.
0 comments

69 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-04-16T10:53:04+00:00
    1. We tried breaking the links manually - luck
    2. We ran this VBA code to force the break - usually works but didn't this time

    Sub BreakLinks()

    'Updateby20140318

    Dim wb As Workbook

    Set wb = Application.ActiveWorkbook

    If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then

    For Each link In wb.LinkSources(xlExcelLinks)

    wb.BreakLink link, xlLinkTypeExcelLinks

    Next link

    End If

    End Sub

    1. Then checked in the Name Manager - empty for the columns/rows that were populated
    2. Then checked Conditional Formatting - empty again
    3. Then checked for Data Validation - also empty

    Repeated steps 3-5 and on step 5 got the warning "The selection contains more than one type of validation.". I clicked OK to erase current settings, repeated step 1 (links still in the Edit Links window); saved the workbook and closed it; opened it again and the links had disappeared!!

    Was this answer helpful?

    8 people found this answer helpful.
    0 comments
  2. Anonymous
    2013-12-09T21:54:16+00:00

    Hey I found another fix. I had an extremely large and complicated excel file.

    I clicked edit links when opening the file, and then changed the source to itself. It actually worked and removed the notification that I needed to update the links.

    I changed the source to a different file before and that one was stuck too.

    After breaking any links possible and changing the last remaining bugged link to itself, my file is back to normal.

    Hopefully that helps someone and you don't have to search every cell.

    (assuming you aren't using the linked file in the first place)

    Was this answer helpful?

    6 people found this answer helpful.
    0 comments
  3. Anonymous
    2013-07-31T12:11:50+00:00

    If you've got excel 2010 especially, then the advice found below specifically Das's advice worked well for me.

    Raju S Das replied on 

    Support Engineer  Community Star

    If the link used a defined name. The name is not automatically removed. You may want to delete the name as well.

    Follow the steps below to check if it helps:

    1. On the Formulas tab, in the Defined Names group, click Name Manager.
    2. In the Name Manager dialog box, click the name that you want to change.
    3. Click the name to select it.
    4. Click Delete. You can also press DELETE.
    5. Click OK to confirm the deletion.

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments
  4. Anonymous
    2012-01-12T05:46:30+00:00

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments