How to Update Hyperlink in Excel Automatically (2 Ways)

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


Download Practice Workbook


2 Ways to Update Hyperlink in Excel Automatically

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.

how to update hyperlink in excel automatically

  • Now to make the system to 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

how to update hyperlink in excel automatically

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.

how to update hyperlink in excel automatically

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

how to update hyperlink in excel automatically

  • To get rid of this problem, go to File of the Update Hyperlink Automatically 2

  • Then choose Options.

how to update hyperlink in excel automatically

  • Select Trust Center >> Trust Center Settings… from Excel Options

  • Then choose External Content and select the marked options in the following picture.

how to update hyperlink in excel automatically

  • After that, select Edit Links from Queries & Connections section of the Data Tab.

  • A dialog box will appear. Select Startup Prompt from the dialog box.

how to update hyperlink in excel automatically

  • Choose Don’t display the alert and update links from the Startup Prompt

.

  • You will be back at Edit Links Just close the window.

how to update hyperlink in excel automatically

Now you are all set. You will be able to update hyperlinks automatically from now on.

Read More: [Fix]: Excel Automatic Update of Links Has Been Disabled


Similar Readings


2. Updating Hyperlink Automatically by Defining Name

We can also update hyperlink 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.

how to update hyperlink in excel automatically

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

how to update hyperlink in excel automatically

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

how to update hyperlink in excel automatically

  • Type the information about the new book.

  • Now go to the updated links sheet and click on the LinkLists Hyperlink.

how to update hyperlink in excel automatically

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 Edit Hyperlink in Excel (5 Quick & Easy Ways)


Practice Section

Here I’m giving you the dataset of this article so that you can practice these examples on your own.

how to update hyperlink in excel automatically


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.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo