Create Dynamic Hyperlinks in Excel

Hyperlinks are Excel’s shortcut way to jump to the required data point in the same workbook, a different workbook, or an external URL.

While hyperlinks are mostly static links, here is the way you can use to create dynamic hyperlinks.

By Dynamic Hyperlinks, I mean links that change based on the selection (or any other user action)

Suppose I have the data set as shown below:

Dynamic Hyperlinks in Excel Data Set

This is the back-end data, and I have a summary sheet where I have drop-downs where the person can select the Month. The idea is to update the hyperlink with the selections so that it takes the user to the right cell when the hyperlink is clicked.

Something as shown below

Dynamic Hyperlinks in Excel Demo

Here to Create Dynamic Hyperlinks in Excel

  1. Create a data validation drop-down in Cell B3, with the source as the name of all months in the Data tab [Learn how to create a drop down list in Excel]
  2. Use the following formula in cell C3
=HYPERLINK("#"&"Data!B"&(MATCH(B3,Data!$B$3:$B$26,0)+2),"Click Here to See Data")
  1. That’s it!! Your Dynamic Hyperlink is Ready

How this works

  • # tells the formula to refer to the same workbook
  • Data!B is the reference of sheet name and column name
  • MATCH(B3,Data!$B$3:$B$26,0) gives the position of the matching month in the list. 2 is added to it as there are 2 data beginning from the third row. For example, in the case of January 2012, the Match formula returns 1, and adding two returns 3. Hence it refers to B3

This is an amazing trick that can come very handy in creating Excel dashboards.

Try it Yourself.. Download the file from here
Download File

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

6 thoughts on “Create Dynamic Hyperlinks in Excel”

  1. I have a unique need for a dynamic hyperlink but not related to text that you can match. I want to be able to enter R1C1 Coordinates in a cell and hyperlink to that cell within the same worksheet. I don’t believe it can be done with the standard hyperlink because the coordinates will vary, I think this needs to be created as a macro and assigned a button or some other form. Can you assist?

    Reply
  2. If we have excel in one folder with all linked files how we can move the folder to another place? When I do it I lost all links

    Reply
  3. Wow thanks, I didn’t know the HYPERLINK formula existed. The way it is used is almost exactly like an INDIRECT formula. I have now used it to link multiple different sheets with a formula rather than tediously linking to each sheet:

    =HYPERLINK(“#'”&$A4&”‘!A1″,”→”)

    Gives an arrow that when clicked takes you to cell A1 of the sheet named in A4… brilliant!

    Reply
    • Hi David, this is so close to what i search for. I want to do this. In a cell i use some functions and the result i got is “A22” . I need to build a Hyperlink to get me to Cell A22 in another sheet…

      Reply
  4. Thank you for this excellent tutorial! Your explanation made it easy for me to modify your formula for a very similar task.

    Reply
  5. I have removed the “#” in the formula and it did not work.
    Also, you said <> , If we want to refer some other workbook ?

    Can you explain it in detail ?

    Reply

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster