Prantick Bala
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.
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.
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.
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
➤ 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 the 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.
Read More: Reference from Another Excel Workbook without Opening (5 Examples)
Similar Readings
- How to Link Data from One Spreadsheet to Another in Excel
- Transfer Specific Data from One Worksheet to Another for Reports
- How to Link Sheets to a Master Sheet in Excel (5 Ways)
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'!G5Here, 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,
='C:\Users\User\Desktop\Reference Workbook.xlsx]Sales Data'!G5Here, C:\Users\User\Desktop is the location of the linked workbook in your computer.
Read More: How to Link Sheets in Excel with a Formula (4 Methods)
Similar Readings
- How to Link Two Sheets in Excel (3 Ways)
- Link Data in Excel from One Sheet to Another (4 Ways)
- How to reference cell in another Excel sheet based on cell value!
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,
='Reference Workbook.xlsx'!Yearly_SalesHere 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.
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.
🔺 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.
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.
