Links in a Blink

Excel data can collaborate with data in other workbooks.

by Donald J. Reynolds

ust as you collaborate with colleagues by picking up the phone or stopping by their office, any cell in Excel can likewise collaborate with some of its “colleagues”—that is, cells in other workbooks. Read how you can create helpful and time-saving links between cells in various workbooks.

AWhat makes the Excel linking function extraordinarily convenient is that once you invest the time to create a connection, you never have to do it again: It functions instantly for the life of the file without further prompting. But while that’s great most of the time, it’s not so convenient when you want to change or break a link. This problem has given Excel links a less-than-favorable reputation, but this article will show how to overcome that problem, demonstrating that the link function deserves more respect.

Despite all the ballyhoo about the wonders of Microsoft’s upgrade to Vista, don’t count on any solution to the link-breaking problem. While the new Excel will be flashier and have new functions, link improvement is not one of them.

DOWN TO BASICS
A link is simply a formula that creates a connection between a cell in one workbook (called the source cell) and a cell in another workbook (called a dependent cell). Once you create a link, the dependent cell will update whenever the source cell changes. Links are created in three ways: the formula method, the paste link method and the direct method.

Suggestion: To see the process in action, and as an aid in understanding the steps, create two Excel files (called workbooks)—SubsidiaryA and Consolidating, as shown in exhibit 1, below. Then change the name of the worksheet (or page name within the workbook) from “Sheet1” to “Budget” in both workbooks.

FORMULA METHOD
To link Sales (D5) in SubsidiaryA to Sales (B5) in Consolidating, enter an equal sign (=) in Consolidating B5; then go to D5 in SubsidiaryA and press Enter (see exhibit 1).

Now any change in SubsidiaryA’s D5 shows instantly in Consolidating’s B5 (see exhibit 2). To see the formula that Excel automatically created, click on Consolidating’s B5 and this will appear in the formula bar:

=[SubsidiaryA.xls]Budget!$D$5

Tip: With this method, a plus sign (+) or a minus sign (-) may be used instead of an equal sign (=) in the first step; Excel will interpret them as equal signs when it creates the formula.

PASTE LINK METHOD
Go to SubsidiaryA’s D6 and click on Edit, Copy. Return to Consolidating’s B6 and again click on Edit, but this time click on Paste Special and then on Paste Link (see screenshot below).

Now, as shown in exhibit 3, below, SubsidiaryA’s cost of sales in D6 is linked to Consolidating’s B6 and the Consolidating workbook should show cost of sales for SubsidiaryA at $675 (exhibit 3).

DIRECT METHOD
In this method you write the link formula and then enter it directly in the dependent workbook (exhibit 4). So, if SubsidiaryA’s total operating expense is $220, enter it in D9. Then type this formula in B8 of the Consolidating workbook:

=[SubsidiaryA.xls]Budget!$D$9

As you can see, the formula must include the source workbook (SubsidiaryA), the source worksheet name (Budget) and the linked cell (D9). Also, the format must include the brackets, exclamation point and dollar signs as shown in exhibit 5.

When you use the first two methods, Excel automatically creates absolute cell references—as shown by the dollar signs in the linked cell notation ($D$9). Linked cells can be copied to other cells, but if you want the reference to be relative, the absolute notation ($) must be removed. To do that, press F2 (the edit key) and then remove them either manually or by pressing F4.

Links also can be included as part of a formula. For example, the following formula divides by 2 the sum of the linked amount plus the value in B15 on the dependent workbook:

=([SubsidiaryA.xls]Budget!$D$9+B15)/2

When the source file is open, only the above formula appears on the dependent workbook formula bar. But when the source file is closed, the displayed formula includes both the drive (F) and directory (Data) of the source file. So, applying the above example, this formula will appear:

='F:\Data\[SubsidiaryA.xls]Budget'!$D$9

Note the addition of two apostrophes in the formula; they’re placed before and after the link location.

BREAKING AND EDITING LINKS
Now that we’ve created links, let’s see how they can be erased or changed. It’s easy to change all links to a source workbook. Click on Edit, Link on the menu bar.

Note: Be aware that the Link button will appear faded (which means it’s not available) if the links in the workbook are connected only to worksheets of the same workbook (Excel file). For the Link function to be available, at least one link must be to a different workbook.

You use a different process if you want to update only a specific link. To see how it works, save and close the Consolidating workbook and save the SubsidiaryA workbook as SubNew.xls. Then reopen the Consolidating workbook and when a message box appears asking whether you want Excel to Update Links, click on Yes.

Links in the Consolidating workbook still will refer to SubsidaryA.xls. To change the link source to SubNew, click on Edit and the Links icon, which opens the Source File box. Click on SubsidiaryA.xls and on the Change Source button. Finally, in the File Directory box, click on SubNew.xls and on OK. Now all of the links are updated to the new source workbook. For example, B5 in the Consolidating workbook now has this formula:

=[SubNew.xls]Budget!$D$5

This method works only if the old and new source files have the same worksheet name—in this case Budget.

In Excel after 2001, delete files another way. Click on Edit and Links and select your workbook and click on the Break Link button.

FINDING LINKS
The process gets less friendly if you want to change selected links, because before you can change them, you’ve got to find them—and it’s that difficulty that soured Excel links’ reputation. However, there are small programs (called add-ins) that can make the job easier. For a list of several, see “Easy Breaks.”

If for some reason you’d rather not download and install an add-in, there are several ways to track down links. One is to search for a specific character common to the link’s formula, such as a bracket or an exclamation point.

Start by evoking the Find and Replace function with Ctrl+F. Click on the Find tab and in the Find What box, type a left-hand bracket (]) or an exclamation point (!) and click on the Find Next button. Excel then will find the first occurrence of the target character, giving you access not only to your target formula, but to any other formulas that inconveniently happen to contain either of those two characters. As a further disincentive, this process must be repeated to find each linked cell.

Another way is to command Excel to display every worksheet formula. To do that, press Ctrl+~ (tilde). Exhibit 6, below, shows a section of a worksheet with all its formulas displayed.

But when a worksheet is large and contains many complex formulas, it’s difficult to track down a particular link. Once a linked cell is located, it can be edited or deleted by the methods described above.

If you’re using an Excel version prior to 2002, there are other equally less-than-convenient methods for deleting links. In one, copy an entire worksheet and, using the Paste Special function, paste it as Values (see screenshot below).

Although this method removes all links quickly and easily, it also changes all formulas—not just the linked ones—to values.

Another unfriendly option is to link the dependent file to itself using Edit, Link. Although this method eliminates the links, it can cause circular references errors.

A final option is to delete the source workbook or move it to a different drive or directory. Then, when you open the dependent workbook and Excel recognizes the absence of the source file and the links, it displays a message box that allows you to recreate the links using a different workbook.

As you can see, although Excel links enhance your use of this spreadsheet tool, the challenging offset is the difficulty in editing or breaking links once they’re created. However, all that can be overcome just by taking the time to install one of the add-ins listed in “Easy Breaks” below.

Donald J. Reynolds, CPA, DBA, is a professor at Calvin College, Grand Rapids, Mich. His e-mail address is djr6@calvin.edu.