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.


Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.


How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.


News quiz: Economy and health care changes top CPAs’ list

CPA decision-makers’ economic outlook and the House Republicans’ proposed tax changes as part of replacing the Patient Protection and Affordable Care Act received attention recently. See how much you know with this short quiz.