cfo.com

Print this article | Return to Article | Return to CFO.com

Desperately Seeking Spreadsheet Links in Excel

Are you trying to figure out if something in your spreadsheet is linked to another spreadsheet? The trick is to look for a –left square bracket,– says Bill Jelen.
Bill Jelen, CFO.com | US
October 26, 2011

Cindy T. wins a copy of Excel Outside the Box from CFO and MrExcel.com for her question, "Is there a way to find out if anything in a spreadsheet is linked to another spreadsheet or workbook?"

Finding Links to Other Workbooks
It is actually easier to detect if something in a worksheet is linked to another workbook, than to figure out if workbooks are linked. Any time that you have a formula that points to another workbook, you will see a formula that roughly follows this syntax: =[WorkbookName.xlsx]SheetName!A1. Thus, the trick is to look for a left square bracket. To do that, follow these steps:

1.       Use Ctrl+F to display the Find dialog
2.       Type a [ in the Find What box
3.       Click the Options>>> button to expand the dialog
4.       Make sure that the Look In dropdown says Formulas (Figure 1)
5.       Click Find All

Fig. 1

MrExcel 1110 26 Fig1

A list of all of the external links will appear in the bottom of the dialog. You might need to expand the dialog by dragging the lower right corner.

Fig. 2

MrExcel 1110 26 Fig2

While this list is displayed, you can click any row in the bottom of the dialog to jump to the cell.

Breaking External Links
When your workbook has external links, you can use the Edit Links icon on the Data tab (Figure 3).

Fig. 3

MrExcel 1110 26 Fig3

The Edit Links dialog offers a Break Link button on the right side of the dialog. You can use this to change external links to their current values.

Fig. 4

MrExcel 1110 26 Fig4

Finding Links to Other Worksheets in the Same Workbook

You could follow a similar set of steps, this time looking for an exclamation point. However, this method is not perfect, as a link to a named range on another worksheet will not have an exclamation point in the formula.

Check to see if you have any named ranges in the workbook by using Formulas, Name Manager. If there are a lot of named ranges in the list, you can follow this set of steps:
1.  Select any blank cell in the worksheet
2.  Select all cells in the worksheet with Ctrl+A
3.  Press F5 to display the Go To dialog.
4.  Click the Special button in the left corner to display the Go To Special dialog
5.  Choose Formulas and click OK. You now have all of the formula cells selected.
6.  Select the Formulas tab in the ribbon.
7.  Click Trace Precedents to trace the precedents for the first formula cell.
8.   Press the Enter key to move to the next formula cell.
9.   Repeat steps 7 & 8 until you have traced precedents for all of the formula cells.
10. Look for any cells that have the icons shown in Figure 5. These symbols indicate a formula that links to another spreadsheet or workbook.
11. You can double-click any of the arrows (not the sheet icons, but the arrows leading to the sheet icons) to jump to the off-sheet precedent.

Fig. 5

MrExcel 1110 26 Fig5

After you are done, click Remove All Arrows to remove the formula auditing arrows.

Bill Jelen is the host of MrExcel.com. He is the publisher of the newly available Excel Outside the Box, offering 54 amazing Excel techniques. Submit questions for future articles using the link in the byline for this article. If your question is selected you will win a book from CFO and MrExcel.com




CFO Publishing Corporation 2009. All rights reserved.