Link Excel Workbooks for Automatic Update (5 Methods)

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!

How to Link Excel Workbooks for an Automatic Update: 5 Methods

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 workbook.
• 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 Two Workbooks in Excel

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 Create Reference with Another Workbook in Excel

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.

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.

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.

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: Reference from Another Excel Workbook Without Opening

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.

Things to Remember

• Fill Handle operation will not work when linking different worksheets.
• When source files are closed, we will apply the Data

Download Practice Workbook

Download these practice workbooks to exercise while you are reading this article.

Destination workbook:

Source workbooks:

Conclusion

In this article, we explained how to link Excel workbooks with automatic update. I hope this will satisfy your needs. Please give your suggestions in the comment box.

Related Articles

• [Fixed!] Excel Links Not Working Unless Source Workbook Is Open
• [Fixed!] This Workbook Contains Links to One or More External Sources That Could Be Unsafe
• [Fixed!] ‘This workbook contains links to other data sources’ Error in Excel

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