How to Update Excel Links (Manually/Automatically)

We’ll use the following datasets to demonstrate how you can update links inside and between sheets.

Overview of How to Update Excel Links


Update Links Automatically
Update Excel Links
 ⏵Update Links Without Opening File
 ⏵Change Source File for the Links
Update Hyperlink to a Web Address
Break Source Links
Things to Remember
Frequently Asked Questions
Update Excel Links: Knowledge Hub


How to Update Links Automatically in Excel

Consider the following workbooks. We have linked the Physics Marks workbook with the Update Excel Link workbook.

Linked two workbooks

Let’s change the values in the Physics Marks workbook i.e. source workbook.

Changed values in the source file to Update links in target Excel file

Open the Update Excel Link workbook i.e. target file. The links in the target workbook can be updated manually or automatically.

Case 1 – Manually Updating All Links:

  • After opening the target file, you will get a notification alert like the picture below.

Opening the target workbook and getting update alert

  • Click the Update button to update the values in the target file.

Manually Updating Links in Excel

Case 2 – Automatically Updating All Links:

  • Open the target workbook and go to the File tab.Going to the File tab
  • From the File tab, click Options.

opening Options setting

  • You will get the Excel Options dialog box. Click Trust Center from the left-side pane and select Trust Center Settings.

Going to the Trust Center settings to Automatically Update Links in Excel

  • In the Trust Center dialog box, select External Content from the left-side pane.
  • Set Security settings for Workbook links to the Enable automatic update for all Workbook Links option.
  • Click the OK button.

Enabling automatic update of links in Excel Workbook

  • Go to the Data tab and click Edit Links.

opening Edit Links tool to update links in Excel

  • In the Edit Links window, click the Startup Prompt button.

opening Startup Prompt

  • In the Startup Prompt dialog box, select Don’t display the alert and update links and click OK.

disabling the alert display and automatically update links in target Excel file

  • Click on the Close button in the Edit Links window.

Closing Edit Links

  • Whenever you open the target file, it will automatically update according to the source file values so long Excel can find the file.

Values updated automatically as we opened the target Excel file


How to Update Excel Links

Method 1 – Update Excel Links Without Opening the File

Consider the following workbooks. The Update Excel Link workbook is linked with the source file Physics Marks.

Linked Physics Marks workbook with Updated Excel Link Workbook

If you change the source file “Physics Marks”, the target file “Update Excel Link” doesn’t change.

Changed Values in the Source file and target file is not updated

Steps:

  • To update the value, open the target file Update Excel Link.
  • Go to the Data tab and select Edit Links.

Opening Edit Links Tool to Update Links in Excel Without Opening Workbook

  • In the Edit Links dialog box, choose the source file and then click the Update Values button.

Using Updated values feature to Update Links from the source file to the target Excel file

  • This will change the Unknown status into OK.
  • Click the Close button.

closing edit links window

  • This will update the linked values in the target Excel workbook.

values updated according to the source file

Read More: Update Links Without Opening File


Method 2 – Change the Source File for the Links

For this method, consider the following workbooks. The source file is the Midterm Marks and the target file is the Change Excel Link. We want to change the source file from Midterm Marks to Final Marks, and this will also change the values in the target file.

Linked Midterm Marks workbook with Change Excel Link workbook

Steps:

  • Open the Change Excel Link Workbook.
  • Go to the Data tab and click on Edit Links.

Opening Edit Links window

  • Select the source file and click the Change Source button.

Changing the source file

  • You will get a File Manager window where you can select the new source file. Select the new source file and click OK.
Changing the Source file with Final Marks workbook

Click the image for a detailed view

  • The source file has changed.
  • Click the Close button.

closing Edit Links window

  • This will update the values in the target file according to the new source file.

Values changed in the target file as the Source file changed


How to Update a Hyperlink to a Web Address in Excel

Consider the following dataset. The cell contents are hyperlinks to web addresses. However, the hyperlink address in cell B5 is incorrect here. Instead of hyperlinking to https://www.exceldemy.com/, it’s going to https://exceldemy.com/forum/.

Incorrect Linking in Excel

Steps:

  • Select cell B5.
  • Right-click and select the Edit Hyperlink command.

Selecting Edit Link Command from Context Menu in Excel

  • You will get a dialog box to edit the hyperlink.
  • Go to the Existing File or Web Page tab.
  • Replace the current incorrect Address with the correct web Address.
  • Edit the Text to display if required.
  • Click the OK button.
Update Web Links in Excel

Click the image for a detailed view

  • The link in cell B5 will get updated.

Updated link in Excel


How to Break Source Links in Excel

Consider the following linked workbooks: the Physics Marks workbook and the Update Excel Link workbook. We will break the links between these workbooks.

values are same as the source file

Steps:

  • Open the Update Excel Link workbook.
  • Go to the Data tab and select Edit Links.

Opening Edit Links window

  • In the Edit Links window, select the source workbook and click the Break Link button.

Breaking Link with the source file

  • You will get an alert. Confirm breaking links by clicking the Break Links button.

Confirming Break Links

  • This will break the link with the source workbook. No workbook is showing anymore in the Edit links window.
  • Click the Close button.

Link broke with the source file

  • If you change the source workbook, no change will occur in the target workbook because we broke the link.

As the link has been broken the target file did not change as the source file changed


What Are the Things to Remember?

  • Once you break links, you cannot undo it. You’ll need to relink the sheets manually.
  • If you don’t change the settings to update the links automatically, you will get an alert notification. From there, you can update it manually.

Frequently Asked Questions

Why is Excel asking me to update links?

When you open a workbook that is linked with another workbook, Excel shows you a notification such as “This workbook contains links to other data sources”. If you choose to update, it will update the file according to its source files.

How do you update linked Data in Excel?

Go to Data >> Edit Links >> Update Values. This will update the linked data.

What is the use of Edit Links in Excel?

You can update the values of the current workbook according to its source workbook. You can also change the source workbook for the current workbook and get values from another workbook.


Download Practice Workbooks


Update Excel Links: Knowledge Hub

<< Go Back To Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Seemanto Saha
Seemanto Saha

Seemanto Saha graduated in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. He has been with ExcelDemy for a year, where he wrote 40+ articles and reviewed 50+ articles. He has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, he is working as a team leader for ExcelDemy. His role is to guide his team to write reader-friendly content. His interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo