How to Link Excel Workbooks (4 Effective Methods)

Sometimes you need to link one Excel workbook with other workbooks to import data or to use in a formula. In this article, I’ll show you 4 effective methods to link Excel workbooks with each other.

Suppose you have sales data of different salesmen in a sheet named “Sales Data” of the workbook named “Reference Workbook”.

Now, you want to extract “Yearly Sales” from the Reference Workbook to a sheet named “Sales Summary” in the workbook named “Link Excel Workbooks”.

I will show you how you can do that by linking the two workbooks.

How to Link Excel Workbooks: 4 Methods

1. Link Excel Workbooks Using Paste Link Option

To use the Paste Link option, first,

➤ Select the cells of the “Yearly Sales” column from the “Reference Workbook” and right-click on it.

It will open a context menu.

➤ Click on Copy from this menu.

After that,

➤ Open the “Link Excel Workbooks” Excel file

➤ Right-click on cell C5 (first cell of “Yearly Sales” column)

A context menu will appear.

➤ Click on the Paste Link icon from the Paste Options.

As a result, the “Reference Workbook” will be linked with the current workbook and you will get the data in the second workbook from the first workbook.

If you click on any of the linking cells, you can see that the cell is referring to another workbook in the formula bar.

2. Switching Between Two Workbooks

You can link workbooks by creating a formula and switching between these workbooks. First,

➤ Open both workbooks

➤ Type = in cell C5 (first cell of the “Yearly Sales” column).

Now,

➤ Go to the “Reference Workbook” and click on cell G5 (first cell of “Yearly Sales” column of the “Reference Workbook”)

After that,

➤ Go back to the first workbook.

Now, you will see the selected cell of the first workbook (“Link Excel Workbooks”) is referring to a cell of the second workbook (“Reference Workbook”).

Now,

➤ Press ENTER.

So, cell C5 of this workbook will be linked with cell G5 of the other workbook.

Now, when you link one workbook with another workbook in this way, Excel automatically applies Absolute reference. You need to convert this absolute reference into a relative reference.

➤ Delete the dollar sign ($) from the formula.

At last,

➤ Press ENTER and Drag cell C5 to the end.

As a result, the “Reference Workbook” will be linked with the “Link Excel Workbooks” workbook and you will get the data in the second workbook from the first workbook.

3. By Entering Formula Manually

You can manually link a workbook with another Excel workbook by referring to the second workbook in the formula of the first workbook.

➤ Type the following formula in cell C5,

Here, Reference Workbook.xlsx refers to the second workbook and Sales Data refers to the datasheet containing data for linking that workbook. G5 is the first cell of the “Yearly Sales” column from the Sales Data sheet of the second workbook

➤ Press ENTER.

So, cell C5 of this workbook will be linked with cell G5 of the other workbook.

At last,

➤ Drag cell C5 to the end.

As a result, the “Reference Workbook” will be linked with the “Link Excel Workbooks” and you will get the data in the second workbook from the first workbook.

If you close the second workbook now, the formula in the first workbook will automatically change to this,

Here, C:\Users\User\Desktop is the location of the linked workbook in your computer.

4. Link Excel Workbooks Using Named Range

You can link Excel workbooks by using Named Range. First,

➤ Open the “Reference Workbook” and select the cells of the Yearly Sales column

➤ Go to Formulas > Defined Names > Define Name.

As a result, a window named New Name will appear.

➤ In the Name box, type a name for the selected cells and press OK.

After that,

➤ Open the workbook “Link Excel Workbooks” and Type the following formula in cell C5,

Here the formula will link the named range of the other workbook with this workbook.

Now, if you use Excel 365,

➤ Press ENTER.

If you use any other version of Excel, you have to press CTRL+SHIFT+ENTER for this array formula.

After that, the other workbook will be linked with this workbook.

Things to Remember

🔺 When you close the workbook, Excel disables all the links used in the workbook for security purposes. To use this link you have to enable them by clicking on the Enable Content in the SECURITY WARNING dialog box, after reopening the workbook.

🔺 Check if the Automatic option is checked from Formulas > Calculation Options > Automatic. Otherwise, the linked cells of your workbook won’t change automatically when you make a change in the other workbook.

Download Practice Workbooks

Conclusion

I hope now you know how to link Excel workbooks. If you feel any confusion about any of the four methods described in this article, please feel free to leave a comment.

Linking Workbooks in Excel: Knowledge Hub

• Link Two Workbooks in Excel
• Create Reference with Another Workbook
• Reference from Another Excel Workbook Without Opening
• Link Excel Workbooks for Automatic Update
• [Fixed!] Excel Links Not Working Unless Source Workbook Is Open
• [Fixed!] This Workbook Contains Links to One or More External Sources That Could Be Unsafe
• [Fixed!] ‘This workbook contains links to other data sources’ Error in Excel

<< Go Back To Linking in Excel | Learn Excel