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

dataset 1

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

dataset 2

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


Download Practice Workbooks


4 Methods to Link Excel Workbooks

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.

link Excel workbooks

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.

paste link

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

link Excel workbooks

Read More: Link Excel Workbooks for Automatic Update (5 Methods)


2. Switching Between Two Workbooks

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

➤ Open both workbooks

both workbooks

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

referring

Now,

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

link Excel workbooks

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”).

link Excel workbooks

Now,

➤ Press ENTER.

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

link Excel workbooks

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 the relative reference.

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

link Excel workbooks

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.

Read More: Reference from Another Excel Workbook without Opening (5 Examples)


Similar Readings


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,

='[Reference Workbook.xlsx]Sales Data'!G5

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

link Excel workbooks

➤ Press ENTER.

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

link Excel workbooks

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.

link Excel workbooks

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

='C:\Users\User\Desktop\Reference Workbook.xlsx]Sales Data'!G5

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

link Excel workbooks

Read More: How to Link Sheets in Excel with a Formula (4 Methods)


Similar Readings


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.

used range

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

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

name box

After that,

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

='Reference Workbook.xlsx'!Yearly_Sales

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

link Excel workbooks

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.

result

Read More: How to Reference Worksheet Name in Formula in Excel (3 Easy Ways)


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.

warning

🔺 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 will make a change in the other workbook.

automatic calculation


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.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo