Had a bear of a time finding a bad link to an external file inside an Excel document. As is usually the case, this was a spreadsheet of many tabs, some of which were hidden. There were formulas, Data validation and conditional formatting galore! The perfect place for a broken reference to hide.
So what can you do when you’re being tormented by the broken links notification at startup? Assuming you’re like me and cannot ignore the problem, you immediately set to work figuring out where the bad reference is by looking at some likely cells. Maybe you perform a FIND operation using the name of the missing file. And when you fail at those tasks, you then attempt to remove the reference and simply deal with whatever fallout occurs. Excel is nice enough to offer a way to do this in the form of a ‘break link’ button which usually operates as it’s name would imply. But what do you do when even that button doesn’t work?
You can try using the ideas offered by Microsoft here, but if you want to zero in on the problem a bit quicker, first you need to:
Find the offending Tab
- Assuming an XLSX file, Save a copy of your spreadsheet as a .zip and UnZip it to a folder.
- Search that folder for the name of the broken external link. You will get back sheetN.xml as one of the results. N is the number of the Tab which has the bad reference somewhere on it. (Count over from the leftmost tab in your workbook tab by tab to find that sheet(tab) number. Hidden tabs can count as well, if they fall between the first tab(sheet) and the offending sheet. So you may want to Unhide them temporarily. Alternatively, you can look at the code inside sheetN.xml (from the batch of files you unzipped) using a text editor like Notepad to see if you recognize which Tab in your original workbook it comes from. That worked for me.
- If you look more at the code surrounding the offending external reference, you may be able to determine where on the sheet the issue is and whether it’s a formula, conditional formatting or data validation reference which is messed up. If you cannot figure it out, you may need to rebuild that Tab as a new tab. That worked for me.
This tip was brought to you by the amazing community over at SuperUser. Here is a back link to the post I used to help solve my issue:
Hi, I’m having trouble with this method – I wonder if you can help.
When I unzip the file I cannot find any of the names of the broken links anywhere. In the SuperUser post it mentions the file CONTENTS, but there is no such file when I unzip. I wondered if it was because I was starting with a macro-enabled file so I tried with a .xlsx but had the same result.
I know what some of the culprits are – links in data validation – so I opened the .xml file of a sheet I know to have a problem. I found the part of the code related to the data validation, but there are no file names/paths there, just the formula for the data validation:
[6]List!$A$2:$A$4
I am using Excel 2007 – is that the problem?
Anyway, I’d be very grateful for any advice.
Thanks
Broken links are such a pain. I doubt Excel 2007 is the problem, but I’d keep that on the table just in case. Maybe you can try to open the file with a newer version and save it again, then change that to zip and unzip it to look for the trouble Sheet/s.
If you still cannot find the sheet then you’re basically in the position of having to recreate each sheet you think has bad references, save it, then reopen the file to see if the broken links cleared…that could be time consuming depending on the size of the file. Good luck!