Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Drop-Down List of Hyperlinks.

Drop-Down List of Hyperlinks

Written by Allen Wyatt (last updated July 13, 2023)
This tip applies to Excel 2007 and 2010


8

Greg knows that he can use data validation to create a drop-down list of choices for a particular cell. (How to do this has been covered in other ExcelTips.) He wonders if it possible to create a drop-down list of hyperlinks and, further, have those hyperlinks remain "hot" when they are selected.

The short answer is no, you can't do that directly. You can, however, make whatever you select in the drop-down list become active. This behavior is a bit kludgy in Excel, however. Follow these general steps:

  1. Select a URL from your drop-down list. (The list created using data validation.) The URL now appears in the cell.
  2. Double-click the URL that appears in the cell. Excel lets you start to edit the hyperlink.
  3. Press Enter. The URL now becomes active ("hot").
  4. Click the URL one more time to follow to that link.

As you can tell, these steps are a bit awkward, and they become more so if you want to select a different URL from the drop-down list. (You must first right-click and remove the hyperlink and then do the above steps again.)

Perhaps a better solution is to use a workaround that relies on the HYPERLINK function to refer to whatever is selected in the drop-down list. For instance, if you have your data validation drop-down list in cell A1, then you might put the following formula in cell B1:

=HYPERLINK(A1, "Goto Link")

The second parameter in the function is optional; it provides the text you want displayed in the cell. (If you don't provide the second parameter, then the contents of A1 are repeated in B1—something you may not want to see.) When someone picks an option in the drop-down list, the link in cell B1 becomes active and will link to whatever was selected in A1.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11213) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Drop-Down List of Hyperlinks.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Inserting the Date and Time

Inserting a date and time in your document is a snap using the tools provided in Word. Just pick the command, then ...

Discover More

Cropping Graphics in a Macro

Excel allows you to easily paste graphics into a worksheet. Once added, you may want to quickly process the graphics by ...

Discover More

Controlling How Body Text is Displayed

When working in Outline view, you can control how Word displays the body text under each heading. You can specify that ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Specifying a Browser in a Hyperlink

Excel allows you to easily add hyperlinks to a worksheet. Click on it, and the target of the link is opened in a browser ...

Discover More

Pulling Apart a URL

Excel provides a good number of worksheet functions that can help you pick apart text strings in various ways. In this ...

Discover More

Adding a ScreenTip

If you want people to know something about a hyperlink you added to your worksheet, one way to help them is to use ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

2020-03-09 20:18:18

Andrew

Hello, I have created 10 different hyperlinks in excel and each hyperlink references a word document. I am attempting to create a dropdown list of these hyperlinks so that when I choose the item from the dropdown, I can double click on it and it will go directly to the word document.

When I create the drop down list (using data validation) the drop down list appears but all the items are no longer hyperlinked to the word document originally referenced.

Any help would be greatly appreciated


2017-03-07 13:09:41

Dick Harkins

Need to add one line of code at the beginning of JaSal's macro. That line is to clear the previous entry in cell "HyperLink_Selected".

His code will now look like:

Sub Whatever you name it()

Call Range("HyperLink_Selected").ClearContents
HyperLink_Index = Range("HyperLink_Selected")
If Range("HyperLinks_Table").Offset(HyperLink_Index - 1, 0).Hyperlinks(1).Name <> "" Then
Range("HyperLinks_Table").Offset(HyperLink_Index - 1, 0).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

End If

That's not all!

Next

In Format Control

enter the Input Range
enter the Cell Link
enter number of Drop Down lines

Now .. you're good to go

Best

Dick


2016-05-19 10:57:46

JaSal

Well it works, although as already mentioned, visualisation would have been helpful.
Anyway, thanks for the workaround.

The data validation dropdown list is positioned in cell A1 and a

=HYPERLINK(A1;"Goto Link")

in cell A2. The crucial point is to fill the list with the hyperlink_address part of the HYPERLINK-Function.
So the source of the list: in my case an entry looked like this:

[Test.xlsm]Sheet1!A100

This part just replaces the A1 in

=HYPERLINK(A1;"Goto Link")

to form

=Hyperlink([Test.xlsm]Sheet1!A100;"Goto Link")

This only works using the function. If you insert a hyperlink over the menu it won´t work.

Hope that helps.

Good Luck


2016-02-11 20:43:26

anonymous

including screenshots would be more helpful :)


2015-08-13 21:47:38

Neil Raine

The problem can be easily solved with a bit of VBA and using a drop down box. To set up:

1. Create a named range (n rows, 1 column) called "HyperLinks_Table" than contains the list of hyperlinks.

2. Create a named range (1 cell only) called "HyperLink_Selected". Leave this cell blank.

3. Add a ComboBox form control to your spreadsheet. Format the control (right click) to have the following values:
Input Range: HyperLinks_Table
Cell Link: HyperLink_Selected

4. Right click the ComboBox control and select the "Assign Macro" option, click new to create a new macro. Add the following lines to the macro:

HyperLink_Index = Range("HyperLink_Selected")
If Range("HyperLinks_Table").Offset(HyperLink_Index - 1, 0).Hyperlinks(1).Name <> "" Then
Range("HyperLinks_Table").Offset(HyperLink_Index - 1, 0).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End If

Now when you change the value in the ComboBox the hyperlink will be opened.


2015-08-13 21:37:55

robert

Mine did not work either !! Please explain..........


2015-06-24 14:54:43

Bob

I tried this but it didn't work. I really need to be able to make a drop down list that will link to elsewhere in the same worksheet. I have 2 drop down lists and both are needed for my purposes. I concatenate the results to another cell so the full text is in one cell, Then I want to have a "go" link that will take the user to the place in the document they just selected with the 2 drop downs. Any help would be appreciated.


2015-01-21 17:59:44

Bill McNamara

I have an Excel 2010 file that has a drop down menu with options in the drop down, to sort a-z and another line to sort z-a. It also has a search field. It also has a list that you can put a check-mark next to, so you can print different criteria from that list. I don't know if I am explaining myself very well. I tried to copy and paste a view of it in this block, but the block will not allow me to do that.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.