Excel – How to find broken links in Excel that can’t be broken with ‘Break Links’

Links to external data can exist in

• formulas (visible through Data --> Edit Links)
• named ranges
• macros called by buttons and shapes
• conditional formatting
• pivot tables linked to external sources

Bill Manville (MS MVP) has created a little tool to find them all. Download: http://www.manville.org.uk/software/findlink.htm

It does not find conditional formatting links, though, so you may need to check these manually.

For anyone else that's spent hours combing their file, the problem can also exist if you've copied a data validation range over from another workbook.

To fix it :

Ribbon-->File-->Check for Issues-->Check Compatibility

This will bring up the checker that will tell you if Validation points to an external sheet.

Most importantly, it will tell you which sheet it is on.

Anyway once you know the sheet(s), go to the sheet. Now

Ribbon-->Home-->Down arrow next to Find and Select-->Data Validation.

This will select the cells that have Data Validation applied.

Now

Ribbon-->Data-->Data Validation

and fix the broken reference in the "Source" field, or click "Clear All" if you don't need it.