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 formula. 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.

WindowsWeb



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. Select the cell or cells where you want to create the external reference.

3. Type = (equal sign).

   If you want to use a function, such as SUM, then type the function name followed by an opening parenthesis. For example, =SUM(.

4. Switch to the source workbook, and then click the worksheet that contains the cells that you want to link.

5. Select the cell or cells that you want to link to and press Enter.

   Note: If you select multiple cells, like =[SourceWorkbook.xlsx]Sheet1!$A$1:$A$10, and have a current version of Microsoft 365, then you can simply press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by pressing CTRL+SHIFT+ENTER. For more information on array formulas, see Guidelines and examples of array formulas.

6. Excel will return you to the destination workbook and display the values from the source workbook.

7. Note that Excel will return the link with absolute references, so if you want to copy the formula to other cells, you'll need to remove the dollar ($) signs:

   =[SourceWorkbook.xlsx]Sheet1!$A$1

   If you close the source workbook, Excel will automatically append the file path to the formula:

   ='C:\Reports\[SourceWorkbook.xlsx]Sheet1'!$A$1

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. Select the cell or cells where you want to create the external reference.

3. Type = (equal sign).

4. Switch to the source workbook, and then click the worksheet that contains the cells that you want to link.

5. Press F3, select the name that you want to link to and press Enter.

   Note: If the named range references multiple cells, and you have a current version of Microsoft 365, then you can simply press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by pressing CTRL+SHIFT+ENTER. For more information on array formulas, see Guidelines and examples of array formulas.

6. Excel will return you to the destination workbook and display the values from the named range in the source workbook.

1. Open the destination workbook and the source workbook.

2. In the destination workbook, Go to Formulas > Defined Names > Define Name.

   

3. In the New Name dialog box, in the Name box, type a name for the range.

4. In the Refers to box, delete the contents, and then keep the cursor in the box.

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

5. Switch to the source workbook, and then click the worksheet that contains the cells that you want to link.

6. Select the cell or range of cells that you want to link, and click OK.

   

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

• Merge data from several workbooks    You can link workbooks from several users or departments and then integrate the pertinent data into a summary workbook. That way, when the source workbooks are changed, you won't have to manually change the summary workbook.

• Create different views of your data    You can enter all of your data into one or more source workbooks, and then create a report workbook that contains external references to only the pertinent data.

• Streamline large, complex models    By breaking down a complicated model into a series of interdependent workbooks, you can work on the model without opening all of its related sheets. Smaller workbooks are easier to change, don't require as much memory, and are faster to open, save, and calculate.

Formulas with external references to other workbooks are displayed in two ways, depending on whether the source workbook — the one 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 spaces or non-alphabetical characters, you must enclose the name (or the path) within single quotation marks as in the example above. Excel will automatically add these for you when you select the source range.

Formulas that link to a defined name 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)

1. Select the cell or cells where you want to create the external reference.

2. Type = (equal sign).

   If you want to use a function, such as SUM, then type the function name followed by an opening parenthesis. For example, =SUM(.

3. Switch to the worksheet that contains the cells that you want to link to.

4. Select the cell or cells that you want to link to and press Enter.

   Note: If you select multiple cells (=Sheet1!A1:A10), and have a current version of Microsoft 365, then you can simply press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by pressing CTRL+SHIFT+ENTER. For more information on array formulas, see Guidelines and examples of array formulas.

5. Excel will return to the original worksheet and display the values from the source worksheet.

Create an external reference between cells in different workbooks

1. Open the workbook that will contain the external reference (the destination workbook, also called the formula workbook) and the workbook that contains the data that you want to link to (the source workbook, also called the data workbook).

2. In the source workbook, select the cell or cells you want to link.

3. Press Ctrl+C or go to Home > Clipboard > Copy.

4. Switch to the destination workbook, and then click the worksheet where you want the linked data to be placed.

5. Select the cell where you want to place the linked data, then go to Home > Clipboard > Paste > Paste Link.

6. Excel will return the data you copied from the source workbook. If you change it, it will automatically change in the destination workbook when you refresh your browser window.

7. To use the link in a formula, type = in front of the link, choose a function, type (, and then type ) after the link.

Create a link to a worksheet in the same workbook

1. Select the cell or cells where you want to create the external reference.

2. Type = (equal sign).

   If you want to use a function, such as SUM, then type the function name followed by an opening parenthesis. For example, =SUM(.

3. Switch to the worksheet that contains the cells that you want to link to.

4. Select the cell or cells that you want to link to and press Enter.

5. Excel will return to the original worksheet and display the values from the source worksheet.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Define and use names in formulas

Find links (external references) in a workbook