How to Update Links Without Opening File in Excel: 4 Methods

Method 1 – Using Edit Links Command to Update Links Without Opening File in Excel

Steps:

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

• The Edit Links window will appear.
• Select the links using the Ctrl button, as shown below.
• Click on Update Values. In the status section, it shows Unknown.
• Click on Check Status.

• The Edit Links window will appear again.
• The Status changes to OK.

Close this window, and the data will be automatically updated.

Method 2 – Updating Links with the Trust Center Tool from Excel Options

Steps:

• Go to the File tab.
• Click Options.

• The Excel Options window will appear.
• Click on Trust Center.
• Choose Trust Center Settings.

• The Trust Center window will appear.
• Select External Content.
• Check Enable automatic update for all Workbook Links from the Security settings for Workbook Links.
• Click OK.

Any external links from another workbook will now be automatically updated.

Method 3 – Place Macro in Source Workbook That Will Update Links in Excel

Create a macro that runs every time the protected source workbook is closed, updating the derivatives workbooks, which can be as simple as recalculating after opening. You should also ensure that derivative workbooks can be updated without opening the source workbook if they have links that are references to cells.

By recording a macro in the source file, the derivative files can be opened, recalculated, and saved. It might be a good idea to let the user decide whether to update the derivative files. A button could also be used to control this.

Method 4 – Update Formulas in Destination Workbook to Recalculate

Steps:

• After opening the Destination workbook, you have to write down the following formula.

This formula is created by taking references from the Report-2.xlsx workbook. If the selected value from Data_Set_2 is less than “1500” in this case, the IF function will return the value; otherwise, it will display “N/A”.

• Press Enter.
• Get the following output.

• Type the formula individually in each cell to fill the other cells with the formula.
• The output will look like this.

Any external links from another workbook will now be automatically updated.

Things to Remember

✎ When linking different worksheets, the Fill Handle operation will not work.

✎ When you close the workbook, Excel disables all the links used in the workbook for security purposes. To use this link you have to enable them by clicking on the Enable Content in the SECURITY WARNING dialog box, after reopening the workbook.

Download Practice Workbooks

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

Destination File:

Source File:

Related Articles

• How to Update Hyperlink in Excel Automatically
• [Fix]: Excel Automatic Update of Links Has Been Disabled

<< Go Back To Update Excel Links | Linking in Excel | Learn Excel