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.

dataset to link workbooks


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.
  • entering formula to link two workbooksToggle 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.
  • two workbooks linkedRemove 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.

using consolidate to link workbooks

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

entering reference to link workbooks

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

add reference to link workbooks

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

two workbooks linked using the consolidate tool

Read More: Reference from Another Excel Workbook Without Opening


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.

paste as link to link workbooks

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

two workbooks linked using copy-paste

Read More: Link Excel Workbooks for Automatic Update


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.

using get data tool to link workbooks

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

workbooks linked by the get data tool


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)

VLOOKUP function to link two workbooks

  • Hit Enter to apply the formula.

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

two workbooks linked using VLOOKUP


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.


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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo