Finding and removing old links

Written by Peter Albert on . Posted in Advanced user, Uncategorized

Executive summary

Use the small Link Finder add-in to identify and remove links that are hard to find/cannot be broken normally!
(Operating and Installation instructions here, direct download here)

Detailed description

One of the rules for good worksheet hygiene (especially for larger models) is to keep the number of external links low – and remove all old/unnecessary links.

However, that is often much easier said than done as the “Break link” functionality very often fails to do exactly this – and these links can be hidden in a lot of places one would not usually think of (see the list of nasty hiding places below).

In a future post, I’ll look into some of these traps a bit more detailed – but for now, let me just recommend you this small gem: Link Finder, a free add-in that detects a lot of hidden links and allows you to remove them automatically!

You can either run it directly by simply opening the file (click here for direct download) – or follow the instructions on the site to install it permanently in Excel.

Once opened/installed, you’ll find a “Find Links” button in the menu – from there the dialogs are pretty self-explanatory: FindLinks

Potential locations of hidden links

  • Validation formulas for a cell
  • Defined Names/Named Ranges (Really nasty: hidden names with links!)
  • Formulas in Conditional Formatting
  • Buttons/shapes linking to macros from old workbooks
  • Formulas stored in “column formulas” in a table (even if all cells of this column have other formulas/values!)

Trackback from your site.

Peter Albert

Worked 5 years as Consultant and Project Manager at McKinsey & Company and Bain & Company. Now builds Excel, other models and web applications for consultancies and other clients - and provides Excel training to consultants at all skill levels.

Leave a comment