Hide and link

BY J. CARLTON COLLINS, CPA

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.

SPONSORED REPORT

In focus: Payroll

Providing payroll services that comply with ever-changing regulations and meet evolving employee and employer demands is no easy task. Paychex's Tom Hammond discusses common payroll considerations for CPA firms.

PODCAST

A closer look at threats to CPA licensure

Legislation in states around the country could put CPA licensure in danger. This episode examines what practitioners need to know to help ensure the profession is protected.