How to find broken links in Excel that can't be broken with 'Break Links'?

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

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?

Solution 1:

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.

Solution 2:

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.

Solution 3:

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.

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

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

Solution 4:

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!

Solution 5:

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.