How to Update Hyperlink in Excel Automatically (2 Ways)

We have a dataset containing brief information about some books, their writers, and hyperlinks where you can find these books to read. We will use some dummy hyperlinks to explain the topic.

How to Update a Hyperlink in Excel Automatically: 2 Ways

Method 1 – Using a Formula to Update Hyperlink Automatically in Excel 

We have two duplicate files where we keep the information about these books. The names of these files are Update Hyperlink Automatically and Update Hyperlink Automatically 2. We want to create a system where we change the hyperlinks in the file named Update Hyperlink Automatically and the hyperlinks in Update Hyperlink Automatically 2 automatically change.

Steps:

The following figure refers to the Update Hyperlink Automatically file.

Here’s the Update Hyperlink Automatically 2 file in the following picture. This file has the price information about these books.

• Remove the hyperlinks in the file Update Hyperlink Automatically 2.

• Use the following formula in cell D5 of the Update Hyperlink Automatically 2.

This formula will store the hyperlinks of the Update Hyperlink Automatically file.

• Press Enter and you will see the hyperlinks.

• The websites that sell Oliver Twist, War and Peace and Frankenstein happen to change their site. We will change the names of these websites in the Update Hyperlink Automatically file.

• The corresponding hyperlinks in the file Update Hyperlink Automatically 2 are updated.

Note: If your hyperlinks don’t change automatically, you may face this dialog box when updating the source file (Update Hyperlink Automatically).

• Go to File of Update Hyperlink Automatically 2.

• Choose Options.

• Select Trust Center and choose Trust Center Settings… from Excel Options.

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

• 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. Close the window.

Read More: How to Update Links Without Opening File in Excel

Method 2 – Updating the Hyperlink Automatically by Defining a Name

Steps:

• Select the hyperlinks and give this range a name. We named the range as LinkLists.

• Select any cell of the new sheet and right-click on it.
• Choose Link.

• The Insert Hyperlink window will show up. Select Place in This Document and choose 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.

• Go to the updated links sheet and click on the LinkLists hyperlink.

• This will navigate to the updated LinkLists in the ‘defined name’ sheet.

Read More: How to Disable Automatic Update of Links in Excel

Practice Section

We’re giving you the dataset of this article so that you can practice these examples on your own.

Download the Practice Workbooks

Related Articles

• [Fixed!] Excel Links Not Updating Unless Source Is Open
• [Fix]: Excel Automatic Update of Links Has Been Disabled

<< Go Back To Update Excel Links | Linking in Excel | Learn Excel