Excel 2019 Bible

Part IV: Managing and Analyzing Data

Linking has its downside, however. External reference formulas are somewhat fragile, and

accidentally severing the links that you create is relatively easy. You can prevent this mis-

take if you understand how linking works. Later in the chapter, we discuss some problems

that may arise and how to avoid them. (See “Avoiding Potential Problems with External

Reference Formulas.”)

The website for this book at http://www.wiley.com/go/excel2019bible contains two linked files

that you can use to get a feel for the way linking works. The files are named source.xlsx and

dependent.xlsx. As long as these files remain in the same folder, the links will be maintained.

Creating External Reference Formulas

You can create an external reference formula by using several different techniques:

Type the cell references manually These references may be lengthy because they include

workbook and sheet names and possibly even drive and path information. These references

can also point to workbooks stored on the Internet. The advantage of manually typing the

cell references is that the source workbook doesn’t have to be open. The disadvantage is

that it’s error prone. Mistyping a single character makes the formula return an error (or

possibly return a wrong value from the workbook).

Point to the cell references If the source workbook is open, you can use the standard

pointing techniques to create formulas that use external references.

Paste the links Copy your data to the Clipboard. Then, with the source workbook open,

choose Home ➪ Clipboard ➪ Paste ➪ Paste Link (N). Excel pastes the copied data as exter-

nal reference formulas.

Choose Data ➪ Data Tools ➪ Consolidate For more on this method, see the section

“Consolidating worksheets by using the Consolidate dialog box” later in this chapter.

Understanding link formula syntax

Ideally, you won’t have to enter too many external links manually. But it’s good to know

the structure of links in case you have to troubleshoot a problem. The general syntax for an

external reference formula is as follows:

=[WorkbookName]SheetName!CellAddress

Precede the cell address with the workbook name (in brackets), followed by the worksheet

name and an exclamation point. Here’s an example of a formula that uses cell A1 in the

Sheet1 worksheet of a workbook named Budget.xlsx:

=[Budget.xlsx]Sheet1!A1