16

I have an Excel workbook that complains about broken links every time I open it:

enter image description here

I've tried using 'Find' to search for [. as suggested in Microsoft's guide. I've also tried searching for objects as the guide suggests and turned up nothing.

How can I find this troublesome broken link?

  • You’re saying “broken link(s)”, but as far as I can tell, you aren’t talking about broken links. What am I missing? – Scott Oct 14 '16 at 4:31

13 Answers 13

13

One place that these links can "hide" in is named ranges. Excel is only breaking links inside formulas. To fix named ranges, Go to Formulas->Name Manager and see if you have outside workbooks referenced there. You can shift-click to select a bunch and delete them.

  • Note, the MS page you reference also talks about this, but your link is to farther down the page--you'd have to scroll up to see about Name References. – Madball73 Feb 25 '14 at 16:44
  • 3
    Check for hidden worksheets too. – coburne Apr 13 '15 at 14:08
  • That was exactly my issue. Really thanks for helping me to get rid of this annoying pop-up message. – thanos.a Sep 10 '18 at 21:32
10

I just had this exact problem, and found another broken link location not mentioned here: Data Validation (otherwise known as drop down menus).

Similar to finding "Objects" in the Microsoft support, go under "Home (Tab) -> Editing (Group) -> Find & Select -> Go To Special... -> Data Validation" to find all cells with data validation on the worksheet. Then you will need to more or less check the Data Validation cells one by one, which you do by clicking one of the Data Validation cells, then going under "Data (Tab) -> Data Tools (Group) -> Data Validation -> Settings -> Source." When updating the Source, be sure to check the box "Apply these changes to all other cells with the same settings" to change all sources that are the same.

9

Here's a solution that has been very reliable for me:

  1. Change the file extension of your .xlsx file to .zip
  2. Unzip this zip file in a new folder
  3. Using Explorer, in that folder, search for the file name in the file CONTENTS.

Note: Sometimes the name is written in URL format, e.g., %20 instead of spaces, so you might consider using a simplified string that is found only in the problematic file. I used last word of the filename + .xlsx.

  1. It will probably return sheetN.xml as a result. This is the file corresponding to your problematic sheet, where N is the number of the problematic sheet in the order they are displayed. Open it in a text editor such as Notepad.

  2. Find the search string and look at the code around it; it should point you to some cell references that you can lookup in the problematic sheet.

Note: As pointed out before, it could also be in Conditional Formatting or Data Validation.

  • This helped, thanks! I had to remove hundreds of rouge conditional-formatting rules, some of which contained references to the template used to generate the final xlsx file – jasttim Nov 27 '18 at 15:41
3

After checking objects, formulas, Names, chart titles and data series, I discovered that my external reference was in "Conditional Formatting". However, there was no reference to another workbook, such as [Budget.xls] in any of the fields or conditions. Only afterward was I able to successfully break the link in the "Connections" Group. I hope this helps!

  • Could you include a screenshot of the location of this? – Burgi May 26 '16 at 20:28
2

It's probably simpler than that. The period is the end of the sentence in help. You should just be searching for the open square bracket. In other words, search for:

[

not for

[.

in formulas. The latter will only find a square bracket followed by a period. Excel find doesn't do regular expressions.

1

i found "Dan G" entry was my issue. A validation cell with a link to another sheet. However I had a few hundred to go through, I found using Dan G's search method worked but with a little twist.

Find a validation cell that is valid, select "Home (Tab) -> Editing (Group) -> Find & Select -> Go To Special... -> Data Validation" but select the "Same" radio button under Data Validation to find all cells with the same data validation on the worksheet.

The one that is causing you the issue should stand out as it won't be highlighted/selected.

1

I was having similar external link issues in excel 2016. I have tried various methods but finally succeeded by data validation tab under data tab. I have clicked "Circle Invalid data" and immediately noted two drop down menu was pointing towards the worksheet from where I coped data from. After sourcing the issue, immediately I could update the data within the work book. Problem solved...

  • Before you post your answer proper research is required. Your answer seem baseless, since you have failed to address the basic reasons along with rectifications. Better refer the link How do I write a good answer, superuser.com/help/how-to-answer . – Rajesh S Feb 14 '18 at 11:07
0

To summarize (and to add an answer I just stumbled across), look at:

  • Formulas (search for "[")
  • Name Manager
  • Hidden worksheets
  • Cells having Conditional Formatting
  • Cells having Data Validation
  • Objects
  • Buttons that call macros (i.e. is that macro in THIS workbook?)

On any sheet, the last three can be reached easily by F5 --> "Special..."

Save, close and reopen the file to update the "Edit Link" list, otherwise the links may still appear even if the source has been successfully removed.

The brute force method would make a copy of the workbook, delete every suspicious object, button, and even worksheet, one at a time, and see when the "Edit Link" window shows that the troublesome link is no more.

0

For Excel 2010 or higher, follow these steps.

  1. Click the Options Command.
  2. Then Hit Advanced.

Under section General ,

  1. Click to clear the "Ask to update automatic links" check box.

NB: When the Ask to update automatic links check box is cleared, the links are automatically updated. And no such message appears. This option applies to the current user only and affects every workbook that the current user opens, if Other users of the same workbooks are not affected.

Other method is to Update the links using Commands, follow these steps.

In Excel 2010 or higher

Click Edit Links in the Connections group on the Data tab.

Click Update Values and Click Close.

Hope this Help you.

0

I wrote a quick macro to find a validation rule that links to an excel spreadsheet:

Public Sub FindExtValidation()

    Set s1 = ActiveSheet        
    Set v1 = s1.Cells.SpecialCells(xlCellTypeAllValidation)
    For i = 1 To s1.UsedRange.Rows.Count
        For j = 1 To s1.UsedRange.Columns.Count
            If Not Intersect(v1, s1.Cells(i, j)) Is Nothing Then
                If InStr(s1.Cells(i, j).Validation.Formula1, ".xl") > 0 Then
                    s1.Cells(i, j).Activate
                    Exit Sub
                End If
            End If
valid_error:
        Next
    Next
End Sub

It fails if the current sheet has no validation at all. Not sure how to fix that, don't really care enough to try.

-1

I had an Excel file that when opened displayed a message regarding a missing external link. No such link could be found and various tools- Kutools, FormulaDesk etc did find nothing. Finally- I solved the problem by opening the xlsx file as zip+xml and deleting the folder dealing with external links (if you want more details- ask me).

  • This duplicates another answer and adds no new content. Please don't post an answer unless you actually have something new to contribute. – DavidPostill Nov 22 '16 at 11:04
  • "if you want more details- ask me" is not a useful answer. If you have more details, post them. Otherwise the response is just noise. – shimonyk May 15 '17 at 14:22
-1

I found my zombie links were stashed in the conditional formatting formulas. As soon as i found and removed all, I was able to break the links.

  1. On the Home tab, select the Conditional Formatting tool
  2. At the bottom of the list select manage rules
  3. At the very top is a pull down for each sheet in your workbook, defaulted to the Current Selection. (If nothing is selected then it defaults to the current sheet from which you accessed the conditional formatting tools)
  4. Examine any of the formula based conditions looking for references to outside documents. Delete the ones that are no longer valid.
  5. From the pull down at the top, select the next sheet until you have eliminated all invalid links used in the conditional formatting.
-3

Check out the following link for more ways to find broken links: https://support.office.com/en-us/article/find-external-references-links-in-a-workbook-fcbf4576-3aab-4029-ba25-54313a532ff1#bmfind_external_references_used_in_name

ALSO, broken links can be hiding in conditional formatting rules.

  • 2
    Link-only answers are not helpful. Please read the help center with regard to this community guideline. – Ramhound May 16 '16 at 20:00

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.