Alok Paul
Tired of repeating the changes in all your workbooks? This article will discuss 5 ways to link multiple Excel workbooks for an automatic update and will ease your tiredness!
Download Practice Workbook
Download these practice workbooks to exercise while you are reading this article.
Destination workbook:
Source workbooks:
5 Methods to Link Excel Workbooks for an Automatic Update
We will discuss 5 methods to link workbooks and how to automatically update them.
1. Use Paste Special to Automatically Update Workbook
We will apply the simple copy and paste method to link between individual workbooks and update them automatically.
Here, we will link Report-1.xlsx and Report-2.xlsx to the Link Excel Workbooks.xlsx file.
Let’s have a quick glance at the source files’ screenshots below.
And the following image shows the Link Excel Workbooks.xlsx file which is our destination file.
Now, let’s jump into the steps without any delay!
Step 1:
- Go to Report-1.xlsx workbook.
- Copy the dates by pressing Ctrl+C from Cell B5 to B9.
Step 2:
- Go to Link Excel Workbooks.xlsx worlbook.
- Click on Cell B5 and press the right button of the mouse.
- Choose Paste Special from the list.
Step 3:
- The Paste Special window will appear. Click on the Paste Link option.
Now, look at the following image.
Step 4:
- Now, go to the Report-2.xlsx workbook.
- Copy cells from C5 to C9 in the Bill column.
Step 5:
- Again, go to the Link Excel Workbooks.xlsx workbook.
- Enter Cell C5 and press the right button of the mouse.
- Either repeat the previous steps or Choose Paste Link(N) from the Paste Options.
Now, notice the Bill column.
Values copied from the source workbooks to destination workbooks.
Step 6:
- Now, change the value of Cell C5 of the Report-2.xlsx workbook and see what happens in the destination workbook.
In the Link Excel Workbooks.xlsx file Cell C5 is updated automatically, as we changed its corresponding linked file.
Read More: How to Link Excel Workbooks (4 Effective Methods)
2. Link Data Manually to Update Workbooks in Excel
We will manually link multiple workbooks in Excel and that will automatically update.
Step 1:
- First, go to Cell B5 of the destination file.
- Put the Equal sign (=) here.
- Now, go to the Report-1.xlsx file and click on Cell B5.
Step 2:
- Then press Enter button.
We can see that workbooks are linked and values are presented on the destination file.
Step 3:
- Execute this process for the rest of the cells of the Date column for these two workbooks.
Step 4:
- Again, go to the destination file.
- Click on Cell C5 and put the Equal sign(=).
- Now, go to the Report-2.xlsx workbook.
- Click on Cell C5.
Step 5:
- Then press Enter.
Step 6:
- Repeat this process for the remaining cells.
This is our final output.
Step 7:
- Now, change the value of Cell C9 of the Report-2.xlsx workbook.
In the destination file, the value of Cell C9 also changed accordingly.
Read More: How to Link Two Workbooks in Excel (5 Methods)
Similar Readings
- How to Link Files in Excel (5 Different Approaches)
- Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)
- How to Link Excel Sheets to Another Sheet (5 Ways)
- Link Word Document to Excel (2 Easy Methods)
- Best Practices for Linking Excel Spreadsheets
3. Automatically Update Workbooks Based on Condition
Here, we will apply the condition ion formula while linking multiple workbooks.
We assume a condition, those bills are less than $ 1500 will show. The Report-2.xlsx and the Link Excel Workbooks.xlsx will be used here.
Step 1:
- Go to Link Excel Workbooks.xlsx file.
- Click on Cell C5.
- Write down the following formula.
=IF('[Report-2.xlsx]Data_Set_2'!$C$5<1500,'[Report-2.xlsx]Data_Set_2'!$C$5, "N/A ")
This formula is created by taking references from the Report-2.xlsx workbook.
Step 2:
- Now, press Enter.
Now, a value from the Report-2.xlsx file is showing.
Step 3:
- Go to Cell C6.
- Put the formula below.
=IF('[Report-2.xlsx]Data_Set_2'!$C$6<1500,'[Report-2.xlsx]Data_Set_2'!$C$6, "N/A ")
Here, we changed the cell reference from C5 to C6.
Step 4:
- Again, click the Enter button.
Another value from the Report-2.xlsx file is showing.
Step 5:
- Similarly, create formulas for the rest of the cells and execute them.
See those cells containing values more than $ 1500 is indicated by N/A.
Read More: How to Link Sheets in Excel with a Formula (4 Methods)
4. Use the Data tab to Automatic Update Workbooks
We can update workbooks automatically using the Data tab.
Step 1:
- Go to the Data tab first.
- Select the Edit Links option.
Step 2:
- Edit Links window will appear. Select the links using the Ctrl button.
- Now, click on Update Values. In the status section, showing Unknown.
Notice the Edit Links window again.
Status changes into OK. Now, close this window and data will be automatically updated.
Read More: How to Link Data from One Spreadsheet to Another in Excel
5. Automatic Update Using Trust Center Tool in Excel
We can update workbooks automatically using the Trust Center tool.
Step 1:
- First, copy data from the Report-1.xlsx file to the Link Excel Workbooks.xlsx
- Then, go to the File tab.
Step 2:
- Now, click on Options.
Step 3:
- From the Excel Options click on Trust Center.
- Then choose Trust Center Settings.
Step 4:
- In the Trust Center go to External Content.
- From the Security settings for Workbook Links tick on Enable automatic update for all Workbook Links.
- Finally, click OK.
Now, the external links from any other workbook will automatically update.
Read More: Transfer Specific Data from One Worksheet to Another for Reports
Things to Remember
- Fill Handle operation will not work when linking different worksheets.
- When source files are closed, we will apply the Data
Conclusion
In this article, we explained how to link Excel workbooks with automatic update. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
