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
a message like this 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 10.2 Download it here
- Extract FindLink.xla from the zip file to a folder on your computer
- 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 2003 or earlier:
- Tools > Add-Ins > Browse to where you saved FindLink.xla, Select FindLink.xla and click OK
- 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.xla, Select FindLink.xla and click OK
- To install as an add-in in Excel 2010 or 2013
- File > Options > Add-Ins > Manage: Excel Add-Ins > Go > Browse to where you saved FindLink.xla, Select FindLink.xla and click OK
- To run it once without installing it as an add-in:
- In Excel 2007: Office Button > Open > FindLink.xla
- In other versions, File > Open > FindLink.xla
- 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 (Bill Manville Associates), do so.
- FindLink adds a "Find Links" menu item to the Tools menu in Excel (up to 2003) or in the Add-Ins ribbon (in 2007 and later)
- Click the Find Links menu item, 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
- It looks through the active workbook for formulas that include a 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 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.
- 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 deficienciesDue 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
- Rows hidden by advanced filtering