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.

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.

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

• 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.

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.

• Hit Enter.

• 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.

• 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.

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.

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.

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.

Method 4 – Use the Data tab to Automatically Update Workbooks

Steps:

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

• 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.

• The status is updated to OK.
• Close the window.

Read More: Reference from Another Excel Workbook Without Opening

Method 5 – Automatic Updates Using the Trust Center in Excel

Steps:

• Copy the data from the Report-1.xlsx file to the Link Excel Workbooks.xlsx
• Go to the File tab.

• Click on Options.

• From the Excel Options, click on Trust Center.
• Choose Trust Center Settings.

• Go to External Content.
• From the Security settings for Workbook Links, check Enable automatic update for all Workbook Links.
• Click OK.

Things to Remember

• The Fill Handle will not work when linking different worksheets.

Download the Practice Workbook

Destination workbook:

Source workbooks:

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