Zehad Rian Jim
This tutorial will demonstrate how to update links in excel. Unnecessary links can create confusion and difficulty while dealing with lots of data or links. Removing the previous link, replacing the link, or adding a new link can ease your work. So, it is essential to update the links in excel from time to time accordingly.
Download Practice Workbook
You can download the practice workbook from here.
5 Easy Methods to Update Links in Excel
Our target is to update links in excel. We can fulfill the task in 5 suitable ways. If you follow the methods correctly, you should learn how to update links in excel on your own. The methods are:
1. Using Context Menu
In this case, our goal is to update the links excel file by using a simple right-click option in excel. The steps of this method are.
Steps:
- First, we have arranged a dataset like the below image.
- Next, right-click on cell C5 and select the Edit Hyperlink option.
- After that, the Edit Hyperlink dialog box will appear on the screen.
- Afterward, you can update the desired link in the Address section and Text to display section and press OK.
- Finally, you will get the desired result.
Read More: How to Update Links Without Opening File in Excel (4 Methods)
2. Use of Insert Tab
Now, we want to update the links excel file by using the Insert Tab. The steps of this method are.
Steps:
- At first, we arranged a dataset like the below image.
- Second, go to the Insert>Link options.
- Third, choose the Insert Link option in the Link option.
- Fourth, the Edit Hyperlink dialog box will appear on the screen.
- Fifth, you can update the desired link in the Address section and Text to display section and press OK.
- Last, you will get the desired result.
3. Applying VBA Code
We can update links in excel by using the VBA code. We have to follow the below steps to learn this whole method.
Steps:
- To begin with, we have arranged a dataset like the below image.
- Next, press the Alt+F11 options to open the VBA window and got o Insert > Module options.
- In addition, insert the following code in the window.
Sub EditHyperlinks()
Dim Sheet As Worksheet
Dim xplink As Hyperlink
Dim xPast As String, xChanged As String
xTitleId = "EditHyperlink"
Set Sheet = Application.ActiveSheet
xPast = Application.InputBox("Former text:", xTitleId, "", Type:=2)
xChanged = Application.InputBox("Changed text:", xTitleId, "", Type:=2)
Application.ScreenUpdating = False
For Each xplink In Sheet.Hyperlinks
xplink.Address = Replace(xplink.Address, xPast, xChanged)
Next
Application.ScreenUpdating = True
End Sub
- Furthermore, in the EditHyperlink dialog box insert the link you want to update in the Former text option and click OK.
- Then, update the file accordingly in the Changed text option and press OK.
- Finally, you will get the desired result.
Read More: [Fixed!] Excel Links Not Updating Unless Source Is Open
4. Editing Broken Links
If any of the links aren’t working properly because of the broken links then we can fix it by following the below steps.
Steps:
- Firstly, we have arranged a dataset like the below image.
- Secondly, right-click on the desired cell and select the Edit Hyperlink option.
- Thirdly, the Edit Hyperlink dialog box will appear on the screen.
- Fourthly, you can update the proper link in the Address section and the proper name in the Text to display section and press OK.
- Lastly, you will get the desired result.
5. Modifying String Hyperlinks
We want to modify the string hyperlinks by following the below steps.
Steps:
- At first, we arranged a dataset like the below image.
- Next, select any cell of the dataset and double-click on the cell.
- Last, you will get the desired result.
How to Update Links Automatically in Excel
In this case, our goal is to update links automatically in excel. The steps of this method are.
Steps:
- At first, we arranged a dataset like the below image.
- Second, select a certain range and give the range a proper name(in this case Linklist).
- Third, go to the new worksheet and select any cell of the new worksheet and press right-click on the cell.
- Then, select the Link option.
- Forth, the Insert Hyperlink window will open on the screen.
- Next, go to Place in This Document > Defined Names > Link lists options and press OK.
- Last, you will get the desired result. If you make any change in the previous worksheet range in this worksheet, this change will be updated and linked automatically.
Read More: How to Disable Automatic Update of Links in Excel (3 Easy Ways)
Things to Remember
- The first method is the most efficient and easiest way to update links in excel.
- In the case of using the VBA method, the name won’t change but the hyperlink will be changed.
- If only the links are not working properly, only then use the fourth method.
Conclusion
Henceforth, follow the above-described methods. Hopefully, these methods will help you to update links in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our level best to solve the problem or work with your suggestions.
