30 Excel Functions in 30 Days: 28 – HYPERLINK

Icon30DayYesterday, in the 30XL30D challenge, we replaced text with the SUBSTITUTE function, and used it to create flexible reports.

For day 28 in the challenge, we’ll examine the HYPERLINK function. Instead of manually creating hyperlinks, with the command on the Excel Ribbon, you can use this function.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the HYPERLINK information and examples, and if you have other tips or examples, please share them in the comments.

Function 28: HYPERLINK

The HYPERLINK function creates a shortcut that opens a document stored on a computer, network server, intranet, or Internet.

Hyperlink00

How Could You Use HYPERLINK?

The HYPERLINK function can open a document, or jump to a specific location, so you can:

  • Link to location in same file
  • Link to Excel file in same folder
  • Link to website

HYPERLINK Syntax

The HYPERLINK function has the following syntax:

  • HYPERLINK(link_location,friendly_name)
    • link_location is the text string for the location where you want to go.
    • friendly_name is the text you want displayed in the cell

HYPERLINK Traps

If you have trouble creating the correct location string for the HYPERLINK function, manually insert a link with the Hyperlink command on Excel’s Ribbon. That should show you the correct syntax, then recreate that in your link_location argument.

Example 1: Link to location in same file

There are several different ways to create the text string for the link_location argument. In the first example, the ADDRESS function returns the address for row 1, column 1, on the sheet that is named in cell B3.

The pound sign (#) at the start of the address indicates that the location is within the current file.

=HYPERLINK(“#”&ADDRESS(1,1,,,B3),D3)

Hyperlink01a

You could also use the & operator to construct the link location. Here, the sheet name is in cell B5 and the cell is in C5.

=HYPERLINK(“#”&”‘” & B5 & “‘!” & C5,D5)

Hyperlink01b

For a link to a named range in the same workbook, just use the range name as the link location.

=HYPERLINK(“#”&D7,D7)

Hyperlink01c

Example 2: Link to Excel file in same folder

To create a link to another Excel file, in the same folder, just use the file name as the link_location argument for the HYPERLINK function.

For files that are up a level or more in the hierarchy, use two periods and a backslash for each level.

=HYPERLINK(C3,D3)

Hyperlink02

Example 3: Link to a website

You can also link to website pages with the HYPERLINK function. In this example, the URL is constructed from text strings, and the website name is used as the friendly name.

=HYPERLINK(“http://www.” &B3 & “.com”,B3)

Hyperlink03

Download the HYPERLINK Function File

To see the formulas used in today’s examples, you can download the HYPERLINK function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the HYPERLINK Video

To see a demonstration of the examples in the HYPERLINK function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Open Files with Excel HYPERLINK Function


_____________

You may also like...

7 Responses

  1. Kurt says:

    Crazy: Using excel 2003 (running on xp) internetexplorer needs to be your default-browser otherwise you get an obscure error-message

    Dont know if this is a requirement on excel2007/2010 too

  2. OmarF says:

    Okay, that # sign was my key missing piece. I couldn’t believe how hard it was to construct a hyperlink to jump within the spreadsheet. I used the following formula to build the filename part of the hyperlink:

    =MID(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1)),FIND(“]”,CELL(“filename”,A1))-FIND(“[“,CELL(“filename”,A1))+1)

    It just seemed so ridiculous. Now I know it was!

  3. Reuvain says:

    Another useful feature of the HYPERLINK function is to send an email using the mailto protocol. For example,
    HYPERLINK(“mailto:myaddress@gmail.com”,”Email me”).
    To pre-fill more fields in the email you can add those to the hyperlink as follows.
    HYPERLINK(“mailto:ErnieAndBert@gmail.com?subject=Sesame Street&body=Have you seen Cookie Monster?”,”Send E-Mail”)
    See Chandoo’s great video tutorial for designing a birthday reminder template.

  4. Luke Wisbey says:

    Extending Reuvain’s point re: use of HYPERLINK for generating email (useful where mail clients are unknown – ie VBA precluded)

    There are obvious constraints re: attachments, auto send and 255 char limitation

    Formatting can be achieved via HEX: http://www.pcguide.com/res/tablesASCII-c.html

    =HYPERLINK(“mailto:someone@somewhere.com?subject=Test%20Email&body=Hello%0A%0ARegards,%0AMe”,”Generate E-Mail”)

    On an aside it’s also worth being aware of the fact that a cell containing a link generated via Hyperlink function does not invoke the FollowHyperlink event in VBA.

  5. Reuvain says:

    This is the link to Chandoo’s video tutorial that I mentioned above.

  6. Contextures Blog » 30 Excel Functions in 30 Days: Conclusion says:

    […] 30 Excel Functions in 30 Days: 28 – HYPERLINK […]

Leave a Reply

Your email address will not be published. Required fields are marked *