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!


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.

Paste Special to Automatic Update Excel Workbook

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.

Paste Special to Automatic Update Excel Workbook

Step 3:

  • The Paste Special window will appear. Click on the Paste Link option.

Paste Special to Automatic Update Excel Workbook

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.

Paste Special to Automatic Update Excel 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.

Link Data Manually to Update Workbooks in Excel

Step 2:

  • Then press Enter button.

Link Data Manually to Update Workbooks in Excel

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.

Link Data Manually to Update Workbooks in Excel

 

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.

Link Data Manually to Update Workbooks in Excel

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


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 ")

Automatically Update Excel Workbooks Based on Condition

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 ")

Automatically Update Excel Workbooks Based on Condition

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.

Automatically Update Excel Workbooks Based on Condition

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.

Data tab to Automatic Update Workbooks

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.

Data tab to Automatic Update Workbooks

Notice the Edit Links window again.

Data tab to Automatic Update Workbooks

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.

Automatic Update Using Trust Center Tool in Excel

Step 2:

  • Now, click on Options.

Step 3:

  • From the Excel Options click on Trust Center.
  • Then choose Trust Center Settings.

Automatic Update Using Trust Center Tool in Excel

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.

Automatic Update Using Trust Center Tool in Excel

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.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo