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.

Add the reference using the Add button.

Repeat the selection procedure for the next range of cells.

Add the second reference.
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.

Download Practice Workbook

You can download the practice workbooks from the download buttons below.

SourceWorkbook.xlsx

Link Two Workbooks.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!