Skip to main content

How to create drop down list with hyperlinks in Excel?

Author: Xiaoyang Last Modified: 2020-05-27

In Excel, adding drop down list may help us to solve our work efficiently and easily, but, have you ever tried to create drop down list with hyperlinks, when you choose the URL address from the drop down list, it will be open the hyperlink automatically? This article, I will talk about how to create drop down list with activated hyperlinks in Excel.

Create drop down list with hyperlinks by using formula

Create drop down list with hyperlinks by using combo box


Create drop down list with hyperlinks by using formula

There is no direct way to create drop down list with activated hyperlinks in Excel, but, you can insert a formula to activate the selected hyperlink from the drop down list, please do as this:

After creating the drop down list, please enter this formula: =HYPERLINK(D2,"Go To Website!") (D2 is the drop down list cell) in cell E2 which beside your drop down list cell, and press Enter key, then when you choose one URL hyperlink from the drop down list, and click the formula cell, the hyperlink will be opened.

doc drop down list hyperlinks 1


Create drop down list with hyperlinks by using combo box

Actually, you can also create a drop down list with hyperlinks by using combo box, and when you select a hyperlink from the combo box, it will be activated at once. Please do as follows:

First, please create two range names for your hyperlinks list and a linked cell.

1. Give your hyperlinks list a range name “Hyperlinks”, and name a blank cell “Linked_cell” which is the linked cell of the combo box value into the Name Box, see screenshots:

doc drop down list hyperlinks 2

doc drop down list hyperlinks 3

Second, create a combo box and format the control.

2. Click Developer > Insert > Combo Box, and drag the cursor to draw a combo box as you need.

doc drop down list hyperlinks 4

Tips: If there is no Developer tab in your ribbon, please read this article How to show/display developer tab in Excel 2007/2010/2013 Ribbon? to activate it.

3. After inserting the combo box, right click it, and choose Format Control, in the Format Control dialog box, click Control tab, and enter Hyperlinks and Linked_cell which are the range names you have created in step 1 into the Input range and Cell link text boxes separately, see screenshot:

doc drop down list hyperlinks 5

4. Then click OK button to finish the settings, go on right clicking the combo box, and choose Assign Macro from the context menu, in the Assign Macro dialog box, click New button, see screenshot:

doc drop down list hyperlinks 6

5. In the popped out Microsoft Visual Basic for applications window, please copy and paste the following code between the scripts, see screenshot:

VBA code: activate the hyperlinks from combo box:

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

doc drop down list hyperlinks 7

6. Then save this code, and now, when you choose one hyperlink from the combo box, the hyperlink will be opened automatically.


Demo: Create drop down list with hyperlinks in Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (3)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I used the formula as indicated, but when I click on the second hyperlink in the drop down list, it takes me directly to my last hyperlink on the list. It's only the first hyperlink that works, the rest of the drop down list only takes me to the last hyperlink. How can I fix this so that each hyperlink in the list takes me to the correct hyperlink requested?
This comment was minimized by the moderator on the site
Is there a possibility to make this available for more than one drop down list without making a new macro for every combo box?

If I have a dropdown list for lets say Charlie with all the employees reporting under him and we have Bryan with all the employees reporting under him in a dropdown menu and when clicking a name on the dropdown menu it hyperlinks to the required sheet or file etc.

In a case where there's only 2 - no problem, but when there's c130 it can become unpleasant to name each separately, create a command button with it's own macro assigned.

Hope I've explained it understandably
This comment was minimized by the moderator on the site
is there a way to make the second option work with hyperlinks to another cell in the worksheet? I basically have an enormous spreadsheet with options in different categories and subcategories and I want to select a sub categorie in a drop down list then the link brings me to the right place in the sheet. Is this possible?
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations