86. Excel Tips - Find Broken Links
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:
- Insert a name in your worksheet
- Create a new workbook
- Save it
- Cut the named cell from your workbook, and paste it onto the new workbook
- Save the new workbook
- Delete the cell from the new workbook
- 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:
- Press in sequence:
- ALT then i then n then d
- Sort by value
- Select the names with #REF
If that doesn't solve your broken link, the next place to look is in your charts:
- Select the charts one by one
- Right click, and choose source data
- Check for any containing #REF
Training Video on how to Find Broken Links in Excel: