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: