In this Excel tutorial, we will learn to update links in Excel. Sometimes we may need to work with multiple workbooks and link data from different workbooks to other workbooks. Here we will learn how to update data in the target workbook if we change values in the source workbook.
We can also change the source workbook values and permissions. And break the links between the workbooks.
Finally, we will learn how we can update the links automatically in Excel. So, without any further delay let’s dive into the methods.
⏷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.
Now, let’s change the values in the Physics Marks workbook i.e. source workbook.
After changing the values of the source file, open the Update Excel Link workbook i.e. target file. The links in the target workbook can be updated manually or automatically. Follow the steps below to learn both methods.
Manually Updating All Links:
- After opening the target file, you will get a notification alert like the picture below.
- Click the Update button to update the values in the target file.
Automatically Updating All Links:
- Open the target workbook and go to the File tab.
- From the File tab, click Options.
- After clicking Options, you will get the Excel Options dialog box. Click Trust Center from the left-side pane >> click Trust Center Settings.
- In the Trust Center dialog box, select External Content from the left-side pane >> set Security settings for Workbook links to Enable automatic update for all Workbook Links option >> click the OK button.
- Then, go to the Data tab>> click Edit Links tool.
- In the Edit Links window, click the Startup Prompt button.
- In the Startup Prompt dialog box, select Don’t display the alert and update links and click OK.
- Then click on the Close button in the Edit Links window.
- Now, whenever you open the target file it will automatically update according to the source file values.
How to Update Excel Links
1. Update Excel Links Without Opening File
Consider the following workbooks. The Update Excel Link workbook is linked with the source file Physics Marks.
If you change the source file “Physics Marks”, the target file “Update Excel Link” doesn’t change.
Apply the following steps to update links here.
Steps:
- To update the value, open the target file “Update Excel Link” >> go to the Data tab >> select Edit Links tool.
- In the Edit Links dialog box, choose the source file and then click the Update Values button.
- This will change the Unknown status into OK. Finally, click the Close button.
This will update the linked values in the target Excel workbook.
Read More: Update Links Without Opening File
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.
Apply the following steps.
Steps:
- Open the “Change Excel Link” Workbook >> go to the Data tab >> click Edit Links tool.
- Select the source file and click the Change Source button.
- After clicking the Change Source button, you will get a window where you can select the new source file. Select the new source file and click OK.
Click the image for a detailed view
- As you can see, the source file has changed. Finally, click the Close button.
This will update the values in the target file according to the new source file.
How to Update Hyperlink to a Web Address in Excel
In the previous sections, we discussed how to update links to other workbooks. But sometimes we may need to update hyperlinks 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/ address, we have hyperlinked to https://exceldemy.com/forum/ address.
Apply the following steps to update the hyperlink here.
Steps:
- Select cell B5 >> right-click on your mouse >> select the Edit Hyperlink command.
- After selecting the Edit Hyperlink button, you will get a dialog box like the following.
- Go to 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.
Click the image for a detailed view
- After clicking the OK button, the link in cell B5 will get updated.
How to Break Source Links in Excel
Consider the following linked workbooks: the Physics Marks workbook and the Update Excel Link workbook. Here, we will break the links between these workbooks.
Following steps the steps below to break links in Excel.
Steps:
- First, open the Update Excel Link workbook. Then, go to the Data tab >> click the Edit Links tool.
- In the Edit Links window, select the source workbook and click the Break Link button.
- After clicking the Break Link button, you will get an alert like the following. Confirm breaking links by clicking the Break Links button.
- This will break the link with the source workbook. Therefore no workbook is showing in the Edit links window. Finally, click the Close button.
Now, if you change the source workbook, no change will occur in the target workbook because we broke the link.
What Are the Things to Remember?
- Once you break links, you cannot undo it. So, it is wise to keep a backup copy of your workbook first.
- If you don’t change the settings to update the links automatically. You will get the alert notification. From there you can update it manually.
Frequently Asked Questions
1. Why is Excel asking me to update links?
When you open a workbook that is linked with another workbook in those cases Excel shows you a notification that is “This workbook contains links to other data sources”. If you update then it will update the file according to its source file.
2. How do you update linked Data in Excel?
To do this go to Data >> Edit Links >> Update Values. This will update the linked data.
3. What is the use of Edit links in Excel?
By using the edit links option 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 Workbook
Conclusion
Updating Excel links is critical for maintaining accurate and dependable data connections within workbooks. Whether you’re working with complex formulas, charts, or external data sources, keeping your links up to date ensures that your Excel files are up to date. By regularly maintaining and updating Excel links, you can ensure the integrity of your data, improve the accuracy of calculations, and enhance the overall usability of your spreadsheets.
Update Excel Links: Knowledge Hub
- Update Hyperlink in Excel Automatically
- Disable Automatic Update of Links
- [Fix]: Excel Automatic Update of Links Has Been Disabled
- [Fixed!] Excel Links Not Updating Unless Source Is Open
- [Fixed!] Excel Update Links Manually Greyed Out
<< Go Back To Linking in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
