Create workbook links

You can reference cells or a defined name in another workbook or the same workbook. 

Understand the difference between a source and destination workbook

The workbook that contains the links is called the destination workbook, and the workbook that it's linked to is called the source workbook. You can link multiple source workbooks to a single destination workbook.

	The destination workbook contains the workbook link. The workbook link refers to a cell or range in a source workbook. The source workbook has the values returned to the destination workbook.

Create a workbook link between cells in different workbooks

Method 1

1. Open the destination workbook that will contain the external reference and the source destination workbook that contains the data to which you want to create the workbook link.

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

Method 2

1. Open the destination workbook that will contain the workbook link and the source workbook that contains the data that you want to link to.
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, and select Home > Clipboard > Paste > Paste Link.
6. Excel returns the data you copied from the source workbook. If you change it, it will automatically change in the destination workbook.

Create a workbook link to a defined name in another workbook

1. Open the destination workbook that will contain the external reference and the source workbook that contains the data to which you want to create the workbook link.

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.

Define a name that contains a workbook link to cells in another workbook

1. Open the destination workbook and the source workbook.
2. In the destination workbook, select 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.
   

What a workbook link to another workbook looks like

There are different ways Formulas with workbook links are displayed.

When the source workbook is open, the workbook link in the destination workbook 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:

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

When the source workbook is not open, the workbook link in the destination workbook includes the entire path. For example:

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

Formulas that link to a defined name in another workbook use the workbook name followed by an exclamation point (!) and the defined name. For example:

=SUM(Budget.xlsx!Sales)

When you create a workbook link, both workbooks must be saved in an online location you can reach with your Microsoft 365 account.

Create a workbook link between cells in different workbooks

Method 1

1. Open the destination workbook that will contain the external reference and the source destination workbook that contains the data to which you want to create the workbook link.

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 saves the formula in the destination workbook but cannot change the tab back to the destination 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. Full cloud file path is used in the URL:
   =SUM('https:⁠//microsoft-my.sharepoint.com/personal/v-mathavale_microsoft_com/Documents/[data.xlsx]Sheet1'!$A$6:$A$9)

Method 2

1. Open the destination workbook that will contain the workbook link and the source workbook that contains the data that you want to link to.
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, and select Home > Clipboard > Paste > Paste Link.
6. Excel returns 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.