Link Excel Workbooks for Automatic Update (5 Methods)

Method 1 – Use Paste Special to Automatically Update Workbook

We will link Report-1.xlsx and Report-2.xlsx to the Link Excel Workbooks.xlsx file.

Paste Special to Automatic Update Excel Workbook

The following image shows the Link Excel Workbooks.xlsx file which is the destination file.

Steps:

Go to the Report-1.xlsx workbook.
Copy the dates by selecting B5:B9 and pressing Ctrl + C.

Go to the Link Excel Workbooks.xlsx workbook.
Click on Cell B5 and right-click.
Choose Paste Special from the list.

Paste Special to Automatic Update Excel Workbook

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

Paste Special to Automatic Update Excel Workbook

Here’s the result.

Go to the Report-2.xlsx workbook.
Copy cells from C5 to C9 in the Bill column.

Go to the Link Excel Workbooks.xlsx workbook.
Select Cell C5 and right-click.
Choose Paste Link (N) from the Paste Options.

The Bill column is filled.

Change the value of Cell C5 of the Report-2.xlsx workbook.
In the Link Excel Workbooks.xlsx file, the cell C5 is updated automatically.

Paste Special to Automatic Update Excel Workbook

Read More: How to Link Two Workbooks in Excel

Method 2 – Link Data Manually to Update Workbooks in Excel

Steps:

Go to Cell B5 of the destination file.
Input the Equal sign (=).
Go to the Report-1.xlsx file and click on Cell B5.

Link Data Manually to Update Workbooks in Excel

Hit Enter.

Link Data Manually to Update Workbooks in Excel

Repeat the process for the rest of the cells of the Date column for these two workbooks.

Go to the destination file.
Click on Cell C5 and put the Equal sign(=).
Go to the Report-2.xlsx workbook.
Click on Cell C5.

Link Data Manually to Update Workbooks in Excel

Hit Enter.

Repeat this process for the remaining cells.

Here’s the output.
Change the value of Cell C9 of the Report-2.xlsx workbook.
In the destination file, the value of Cell C9 also changed accordingly.

Link Data Manually to Update Workbooks in Excel

Method 3 – Automatically Update Workbooks Based on a Condition

Only bills with value lower than $1,500 will show. The Report-2.xlsx and the Link Excel Workbooks.xlsx will be used here.

Steps:

Go to the Link Excel Workbooks.xlsx file.
Click on Cell C5.
Use 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.

Press Enter.

A value from the Report-2.xlsx file is showing.
Go to Cell C6.
Insert 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

We changed the cell reference from C5 to C6.

Hit the Enter button.

Insert the other formulas for subsequent cells by changing the references.
Cells that weren’t fetched (due to a large value) show N/A.

Automatically Update Excel Workbooks Based on Condition

Method 4 – Use the Data tab to Automatically Update Workbooks

Steps:

Go to the Data tab first.
Select the Edit Links option.

Data tab to Automatic Update Workbooks

The Edit Links window will appear. The status of all links is unknown. Select those links while holding the Ctrl button.
Click on Update Values.

Data tab to Automatic Update Workbooks