Create an external reference (link) to a cell range in another workbook

You can refer to the contents of cells in another workbook by creating an external reference. An external reference (also called a link) is a reference to a cell or range on a worksheet in another Excel workbook, or a reference to a defined name in another workbook. You can refer to the specific cell range, a defined name for the cell range, or define a name for the external reference.

What do you want to do?


Learn more about external references

Although external references are similar to cell references, there are important differences. You use external references when working with large amounts of data or complex formulas that are spread across several workbooks. You create them in a different way and they appear differently in a cell or in the formula bar.

Where external references can be used effectively

External references are especially useful when it is not practical to keep large worksheet models together in the same workbook.

Ways to create external references

If you use a cell reference to create an external reference, you can then also apply formulas to the information. By switching between the various types of cell reference, you can also control which cells are linked to if you move the external reference. For example, if you use a relative reference, when you move the external reference, the cell or cells that it links to change to reflect its new position on the worksheet.

When you create an external reference from one workbook to another workbook, you should use a name to refer to the cells that you are linking to. You can create an external reference by using a name that is already defined, or you can define the name when you create the external reference. By using a name, it is easier to remember the contents of the cells that you are linking to. External references that use defined names do not change when you move them because a name refers to a specific cell or range of cells. If you want an external reference that uses a defined name to change when you move it, you can change the name that is used in the external reference, or you can change the cells that the name refers to.

What an external reference to another workbook looks like

Formulas with external references to other workbooks are displayed in two ways, depending on whether the source workbook — the one workbook that supplies data to a formula — is open or closed.

When the source is open, the external reference includes the workbook name in square brackets ([ ]), followed by the worksheet name, an exclamation point (!), and the cells that the formula depends on. For example, the following formula adds the cells C10:C25 from the workbook named Budget.xls.

External reference
=SUM([Budget.xlsx]Annual!C10:C25)

When the source is not open, the external reference includes the entire path.

External reference
=SUM('C:\Reports\[Budget.xlsx]Annual'!C10:C25)

 Note   If the name of the other worksheet or workbook contains nonalphabetical characters, you must enclose the name (or the path) within single quotation marks.

Formulas that link to a defined name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) in another workbook use the workbook name followed by an exclamation point (!) and the name. For example, the following formula adds the cells in the range named Sales from the workbook named Budget.xlsx.

External reference
=SUM(Budget.xlsx!Sales)

Top of Page Top of Page

Create an external reference between cells in different workbooks

  1. Open the workbook that will contain the external reference (the destination (destination file: The file that a linked or embedded object is inserted into. The source file contains the information that is used to create the object. When you change information in a destination file, the information is not updated in the source file.) workbook) and the workbook that contains the data that you want to link to (the source workbook).
  2. In the source workbook, click Save Button image on the Quick Access Toolbar.
  3. Select the cell or cells in which you want to create the external reference.
  4. Type = (equal sign). If you want to perform calculations or functions on the external reference value, type the operator (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.) or function that you want to precede the external reference.
  5. Switch to the source workbook, and then click the worksheet that contains the cells that you want to link to.
  6. Select the cell or cells that you want to link to.
  7. Return to the destination workbook, and notice that Excel adds the reference to the source workbook and the cells that you selected in the previous step.
  8. Optionally, in the destination worksheet, edit or change the formula.
  9. Press CTRL+SHIFT+ENTER.

Top of Page Top of Page

Create an external reference to a defined name in another workbook

  1. Open the workbook that will contain the external reference (the destination workbook) and the workbook that contains the data that you want to link to (the source workbook).
  2. In the source workbook, click Save Button image on the Quick Access Toolbar.
  3. Select the cell or cells in which you want to create the external reference.
  4. Type = (equal sign). If you want to perform calculations or functions on the external reference's value, type the operator or function that you want to precede the external reference.
  5. On the View tab, in the Window group, click Switch Windows, click the source workbook, and then click the worksheet that contains the cells that you want to link to.

Excel Ribbon Image

  1. Press F3, and then select the name that you want to link to.

Top of Page Top of Page

Define a name that contains an external reference to cells in another workbook

  1. Open the destination workbook and the source workbook.
  2. In the destination workbook, on the Formulas tab, in the Defined Names group, click Define Name.

Excel Ribbon Image

  1. In the New Name dialog box, in the Name box, type a name for the range.
  2. In the Refers to box, delete the contents, and then keep the cursor in the box.

If the name contains a formula, enter the formula, and then position the cursor where you want the external reference. For example, type =SUM(), and then position the cursor between the parentheses.

  1. On the View tab, in the Window group, click Switch Windows, click the source workbook, and then click the worksheet that contains the cells that you want to link to.

Excel Ribbon Image

  1. Select the cell or range of cells that you want to link to.
  2. In the New Name dialog box, click OK.

Top of Page Top of Page

 
 
Applies to:
Excel 2007