Hide and link
Q: When I open a file that contains external links, Excel 2007 asks whether I want to update those links. Sometimes Excel is unable to update a link and, in this case, I would like to delete the link formula. However, the dialog box does not indicate the worksheet or cell reference that contains the faulty link. My solution is to scan each worksheet for link formulas in Show Formulas mode, but is there a quicker way to identify link formulas?
A: Clicking the Edit Links tool in the Connections group on the Data tab displays a list of external link sources, which is a quick method to confirm that your workbook contains links. However, this tool does not indicate the specific worksheets or cells, and Excel does not provide an easy way to pinpoint the location of formulas containing external links, but there is a solution. Because external link formulas always include brackets ([ and ]) to encapsulate the source file name (e.g., =SUM([Budget. xls]Annual!C10:C25), you can locate external links by searching for formulas that contain a bracket symbol ([), as follows:
1. On the Home tab, click Find & Select from the Editing group, and then select Find.
2. Click the Options button to display extra options.
3. In the Find what box, enter [ (the bracket symbol).
4. In the Within box, select Workbook.
5. In the Look In box, select Formulas.
6. Click the Find All button.
7. A list of all external link formulas will be displayed at the bottom of the Find and Replace dialog box (as pictured), and clicking on any list entry will move your cursor to that link’s cell location.
