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:
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).
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).
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.
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:
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.