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.


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.