How to Update Links Without Opening File in Excel (4 Methods)

If you are looking for special tricks to learn how to update Excel links without opening file, you’ve come to the right place. There are four tricky methods to update Excel links without opening file. This article will discuss every step of these methods to update links without opening Excel files. Let’s follow the complete guide to learn all of this.


Download Practice Workbook

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:


4 Easy Methods to Update Links Without Opening File in Excel

In the following section, we will use four effective and tricky methods to update Excel links without opening files. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Let us first introduce you to our Excel worksheet so that you are able to understand what we are trying to accomplish with this article. The following Excel files are the source files.

Update Links Without Opening File in Excel

  • Creating links is done in the following Excel file.

Now, we are going to demonstrate how to update Excel links without opening the file.


1. Using Edit Links Command from Data Tab to Automatic Update Links

Here, we will demonstrate how to update links without opening Excel files. Here, we will use the Data tab to update automatically. Let’s walk through the following steps to update Excel links without opening the files.

📌 Steps:

  • First of all, go to the Data tab.
  • Next, select the Edit Links option.

using Data tab to Update Links Without Opening File in Excel

  • Therefore, the Edit Links window will appear.
  • Next, select the links using the Ctrl button as shown below.
  • Now, click on Update Values. In the status section, it shows Unknown.
  • Next, click on Check Status.

click on update values

  • Therefore, the Edit Links window will appear again.
  • Now, you will notice that the Status changes to OK.

Now, 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.

2. Use of Trust Center Tool from Excel Options

Now, we will illustrate another tricky method to update links without opening an Excel file. Here, we will use the Trust Center tool to do that. Let’s walk through the following steps to update Excel links without opening the file.

📌 Steps:

  • First of all, go to the File tab.
  • Now, click on Options.

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

  • Therefore, the Excel Options window will appear.
  • Next, click on Trust Center.
  • Then, choose Trust Center Settings.

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

  • Consequently, the Trust Center window will appear.
  • Next, select External Content.
  • Then, check Enable automatic update for all Workbook Links from the Security settings for Workbook Links.
  • Finally, click OK.

select External content

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


3. Place Macro in Source Workbook That Will Update Links

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 or not. A button could also be used to control this.


4. Update Formulas in Destination Workbook to Recalculate

Now, we will illustrate another tricky method to update links without opening an Excel file. Here, we will apply a conditional statement to do that. we will use the IF function. Let’s walk through the following steps to update links without opening an Excel file.

📌 Steps:

  • First of all, 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.
  • Therefore, you will get the following output.

  • After that, type the formula individually in each cell to fill the other cells with the formula.
  • Therefore, 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. Otherwise, the linked cells of your workbook won’t change automatically when you will make a 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.


Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to update Excel links without opening File. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo