How To Use Hyperlinks With Data Validation In Excel

In this tutorial we’ll take a look at several ways that you can have a Data Validation drop-down list that contains either URL links to web pages or hyperlinks to other locations in your workbook.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

The first option involves having a list of URLs in a drop-down list from which you can choose and then have it launch a web browser to that page:

81615-1

The steps to do this are:

1) Create your data validation drop-down list
2) Select the one from the list you want to launch
3) While that cell is still selected, hit the “F2” function key – this puts the cell in Edit mode
4) Hit the “Enter” key
5) Click on the link and it will launch the web page

Note that when I hover over the link, it shows the destination:

81615-2

However, when I choose another link, it still shows it as the previous destination, in this case “google.com”:

81615-3

The key with this method is that you need to hit “F2” and “Enter” after each selection.

Now, the second option is a bit different in that, instead of converting the drop-down list selection itself into the hyperlink, we use the HYPERLINK function below the selection to “launch” that link:

81615-4

The syntax for this function is: =HYPERLINK(link_location,[friendly_name])

In our scenario, the link location points to cell F1 where our drop-down list is, and the friendly name is “Launch”.

Once you select the choice from the drop-down list, click on the cell with the hyperlink function (cell F2 in our file) and it will launch that web page.

Our last scenario is a bit different in that, instead of using the drop-down list for options to web pages, we want to use it to go to various locations in our workbook. In our case, we have three choices: go to cell A1 in either Sheet1, Sheet2, or Sheet3. The sheet names are the choices in our drop-down list:

81615-5

We are going to use a similar method to Option #2 above by using the HYPERLINK function below the list to launch the link. I’ve done it two ways:

81615-6

Both use the friendly name as “Click Here”, but the link location are slightly different. In the top formula, I’ve entered:

=HYPERLINK(“#”&A1 & “!A1″,”Click Here”)

Here I’ve concatenated the # sign (or octothorpe) with cell A1 which is the drop-down list identifying the sheet name, then concatenating that with “!A1” which adds an exclamation point to indicate to Excel that the name chosen from the drop-down list is a sheet name, followed by the cell reference that I want that link to go to on that sheet. When resolved, the formula will look like this:

=HYPERLINK(“#Sheet1!A1″,”Click Here”)

The only difference with in the lower formula is that I’ve replaced the octothorpe with the actual workbook name:

=HYPERLINK(“[Hyperlink Sheets.xlsx]”& A1 & “!A1″,”Click Here”)

Which will resolve to:

=HYPERLINK(“[Hyperlink Sheets.xlsx]Sheet1!A1″,”Click Here”)

Clicking on either “Click Here” cell will take you to cell A1 of the selected worksheet.

And that’s how you can use a Data Validation drop-down list with hyperlinks in Excel!

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

No comments yet.

Leave a Reply