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.

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.

• 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 to Update Links Without Opening File in Excel

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.

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

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

Read More: How to Disable Automatic Update of Links in Excel

2. Updating Links with the 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.

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

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

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

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

Read More: [Fixed!] Excel Links Not Updating Unless Source Is Open

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.

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.

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:

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 the File. If you have any queries or recommendations, please share them in the comments section below.

Related Articles

• [Fixed!] Excel Update Links Manually Greyed Out
• 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