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!
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.
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.
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.
4. Choose the Sum function in the function field.
5. Now, click on the up arrow in the reference field.
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.
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.
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.
5. You can see the new dataset is linked to the source workbook.
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.
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.
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)
4. Hit Enter and you will see the following.
5. Now, enter a name and the sales value will be shown as follows.
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.
