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.

FEATURE

Tackling TCJA changes this tax season

Return preparers must be ready for how the Tax Cuts and Jobs Act has modified many common features of individual and business returns.

PODCAST

Why CPAs can’t wait on automation tools

What do accounting firms waiting on others to develop AI, automation, and data analytics tools have in common with a baseball fan sitting in a stadium filling with water at an exponential rate? The answer could determine your firm’s fate.