Finding broken links can be tricky if you don't know how they're caused.

In this Excel Tip, I will show you how to recreate a broken link, and give you some insight on how to fix them.

The most common type of broken links are range names..

To recreate a broken link, follow these steps:

1. Insert a name in your worksheet
2. Create a new workbook
3. Save it
4. Cut the named cell from your workbook, and paste it onto the new workbook
5. Save the new workbook
6. Delete the cell from the new workbook
7. go back to your workbook, and press in sequence:
   • ALT then i then n then p, then press ALT and hold it down then press l

Here's a screen shot of what you'll end up with:

Note: the range name now contains a link with #REF in it, and this will cause a broken link.

To remove this broken link, just delete the range name:

1. Press in sequence:
   • ALT then i then n then d
   • Sort by value
   • Select the names with #REF
   • Delete

If that doesn't solve your broken link, the next place to look is in your charts:

1. Select the charts one by one
2. Right click, and choose source data
3. Check for any containing #REF

Download sheet to practise how to Find Broken Links in Excel

Training Video on how to Find Broken Links in Excel: