How to Link Two Workbooks in Excel (5 Methods)

Consider the following dataset, containing half-yearly sales made by six employees of a company in 2021. The source and destination workbooks are named “SourceWorkbook” and “Link Two Workbooks” respectively. The methods for linking them involve external reference, the Consolidate tool, copy-paste, the Get data tool, and VLOOKUP.

Method 1 – Link Two Workbooks Using External Reference

Suppose you need to get the annual sales made by each employee in the destination workbook. You can apply the SUM function to do that, with external references to link the two workbooks.

Steps

• Type the following formula in cell C5 in the destination workbook:
`=SUM()`
• Do not press Enter yet. Put the cursor between the parentheses.
• Toggle back to the source workbook using the taskbar. You can also do that from View and Switch Windows.
• Select the cells you want to sum with your mouse.
• If you press Enter now, you will see the sales added as follows.
• Notice that the formula contains an absolute reference.
• Remove the \$ symbols to convert them into relative references.
• Drag the fill handle down to copy the formula to the cells below.

Method 2 – Link Two Workbooks Using the Consolidate Tool

Steps

• Select the first cell or the cell range where you want to get the sum.

• Go to the Data tab.
• Click on the Consolidate icon in the Data Tools group. This will open a new dialog box.

• Choose the Sum function in the Function field.
• Click on the up arrow in the Reference field.

• Go back to the source workbook.
• Select the first range of cells andÂ click on the down arrow.

• Repeat the selection procedure for the next range of cells.

• Check Create links to source data.
• Hit the OK button.

• You should get the same result as in method 1.

Method 3 – Link Two Workbooks Using Copy-Paste

Steps

• Copy the dataset in the source workbook.

• Go to the destination workbook.
• Select the cell where you want to copy the dataset.
• Right-click and select the link icon from Paste Options.

• You can see the new dataset linked to the source workbook.

Method 4 – Link Two Workbooks Using the Get Data Tool

Steps

• Select the cell where you want to get your data.

• Go to the Data tab, choose Get Data, then From File, and finally From Workbook.

• Browse through your PC to find the source workbook.
• Select the Import button. A new dialog box will open.

• Select the worksheet where your data is located and choose Load on the bottom.

• Your data will be imported as an Excel Table to a new worksheet.
• You can copy the data and paste it as a link in your desired worksheet.

Method 5 – Link Two Workbooks Using the VLOOKUP Formula

Suppose you want to get the second half-yearly sales by an employee in a new workbook when we enter a name. Here’s how to do that.

Steps

• Type the following in cell C5:
`=VLOOKUP(B5,`

• Go back to the source workbook and select the entire data range.

• Put a comma(,) and complete the formula as follows:
`=VLOOKUP(B5,[SourceWorkbook.xlsx]Sales!\$B\$5:\$D\$10,3,FALSE)`

• Hit Enter to apply the formula.

• You can enter a name in B5 and the sales value will be fetched.

Things to Remember

• Always keep both of the workbooks open while applying these methods.
• More than two worksheets can be linked together.
• If you want to manually enter an external reference, donâ€™t forget to enclose the file name inside single quotation marks().
• Data will be automatically updated while both of the workbooks are open. Otherwise, you will need to update the destination workbook.

Related Articles

<< Go Back To Linking Workbooks in ExcelÂ | Linking in ExcelÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF