30

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?

3
  • You’re saying “broken link(s)”, but as far as I can tell, you aren’t talking about broken links. What am I missing? Oct 14, 2016 at 4:31
  • This can occur from conditional formatting. I fixed this by going to each tab and clicking Home>Conditional Formatting>Clear Rules>Clear Rules from Entire Sheet. You then have to save, close, and reopen. Upon reopening the links will no longer exist. If you had any desired conditional formatting you'll need to reapply
    – kackleyjm
    Feb 13, 2019 at 3:58
  • If you are looking at finding 404/ Broken external website links within a bunch of Excel Files , have a look at excelurlvalidator.com
    – techno
    Oct 13, 2022 at 6:30

14 Answers 14

22

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.

3
  • 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, 2014 at 16:44
  • 4
    Check for hidden worksheets too.
    – coburne
    Apr 13, 2015 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, 2018 at 21:32
16

I just had this exact problem and found another broken link location not mentioned here which is 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
------> Data Validation... (Settings Dialog)
--------> 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.

0
11

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.

2
  • 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, 2018 at 15:41
  • You just saved me hours of work...nothing else was working. One thing -- I searched . for the name of the linked file. A file name sheet12 referenced the named sheet with the offending code, including this indicating the exact cells that referenced the file: <xm:f><xm:sqref>D45:D144</xm:sqref>
    – wolfmason
    Jan 14, 2020 at 23:21
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!

1
  • Could you include a screenshot of the location of this?
    – Burgi
    May 26, 2016 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.

2

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...

1
  • 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 . Feb 14, 2018 at 11:07
2

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

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

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.

1
  • nice summary. perhaps others could add to it if they find something missing Nov 9, 2020 at 9:40
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.

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).

2
  • 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, 2016 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, 2017 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.
-1

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.

1
  • Please write your concern too,,, that why is been down voted,, help me to improve ! Feb 21, 2020 at 5:47
-1

ASAP Utilities deletes all range names with invalid cell references. $49 for 2 years updates and worth every cent. There is a free home and student version which is functional. ASAP Utilities for Excel - About ASAP Utilities

-2

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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .