Creating Hyperlinks

Here are two ways to create hyperlinks in your spreadsheet.

Menu: Insert>Hyperlink will bring up the Insert Hyperlink dialog box. There you can type in a URL, a file name, or point to location in your spreadsheet.

Function: The HYPERLINK worksheet function can also be used to create hyperlinks. To use this method, just type the function in a cell:

=HYPERLINK(“www.dicks-clicks.com”,”My Web Site”)

The URL is the first argument. The optional second argument is a “friendly name” that will appear in the cell. If you omit the second argument, the cell will show the URL.

Posted in Uncategorized

52 thoughts on “Creating Hyperlinks

  1. I like using hyperlinks for quick navigation around a worksheet or workbook if I’m working with a large file. Just insert them at strategic places and say goodbye to scrollbars (well, maybe not entirely)

    You can use a “friendly name” for this too.

  2. Ok, I have just learned how to do hyperlinks so I am not sure what I am doing wrong.

    I am trying to add a hyperlink into one of my excel spreadsheets from another excel document and it says it can’t open specified file, why?

    It works great with my word document but not my excel document.

    Can you please help me with this.

    Thank you in advance.

    Heather

  3. When you create hyperlinks, you can locate them in an endless variety of places on your worksheet. But what happens if you want to remove them and you don’t remember where they are? Excel doesn’t tell you. This routine helps you to find them:

    Sub find_hyperlinks()

    Dim r As Range
    Set r = Range(“a1:cv1500?)
    For Each c In r
    If c.Hyperlinks.Count > 0 Then
    msgbox c.address
    End If
    Next c

    End Sub

    To get rid of the hyperlink, right click, remove, or in Excel ’97 via the edit menu – hyperlinks edit hyperlink – remove.

    If you created hyperlinks to other Excel documents, your file will always open with a message asking “This file contains links to other documents – do you want to update them? This is a nuisance, because you’ll have to answer this question perpetually each time your file opens. In Excel ’97 there is no way to get rid of it. Luckily, as from Excel 2002, the Edit – Links dialog has a “Break Links” option.

  4. Ok, what I want to do is assign a macro to a hyperlink, so that if a user selects that cell with that hyperlink in it, it will run a macro and pop up a chart, I don;t want to use buttons because there could be many such cells. Any ideas as to how I can accomplish this ??

    Thank you.
    ercan

  5. Ercan –

    Independent of the hyperlink, put a SelectionChange event in the worksheet code module. To do this, right click the sheet tab, and select View Code. In the code module that pops up, select Worksheet from the top left dropdown, which puts the following procedure into the code module:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    End Sub

    You then adjust what’s between the Sub and End Sub to get what you want:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Select Case Target.Address
    Case “$A$1?
    ‘ carry out action for Cell A1
    Case “$A$2?
    ‘ carry out action for Cell A2
    ‘ etc.
    End Select
    End Sub

  6. I have another question, this time regarding Excel Chart Events.

    I want to be able to click anywhere on a chart and drag over a region on the x-axis , to resize the chart to plot only that region, a bit like kike zooming in.

    Say for example , a chart shows prices of a stock over one day , if I click on the chart at a point that corresponds to 10:30 and drag the mouse while holding keeping the left mouse button pressed and releasing it when I am at 13:00 pm , the chart automatically re-draws it self with the x-azis ranging from 10:30 to 13:00 pm ….

    Appreciate the help.

  7. A question for the Excel experts. I have a workbook that that displays links for documents that are scattered throughout a network. Is there any way display information in Excel about those hyperlinked documents such a creation date or author. I’m really interested in being able to monitor when these files get changed or updated.

    Thanks,

    el parce

  8. When I try to create a hyperlink in an Excel workbook cell, I can’t make a partial selection of text for the link; all the text in the cell is automatically hyperlinked–very ugly!

    Thanks!

  9. Question: I have an Excel workbook but when I select Insert and attempt to click on the Hyperlink selection in the dropdown menu it is not available for me to select. If I create a new document I can do so/ for some reason I cann access this in the workibook I have ben working with.
    Help

    Jewan

  10. I would like to create a hyperlink based on what’s in a cell. Thus if a cell has value “x” in it, i want it to link to a hyperlink which is “http://www.value_x_somethingelse.net” I

    don’t know how to do this when there’s something after the “x” thus i can make it work if the link is “http://www.value_x_.net” but not when there’s something after the “x” ie “http://www.value_x_somethingelse.net”

    Any advice?

  11. I have the OPPOSITE problem. I have a spreadsheet of hyperlinks where the friendly name is displayed. I want to remove the friendly name, so that the hyperlink is what is in the cell.

    Is there a way to do this without editing each cell?

  12. Elizabeth,

    You could do something like this:

    Public Sub RemoveFriendlyNames(Optional rwsTarget As Worksheet = Nothing)
        Dim hyp As Hyperlink
       
        If rwsTarget Is Nothing Then Set rwsTarget = ActiveSheet
       
        For Each hyp In rwsTarget.Hyperlinks
            hyp.TextToDisplay = hyp.Address
        Next hyp
    End Sub

    You can either call it without arguments or pass in a reference to the worksheet from which you wish to remove friendly names.

  13. I have a spreadsheet which is a listing of hundreds of urls which are friendly names (non-linked). Does anyone know the easiest way to turn them into hyperlinks (going to the same url as their friendly name)? Do I have to do them one by one?

  14. Daniel,

    You can use the HYPERLINK function in Excel. It takes two arguments:
    =HYPERLINK(URL, Friendly Name)

    An example would be:
    =HYPERLINK(“http://www.datapigtechnologies.com”,”DataPig”)
    The cell would show DataPig, but would go to the correct

  15. I forgot to mention; you can use a cell reference in any of the arguments.
    For instance, if you already have friendly names in Column B, you can do something like this:

    =HYPERLINK(“http://www.datapigtechnologies.com”,B1)

    Then you can copy down.

  16. What about creating a hyperlink in a cell to link it to a word document. I tried that and it worked, but I just could not “save” the hyperlink. I have to edit them all over agai when I open my excel file. Can anybody help me please? Thanks.

  17. Hi guys, I’m loathe to ask for help here, but this blog is really helpful. How do i use the contents of a cell as the website address? I have a macro to create a variable filename/hence variable address online to go view it. this is in A5. I want to link to this in B5. I have tried with a cell reference but this only links to that cell. cheers :)

  18. Hi

    How would you add instructions to a hyperlink to make it open in a new window when in a webpage – ie “_blank” =HYPERLINK(“http://www.dicks-clicks.com”,target=????)

    Thanls

  19. Is it possible to pass arguments to the hyperlinks within excel. The hyperlink in excel will invoke a batch file with some arguments. Is this possible?

  20. Hi,

    My problem is the following: I have an Excel WB and in one of the columns I have countries listed, where the different projects take place (1 country per cell). I would like to create hyperlinks to the website of each country’s Wikipedia site, where the address is http://en.wikipedia.org/Wiki/“Country name”. How can I do this, withouth creating the links one-by-one?

    Thanks a lot for the help!

  21. Hi I have a an issue with a workbook I am working on. It is a list of orders that I am generating from filling in a master sheet. As the master is completed it is “Submitted” and a separate file in created and saved based the order number (as entered). In addition a new line is created on the directory page and some basic information is copied from the new file. I want to add a hyperlink to the newly created file from within the newly created line. The hyperlink will always reference a file with the same name as the value returned in say cell C7 (as calculated). Any help would be greatly appreciated.

  22. hi ! i would like to know if i can create a link independant of the location in excel. this linking document id a word file. please let me know. thanks in advance.

  23. Great blog folks. Here’s a curly one. I want to create an email hyperlink which generates an email with a populated subject line based on cell content.

    i.e the link location would be mailto:whatever@whatever.com, the friendly name would be a Meeting date and location and the email subject line would be the same as the same text as the friendly name.

    Is this possible? Many thanks.

  24. Many thanks Dick,

    This partially works in that the subject heading is there but it won’t incorporate cell content into the email subject heading i.e. if you I insert the function =HYPERLINK(“mailto:whatever@whatever.com?subject=DB2?,DB2) where cell DB2 = “Meeting 1/14/10 at Chotchkies”.

    What happens here is that the friendly name is correct in that it shows “Meeting 1/14/10 at Chotchkies” but the email subject heading just shows DB2.

    P.S. you may be able to get some concept of the size of spreadsheet I’m working off by the cell reference!

    Cheers
    Warrick

  25. I have a workbook with multiple sheets in it. I am forcefully hiding those sheets (not visible within sheet unhide menu option) and unhiding them with a VBA code.

    Thru a cell/hyperlink in a particular sheet, i want to

    (a) First – Unhide the target Sheet
    (b) Then – Go to a particular cell reference.

  26. Using excel 2007 I’m running into an issue where a hyperlink used on the false side of a if is creating a hyperlink when the if evaluates to false.

    Example:
    =if($A$1=0,”No Link”,hyperlink(“http://test.com”,”test”))

    Will populate the cell with a link called “No Link” when cell A1 = 0

    I think it’s an excel bug, any ideas to get around this?

    Thanks in advance
    Tim

  27. Hell, can anyone help with the following, please?
    My folder (folder A) is part of a system used at work by many people, so it’s full name is Disk C/folder 1/folder 2/folder 3/folder 4/folder A.
    What I have in folder A is an Excel spreadsheet plus three other folders B,C,D each of which in turn contain several Excel spreadsheets plus in turn folders (say X,Y,Z) containing more spreadsheets.
    In all the spreadsheets I have hyperlinks to other spreadsheets which are in A,B,C etc.
    These work fine – for example if I want a hyperlink in spreadsheet x which is in Folder X to a spreadsheet b in folder B, then the actual hyperlink is Disk C/folder 1/folder 2/folder 3/folder 4/folder A/folder B/spreadsheet b.
    The problem is that I don’t have control over folders 1,2,3 and 4.
    So if someone comes in and changes the name of folder 2, for example, all my hyperlinks will fail, and will have to be recreated. Is there any way to protect from this please?

  28. Hi

    The code below works ok. However I have a column (say C)with email addresses where I want the hyperlink on each one, and copy formula down. Need to replace the email address with something like mailto:C20?
    Not quite working. Any help ?

    Jon Peltier says:
    January 20, 2010 at 7:05 pm

    Warrick:

    Try this:

    =HYPERLINK(“mailto:whatever@whatever.com?subject=”&DB2,DB2)

  29. Hi all.
    The goal is to filter worksheet data, using arguments passed from an hyperlink.
    Can I use an hyperlink to achieve this?

    Many thanks

  30. question – why does compiler choke on this?

     ‘name of destination sheet
       Const dSheet = “graphtab”    ‘destination sheet
       Dim copyToRow As Long    ‘row counter
       Dim insheet As Worksheet    ‘current worksheet

            dSheet.Range(“A” & copyToRow).Hyperlinks.Add _
            Address:=“#'” & insheet.Name & “‘!A61”, _
            Anchor:=Worksheets(dSheet).Range(“A” & copyToRow), _
            TextToDisplay:=insheet
  31. roy:

    Const dSheet = “graphtab”

    creates a String constant. I don’t think it’s possible to have an object constant. You can have a Variant, but I don’t think it will let you put an object in there. You need to make it a variable, like

    Dim sh as Worksheet
    Const sSHEETNAME As String = “graphtab”

    Set sh = Sheets(sSHEETNAME)
    sh.Range(“A1”…)

  32. I am not a programmer and dont have the concept ‘object’ in contrast with variant or deviant for that matter. I’ve made some changes however compiler still does not accept destSheet;
    Worksheets destSheet! & “A” & destRow,

      Sub AAperfmon()

    Setup:
        Dim destSheet As Worksheet          ‘name of destination sheet
       Dim srcSheet As Worksheet           ‘current sheet – with data
       Dim whatToCopy As Range             ‘list of cells to copy
       Dim destRow As Long                 ‘counter synchronizing srcSheet and row in destSheet
       Dim cnt As Long                     ‘loop limit to number of worksheets in workbook
                                           ‘MsgBox “Start”
    Start:
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Application.DisplayStatusBar = True
        Application.CutCopyMode = True
                                            ‘ initialize
       destSheet = ActiveWorkbook.Sheet.graphtab
        cnt = ActiveWorkbook.Sheets.Count
        destRow = 2

    Skip:
     For Each srcSheet In Worksheets
        If destRow = (cnt + 3) Then GoTo Cleanup:
            If srcSheet.Name = “v.q.” & “*'” Then   ‘ if vdbench file skip
               destRow = destRow + 1
                Application.StatusBar = “Skipping”
                GoTo Skip:
            End If
    Main:                                   ‘exclude destination sheet
       If srcSheet.Name <> destSheet Then
                                            ‘hyperlink from destSheet column A current row to srcSheet column A Row 61
                   Worksheets destSheet! & “A” & destRow, _
                    .Hyperlinks.Add, _
                    Anchor:=destheet! & “A” & destRow, _
                    Address:=“”, _
                    SubAddress:=srcSheet.Name & !A61, _
                    TextToDisplay:=srcSheet.Name
                                            ‘specify cells to copy
           Set whatToCopy = srcSheet.Range(“F62,T62,Z62,AF62,AL62,AR62,AX62”)
                whatToCopy.Copy
                                            ‘copy cell value not formula
               Worksheets destSheet.Range(“B” & destRow), .PasteSpecial
                Worksheets destSheet.Range(“B” & destRow), .xlPasteValues
                                            ‘increment sheet pointer
               destRow = destRow + 1
                                            ‘adjust column to widest entry
               Worksheet destSheet.Range(“B” & destRow), .EntireColumn.AutoFit
         End If
                Application.StatusBar = srcSheetName & “complete”
     Next                                   ‘get next sheet
    Cleanup:
         End If
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Application.CutCopyMode = False
        Application.StatusBar = False
        Worksheets(destSheet).Active
       
    End Sub

  33. If I understand your code correctly, this is how I would write it.

    Sub AAperfmon()

        Dim shDestination As Worksheet
        Dim shSource As Worksheet
        Dim lDestRow As Long
        Dim lSheetCnt As Long

        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Application.DisplayStatusBar = True

        Set shDestination = ActiveWorkbook.Sheets(“graphtab”)
        lSheetCnt = ActiveWorkbook.Sheets.Count
        lDestRow = 2

        For Each shSource In ActiveWorkbook.Worksheets
            If shSource.Name <> shDestination.Name And Not shSource.Name Like “v.q.*” Then
               
                ‘hyperlink from destSheet column A current row to srcSheet column A Row 61
               shDestination.Hyperlinks.Add _
                    Anchor:=shDestination.Range(“A” & lDestRow), _
                    Address:=“”, _
                    SubAddress:=shSource.Name & “!A61”, _
                    TextToDisplay:=shSource.Name

                shSource.Range(“F62,T62,Z62,AF62,AL62,AR62,AX62”).Copy
                shDestination.Range(“B” & lDestRow).PasteSpecial xlPasteValues

            End If
           
            lDestRow = lDestRow + 1
            Application.StatusBar = shSource.Name & ” complete”
           
        Next shSource
       
        shDestination.Range(“B” & lDestRow).EntireColumn.AutoFit
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Application.CutCopyMode = False
        Application.StatusBar = False
        shDestination.Activate
       
    End Sub

  34. I want to create a link in an email that, when clicked, would open an excel file and take the user to a specific sheet and cell address. I have been led to believe that

    L:MyExcelDirectoryMyBook.xls#Sheet1!A1

    is the correct syntax (where L: is a network drive to which users are mapped) – using the pound sign with the standard sheet and cell reference.

    Doesn’t work – any thoughts on what a working context should be?

  35. Gregg: That # option only works within Excel, not from external sources. At least as far as I know. I don’t think you can get to a specific spot in a workbook from an external link.

  36. I’m inserting hyperlinks in a spreadsheet so that customers can click the price/description of an item and it will take them to the appropriate website. The links work fine when I save it on the computer, but the file is too big to email so I had to save it to a CD. When I reopen the file on the CD, all of the hyperlinks are gone. Anyone know how to fix that?

  37. How to create the friendly name of the cell to hyperlink automatically to the file name in a folder.

  38. I simply needed to thank you so much yet again. I am not sure the things that I would’ve sorted out without the actual points documented by you on my subject. It has been a very terrifying situation in my opinion, but understanding a new specialized avenue you dealt with it took me to jump with joy. I’m happy for this assistance as well as sincerely hope you know what an amazing job you happen to be undertaking educating other individuals via your blog. Probably you haven’t got to know any of us. Read more on my site accutane action class suit or buy cheap isotretinoin online

  39. Hi

    i have this problem, i am a new beginner in hyperlink. i am trying to organise the data in my excel so that it is easy for my boss to read whenever she comes to check my computer. my problem is that i have client statements that are sorted on a monthly basis at the end of the month i have to report to my boss about the total earnings for the month. the statements are in one workbook across worksheets. i want to do a hyperlink in my masterfile to help me link to the other documents within the same workbook so that i can be able to add the total for the end of the month. can someone help me please? how can i do multiple hyperlinks at the same time within one workbook instead of doing it one by one? thank you in advance


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.