In this article, you will have some basic ideas on how to update Hyperlink in Excel automatically. Sometimes, you may need to update information from one Excel file to another. For that reason, you need to update Hyperlink automatically.
In the following image, we have a dataset containing brief information about some books, their writers, and hyperlinks where you can find these books to read. I will use some dummy hyperlinks to explain the topic.
How to Update Hyperlink in Excel Automatically: 2 Ways
1. Using Formula to Update Hyperlink Automatically in ExcelÂ
I have two duplicate files where I keep the information about these books. The names of these files are Update Hyperlink Automatically and Update Hyperlink Automatically 2. I want to create a system where I change the hyperlinks in the file named Update Hyperlink Automatically and the hyperlinks in Update Hyperlink Automatically 2 automatically change. As you know, any website can change its name so we need to update hyperlinks.
Let’s go through the description below.
Steps:
First, I want to show you the two files. The following figure refers to the Update Hyperlink Automatically file.
And you will see the Update Hyperlink Automatically 2 file in the following picture. This file has price information about these books.
- Now to make the system update hyperlink automatically, you need to remove the hyperlinks in the file Update Hyperlink Automatically 2.
- Now type the following formula in cell D5 of the Update Hyperlink Automatically 2.
='[Update Hyperlink Automatically.xlsx]update hyperlink'!$D$5:$D$10
This formula will store the hyperlinks of the Update Hyperlink Automatically file.
- Press ENTER and you will see the hyperlinks.
- Say, the websites that sell Oliver Twist, War and Peace and Frankenstein happen to change their site So we will change the names of these websites in the Update Hyperlink Automatically file.
- Simultaneously, you can see the corresponding hyperlinks in the file Update Hyperlink Automatically 2 are updated.
Thus you can easily update hyperlinks automatically by using the formula. You don’t need to do any manual editing.
Note: If your hyperlinks don’t change automatically, you may face this dialog box when updating the source file (Update Hyperlink Automatically).
- To get rid of this problem, go to File of the Update Hyperlink Automatically 2
- Then choose Options.
- Select Trust Center >> Trust Center Settings… from Excel Options.
- Then choose External Content and select the marked options in the following picture.
- After that, select Edit Links from the Queries & Connections section of the Data Tab.
- A dialog box will appear. Select Startup Prompt from the dialog box.
- Choose Don’t display the alert and update links from the Startup Prompt.
- You will be back at Edit Links. Just close the window.
Now you are all set. You will be able to update hyperlinks automatically from now on.
Read More: How to Update Links Without Opening File in Excel
2. Updating Hyperlink Automatically by Defining Name
We can also update hyperlinks in Excel by defining the name range of the Hyperlinks in the dataset. Let’s go through the procedure below.
Steps:
- Select the Hyperlinks and give this range a name. In this case, I named the range as LinkLists.
- We will navigate this range from another sheet. So we will create a hyperlink in a different sheet.
- Select any cell of the new sheet and right-click on it.
- Then choose Link.
- After that, the Insert Hyperlink window will show up. Select Place in This Document >> LinkLists (the named range)
- Click OK.
You will see the hyperlink in the new sheet.
- If you click on this link, it will navigate to the sheet where we defined LinkLists.
- Say you want to add a new book before War and Peace. Insert a new row above it.
- Type the information about the new book.
- Now go to the updated links sheet and click on the LinkLists Hyperlink.
This will navigate to the updated LinkLists in the ‘defined name’ sheet.
Thus you can update hyperlinks automatically by defining a name or named range.
Read More: How to Disable Automatic Update of Links in Excel
Practice Section
Here I’m giving you the dataset of this article so that you can practice these examples on your own.
Download Practice Workbooks
Conclusion
The bottom line is, that you will get to know about how to update hyperlink in Excel automatically like if we have similar information including Hyperlinks in two different Excel files. If we update the hyperlinks in one file, they will automatically appear in the other file or sheet. If you have any other ideas or if you have any feedback, please share them in the comment box. It will help me enrich my upcoming articles.
