Excel 2007: Manage Workbook Links

By David H. Ringstrom, CPA

Excel users either love workbook links or hate them. Indeed, it can be frustrating when the, “This workbook contains links…” prompt appears unexpectedly. In this article, I'll discuss how to create, locate, and eliminate workbook links. I’ll focus on Excel 2007 first, and then finish with some quick tips for users of earlier versions of Excel.

Link Lowdown

Most Excel formulas involve references to other cells within the same worksheet or perhaps another worksheet within that same workbook. A workbook link follows the same conventions as these Excel formulas, but instead refers to a cell or cells in a separate workbook. Assuming that your workbook links update automatically, changes to the linked cells in the second workbook will flow through when you open or refresh the first workbook. Such a formula might take one of these forms:

• =[Book1]Sheet1!A1
• ='C:\Folder\[Workbook2.xls]Sheet1!A1

These formulas break down into these parts:

• Workbook path: The path to the workbook always precedes the file name. In this case, the workbook path is C:\Folder\. The first formula doesn't include a path because the formula refers to a workbook that hasn't been saved yet.
• Workbook name: The file name, in this case [Book1] or [Workbook2.xls], is always surrounded by square brackets.
• Sheet name: The sheet name, in this case Sheet1, always appears to the right of the closing square bracket, and before the exclamation point.
• Cell reference: The cell reference, in this case A1, always appears to the right of the exclamation point. By way of comparison, a linked formula that refers to several cells might take this form:

=SUM('C:\Folder\[Workbook2.xls]Sheet1'!A1:A10)

Links provide a helpful convenience, but they can also be troublesome:

• A user e-mails a workbook containing links without also sending the linked workbooks. The recipient has no way of refreshing the links.
• The linked workbooks get moved, renamed, or deleted. Any of these actions can cause broken link formulas that either return stale data or error messages, such as #REF!.

Link defense: I typically avoid using links and keep all related data within a single workbook. However, if your situation requires that you use links, be sure to keep the above caveats in mind.

If you want to build a linked formula, you'll often find the process is easier when you can see both workbooks onscreen simultaneously. This technique usually works best when you only have two workbooks open at once, but you can also minimize unneeded open workbooks before you carry out these steps:

1. Choose Arrange All in the Window section of the View ribbon.
2. Choose either Vertical or Horizontal, and then click OK. The two open (or at least the workbooks that aren't minimized) will appear side by side or top and bottom on the screen, as shown in Figure 2. You can now build your linked formulas by pointing and clicking on the cells in the other workbook.

Figure 1: Linked formulas are easier to build when both workbooks are visible.

To Update, Or Not To Update …

As shown in Figure 2, a security warning appears when you open a workbook that contains links. As shown in Figure 3, you can click the Options button and make a choice:

• Help protect me from unknown content (recommended)
• Enable this content

Figure 2: Excel displays a security warning when you open a workbook that contains links.

Figure 3: This window allows you to enable or disable workbook links for the current session.

The first option prevents the links from updating, while the second option enables the links, which means your formulas will update automatically. This prompt allows you to manage links on a global level for your workbook, but you can also manage links on an individual basis:

1. Choose Edit Links in the Manage Connections section of the Data ribbon.

Visual cue: A disabled Edit Links command indicates that your workbook does not contain any links.

2. Choose the link that you wish to update in the source list. Hold down the Ctrl key while you click if you wish to select multiple links, or press Ctrl-A to update all links.
3. Click Update Values.

As shown in Figure 4, the other buttons in the Edit Links dialog box allow other ways for you to manage links within your workbook:

• Change source - Click this button to change the file name and/or location of the linked workbook.
• Open source - Click this button to open the linked workbook.
• Break link - This option converts the linked formulas in the current workbook to static values. Use this button with care, as its use could cause unintended consequences if other users don't know that the formula links have been eliminated.
• Check status - This button updates the Status column of the Source list, so you can tell at a glance if there are any issues with your linked formulas.
• Startup prompt - This button lets you choose how Excel notifies you of links within the currently open workbook:
• Let users choose whether to display the alert or not: This is the default setting, which means the security warning in Figure 1 will always appear.
• Don't display the alert and don't update automatic links: This choice disables the security warning, and prevents links from automatically updating.
• Don't display the alert and update links: This choice refreshes linked formulas automatically without any interaction from the user.

Caveat: Regardless of your choice within the Start-up prompt, broken links may still cause the security warning to appear.

Figure 4: Use the Edit Links dialog box to manage links within your workbooks.

 

Trust Center Settings

A new feature in Excel 2007 is the Trust Center, which you can use to manage various security settings, including workbook links:

1. Click the Office button, and then choose Excel Options.

2. Choose Trust Center, and then Trust Center Settings.

3. Click External Content, and then make a choice within the Workbook Links section:

Enable automatic update for all Workbook Links (not recommended) – Select this choice if you always want workbook links to refresh automatically without displaying the security prompt shown in Figure 1.

Prompt user on automatic update for Workbook links – This default setting causes the security warning shown in Figure 1 to appear whenever you open a workbook that contains links.

Disable automatic update of Workbook Links – This setting disables all linked formulas, as well as the security warning. You can manually update links at any time by following the instructions above regarding the Links button on the Connections section of the Data ribbon.

 

Eliminating Links

Unfortunately, the Edit Links window shown in Figure 4 confirms that your file contains links, but does not tell you which cells or range names contain links. In many cases, the Break Links button will eliminate the links, but the downside is you won't know what cells are being affected. You may then need to do some detective work.

Since every workbook link formula contains a square bracket, try pressing Ctrl-F and search for the [ character. You'll need to do this on each worksheet, unless you group the worksheets first. You can then edit each link formula individually, or press Ctrl-H to replace the file path and workbook name with either a blank — this would eliminate the link and have the formula refer to the sheet name in question within the current workbook — or another path.

Another place to look for hidden links is within the Name Manager. To do so, choose Name Manager from the Defined Names section of the Formula ribbon. As shown in Figure 4, you can click the Refers To column to sort the range names so that any names referencing other workbooks sort to the top. You can then delete or amend each defined name as needed. As with worksheet formulas, linked range names will contain a workbook path and workbook name contained within square brackets.

Other links: In addition to links within formulas and range names, links can reside within database queries. In addition, some users rely on Object Linking and Embedding (OLE) or Dynamic Data Exchange (DDE) links to other documents or resources.

Stubborn links: If your workbook contains links that you can't locate or remove through any other fashion, download and install the Delete Links Wizard. Once you download and expand the compressed file, double-click on the resulting DELLINKS.XLA file. The wizard will appear on the Add-Ins tab of the ribbon, as shown in Figure 5. You can then use the wizard to search for and eradicate links within your workbooks.

Figure 5: The free Delete Links Wizard makes fast work of dispatching unwanted links.

Programming resource: Microsoft Knowledgebase article 126093 contains macros that Visual Basic for Applications developers can use to delete links.

 

Tips for Excel 2003 and Earlier

Many of the techniques I describe above work in Excel 2003 and earlier:

• To arrange two workbooks in the fashion shown in Figure 2, choose Window, Arrange, Vertical or Horizontal, and then click OK.
• To access the Edit Links window shown in Figure 4, choose Edit, and then Links. If the Links command is disabled, then you know your workbook does not contain links.
• To search for links within range names, choose Insert, Name, and then Define. You must then click on each range name individually to ferret out any links hidden within a range name.
• Once installed, the Delete Links Wizard appears at the bottom of the Tools menu.

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.