How to Link Two Workbooks in Excel (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Linking workbooks is necessary and effective in various ways. It is necessary when creating a summary or report from multiple data sources, merging data from several workbooks, etc. This article shows 5 different ways how to link two workbooks in excel. The methods involve external reference, the Consolidate tool, copy-paste, the Get data tool, and VLOOKUP.


How to Link Two Workbooks in Excel: 5 Methods

I am going to illustrate 5 different ways for you how to link two workbooks in excel. We will use the following dataset from the source workbook to highlight the methods. The dataset contains 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. So, let’s start!

dataset to link workbooks


1. Link Two Workbooks Using External Reference

Suppose we want to get the annual sales made by each employee in the destination workbook. We can apply the SUM function to do that and use external references to link the two workbooks. Follow the steps below to apply this method.

Steps

1. First, type the following formula in cell C5 in the destination workbook.

=SUM()

2. Do not press Enter yet. Keep the cursor in between the brackets.

entering formula to link two workbooks

3. After that, toggle back to the source workbook using the taskbar. You can also do that from View>>Switch Windows.

4. Then, select the cells you want to sum as follows.

5. Now, if you press Enter, you will see the sales added as follows.

6. Notice that the formula contains an absolute reference.

two workbooks linked

7. Next, remove the $ symbols to make that a relative reference.

8. Finally, use the fill handle to copy the formula to the cells below.

Read More: How to Create Reference with Another Workbook in Excel


2. Link Two Workbooks Using the Consolidate Tool

An alternative to the above method is to use the Consolidate tool in Excel. To do that, apply the following steps.

Steps

1. First, select the first cell or the cell range where you want to get the sum.

2. Then, go to the Data tab. Click on the Consolidate icon in the Data Tools group.

3. This will open a new dialog box.

using consolidate to link workbooks

4. Choose the Sum function in the function field.

5. Now, click on the up arrow in the reference field.

entering reference to link workbooks

6. Then, toggle back to the source workbook.

7. Select the first range of cells as follows.

8. After that, click on the down arrow.

9. Add the reference using the Add button.

add reference to link workbooks

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

11. After that, add the second reference.

12. Then, check the checkbox for Create links to source data.

13. Finally, hit the OK button.

14. At last, you get the same result as earlier.

two workbooks linked using the consolidate tool

Read More: Reference from Another Excel Workbook Without Opening


3. Link Two Workbooks Using Copy-Paste

The easiest way to link two workbooks is by using the copy-paste tool in Excel. Suppose, we want to get our dataset from the source workbook to the destination workbook. To keep the new data linked to the source data, follow the steps below.

Steps

1. First, copy the dataset in the source workbook.

2. Then, go back to the destination workbook.

3. Next, select the cell where you want to copy the dataset.

4. Paste the copied data as a link from Paste Options.

paste as link to link workbooks

5. You can see the new dataset is linked to the source workbook.

two workbooks linked using copy-paste

Read More: Link Excel Workbooks for Automatic Update


4. Link Two Workbooks Using the Get Data Tool

Using the Get Data tool is another amazing way to link two workbooks. Follow the steps below to apply this method.

Steps

1. Firstly, select the cell where you want to get your data.

2. Secondly, from the Data tab, choose to Get Data >> From File >> From Workbook.

using get data tool to link workbooks

3. Browse through your PC to find the source workbook. Then hit the Import button.

4. A new dialog box will open.

5. Select the worksheet where your data is located. Then hit the Load button.

6. Finally, your data will be imported as an Excel Table to a new worksheet as follows.

7. You can copy the data and paste it as a link in your desired worksheet.

workbooks linked by the get data tool


5. Link Two Workbooks Using the VLOOKUP Formula

Two workbooks can also be linked using the VLOOKUP function. Suppose, we want to get the second half-yearly sales by an employee in a new workbook when we enter a name. To do that, follow the steps below.

Steps

1. Type the following in cell C5. Don.t press Enter yet.

=VLOOKUP(B5,

2. Go back to the source workbook. Then select the entire data range as follows.

3. Put a comma(,) after that, and then complete the formula as follows.

=VLOOKUP(B5,[SourceWorkbook.xlsx]Sales!$B$5:$D$10,3,FALSE)

VLOOKUP function to link two workbooks

4. Hit Enter and you will see the following.

5. Now, enter a name and the sales value will be shown as follows.

two workbooks linked using VLOOKUP


Things to Remember

  • Always keep both of the workbooks open while applying these methods.
  • Multiple worksheets can also be linked following these methods.
  • If you want to manually enter the external reference, don’t forget to close 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.


Conclusion

Now you know 5 different ways to link workbooks in excel. Please use the comment section below for further queries. Please share your suggestion with us there too.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo