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. Anytime 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:
- Use Ctrl+F to display the Find dialog (Figure 1)
- Type a [ in the Find what box
- Click the Options<< button to expand the dialog
- Make sure the Look in dropdown says Formulas
- Click Find All
Fig. 1
A list of all the external links will appear in the bottom of the dialog. You might need to expand the dialog by dragging the lower-right corner (Figure 2).
Fig. 2
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
The Edit Links dialog offers a Break Link button on the right side of the dialog (Figure 4). You can use this to change external links to their current values.
Fig. 4
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:
- Select any blank cell in the worksheet.
- Select all cells in the worksheet with Ctrl+A.
- Press F5 to display the Go To dialog.
- Click the Special button in the left corner to display the Go To Special dialog.
- Choose Formulas and click OK. You now have all of the formula cells selected.
- Select the Formulas tab in the ribbon.
- Click Trace Precedents to trace the precedents for the first formula cell.
- Press the Enter key to move to the next formula cell.
- Repeat steps 7 and 8 until you have traced precedents for all of the formula cells.
- Look for any cells that have the icons shown in Figure 5. These symbols indicate a formula that links to another spreadsheet or workbook.
- 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
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 or send an e-mail directly to [email protected]. If your question is chosen, you will win a book from CFO and MrExcel.com.