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.

using Data tab to Update Links Without Opening File in Excel

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

click on update values

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

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

Note:

  • It is possible to link workbooks using four methods: paste the link option, switch between two workbooks, enter the formula manually, and use a named range.

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

Steps:

  • Go to the File tab.
  • Click Options.

using trust center tool to Update Links Without Opening File in Excel

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

using trust center tool to Update Links Without Opening File in Excel

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

select External content

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.

=IF('F:\SOFTEKO\61-0095\[Report-2.xlsx]Data_Set_2'!$C$5<1500,'F:\SOFTEKO\61-0095\[Report-2.xlsx]Data_Set_2'!$C$5, "N/A ")

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.

using conditional formula to Update Links Without Opening File in Excel

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

Note:

  • Check if the Automatic option is checked from Formulas > Calculation Options> Automatic. The linked cells of your workbook won’t change automatically when you change in the other workbook.


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

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

Get FREE Advanced Excel Exercises with Solutions!
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

2 Comments
  1. Thanks Saquib.
    So if I understand correctly, the nested sheet between the source sheet and the outpoint sheet can only refresh if it’s opened, wheather by VB or manually. Is that correct?

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo