19 Brindley Close
Oxford OX2 6XN
England
01865 552731

FINDLINK

Purpose

This popular utility is an Excel add-in which searches a workbook for a specified string (usually representing a link to another workbook) and tells you where in the workbook it occurs, optionally replacing formulas that include the string with their current values.

It can thus be used to find out why you are unexpectedly getting messages like these on opening the workbook
.

and, by replacing the formulas that contain the links with their values it can prevent the message from appearing in future.

The current version is for Excel 2007 or later. For Excel 2003 or earlier please go here

Installation

  • The current version for Excel 2007 and later is 13.2  Download it here
  • Extract FindLink.xlam from the zip file to a folder on your computer
  • Check that Windows has not marked it as a Blocked file. Using Windows Explorer, in the folder where you extracted Findink.xlam, right-click Findlink.xlam > Properties > General and click the Unblock button if it is present
  • You can choose whether to install FindLink as an add-in so that it is available whenever you are running Excel or whether to just open it when you want to use it.
  • To install as an add-in in Excel 2007
    • Office Button > Excel Options > Add-Ins > Manage: Excel Add-Ins > Go > Browse to where you saved FindLink.xlam, Select FindLink.xlam and click OK
  • To install as an add-in in Excel 2010 or later
    • File > Options > Add-Ins > Manage: Excel Add-Ins > Go > Browse to where you saved FindLink.xlam, Select FindLink.xlam and click OK
  • To run it once without installing it as an add-in:
    • In Excel 2007: Office Button > Open > FindLink.xlam
    • In other versions, File > Open > FindLink.xlam
    • If you are warned about the presence of macros in the file, enable the content.
    • If you are offered the opportunity to trust the publisher (William Manville), do so.

Use

  • FindLink adds a "Find Links" command in a BMA group on the Data ribbon in Excel 2010 and later.
    • If it does not appear when FindLink is open, check that FindLink.xlam is not marked as blocked (see above)
  • Click this Find Links commands on the ribbon, and FindLink displays a dialog:

            
  • Usually FindLink is asked to find a particular filename to which there are links in the workbook, or ".xl" to find links to any Excel files.  However you can enter any string in the box and that string will be looked for in places where links might be found.  So you could search for links to Powerpoint files by entering *.ppt* for example.
  • It looks through the active workbook for formulas that include the string specified by the user. It looks in defined names, cell formulas and also in any objects which can be linked to data in cells (e.g. chart series, data labels, textboxes, data validation lists, conditional formatting formulas).
  • There are options to make a list in a worksheet of everywhere that the specified string was found, and/or to remove all occurrences of the string (e.g. by replacing formulas with values) or to give a message for each occurrence, allowing the user to decide on an action:

        
  • Note: If you are looking for links to an installed add-in they will not be found unless you either de-install the add-in or launch Excel in Safe mode (hold down Ctrl key while starting Excel) to prevent the add-infrom loading. If you use Safe mode you would then need to open FINDLINK explicitly.

What's new

  • Version 13.2 will not change Excel calculation to manual if it is the first workbook to be opened
  • Version 13.1 no longer truncates currency-formatted values to 2 decimal places when replacing formula with value, no longer gives an error 7 if the workbook has a huge number of defined names, and no longer shows internal functions as user-defined in the function wizard
  • Version 12.9 no longer gives an error 9 after deleting some defined names
  • Version 12.8 shows the formulas in defined names in the language of the user
  • Version 12.7 shows functions in formulas in the language of the user.
  • Version 12.6 no longer gives an error 7 if the workbook has a huge number of defined names
  • Version 12.5 no longer adds a menu item to the Add-Ins group on the ribbon
  • Version 12.4 deals correctly with macros assigned to hidden grouped shapes
  • Version 12.3 looks in chart series that have been hidden in Excel 2016 or later.
  • Version 12.1 overcomes an issue with chart series that have more than 32767 points
  • Version 12.0 works on Mac Excel 365 as well as on Windows versions from 2007 to 365.  It also finds links in formulas in empty tables which would only be visible when data was added to the table.
  • Version 11.1 fixes a problem where the only instance of the sought link on a worksheet is in a merged cell
  • Version 11.0 fixes a broken link on the Help button (!)
  • Version 10.9 has some minor user interface improvements
  • Version 10.8 finds some links that were previously missed in conditional formatting and it handles large blocks of formulae more efficiently
  • Version 10.2 works when file names contain ' and also when pivottables are connected to external data sources such as PowerQuery
  • Version 10.0 improves the searching of conditional formatting formulas to include ColorScales, DataBars and IconSets
  • Version 9.9a has an updated email address for support and error reporting
  • Version 9.9 finds links in additional conditional formatting formulas (Excel 2007 and later)
  • Version 9.7 works better on Mac versions of Excel
  • Version 9.6 finds links in cells hidden by filtering
  • Version 9.5 handles axis titles better in Excel 2010

Known deficiencies

Due to limitations in the VBA object model, links will not be found in:
  • Titles of secondary axes in charts
  • Sources of custom error bars in charts
  • Chart series that have been hidden in Excel 2016 or later if runnning in Excel 2013 or earlier
  • Rows hidden by advanced filtering