How do I get rid of unwanted links that wont break in Excel?

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?

thilina R

2,9135 gold badges25 silver badges35 bronze badges

asked Feb 21, 2014 at 15:17

3

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.

answered Feb 25, 2014 at 16:39

Madball73Madball73

2,3801 gold badge13 silver badges16 bronze badges

3

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.

answered Jul 6, 2016 at 15:30

Dan GDan G

1611 silver badge2 bronze badges

0

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.

answered Oct 13, 2016 at 14:14

Geoff MGeoff M

1111 silver badge2 bronze badges

2

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!

answered May 26, 2016 at 19:32

1

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.

answered Oct 20, 2015 at 14:21

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

answered Feb 14, 2018 at 9:22

1

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.

answered Oct 11, 2016 at 22:33

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.

answered Apr 19, 2017 at 16:39

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.

answered Mar 29, 2018 at 10:57

PhilHibbsPhilHibbs

1953 silver badges13 bronze badges

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

answered Nov 22, 2016 at 9:52

2

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.

DavidPostill♦

146k75 gold badges340 silver badges374 bronze badges

answered Feb 10, 2017 at 17:50

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.

answered Feb 14, 2018 at 11:22

Rajesh SinhaRajesh Sinha

8,7616 gold badges13 silver badges35 bronze badges

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

answered Aug 31, 2020 at 22:47

PiecevcakePiecevcake

5124 silver badges15 bronze badges

answered May 16, 2016 at 19:22

1

How do I remove unbreakable links in Excel?

How do you completely remove links from Excel?