The HYPERLINK function builds a link from a cell value rather than a fixed address, so the link changes when the cell does: =HYPERLINK(B1,"Open") creates a clickable link to whatever web address sits in B1, with “Open” as the display text. Pair B1 with a dropdown and one cell becomes a link switcher.
To jump somewhere inside the same workbook, prefix the reference with a hash: =HYPERLINK("#"&B1,"Go") where B1 holds something like Sheet2!A1. To link to a named range, the same syntax works: =HYPERLINK("#"&"MyRange","Go").
Three things worth knowing. If the display-text argument is left out, the cell shows the raw address instead.
The link is only a formula result, so it won’t appear in Excel’s Edit Links or hyperlink list, and Ctrl+K won’t show it. And if the address is invalid Excel gives no warning until someone clicks — wrap it in =IFERROR(HYPERLINK(B1,"Open"),"No link") if the source cell might be blank.
To create a dynamic hyperlink where you can change the link that you have specified, you need to use Excel’s HYPERLINK function. This function allows you to specify a link to a web address or cell in the workbook.
In this tutorial, we will learn to write a formula for this.
Write a Formula to Create a Dynamic Hyperlink (Web Address)
You can use the below steps for this.
- First, enter the HYPERLINK function in a cell.
- After that, in the first argument, refer to the cell where you are going to update the hyperlink address, Let’s say cell B1.
- Next, in the second argument, enter a friendly name for the link to how in the link.
- In the end, enter the closing parentheses and hit enter.
=HYPERLINK(B1,"Hello")In cell B1, we have a drop-down list to change the web address. And the moment you change the link address in cell B1, it will also change the link in the formula.
Create a Dynamic Hyperlink for a Cell Address
In the same way, you can create a dynamic hyperlink for a cell in the workbook.
=HYPERLINK("#"&B1,"Hello")In the above formula, we have used the “#” with a combination of the cell reference of cell B1. Now, as soon as you change the cell address in cell B1 it changes the link in the formula.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.