Using the Hyperlink Function in Excel

3 Flares 3 Flares ×

Hyperlinks can be used to link to other sheets, webpages or other files such as PDF’s from your Excel spreadsheet. If you are creating hyperlinks for many records though this will take a long time to set them up.

Excel provides a HYPERLINK function for creating hyperlinks in our spreadsheets. The real power behind this function is that it can be used to create dynamic hyperlinks.

We can create conditional hyperlinks by nesting them within an IF function, or create hyperlinks that can find the address to link to themselves by embedding Lookup or Text functions within them. This helps us to create automated and error resistant spreadsheets.

The HYPERLINK Function

The HYPERLINK function is written as;

=HYPERLINK(link_location, [friendly_name])

Link Location: This is the cell, file or webpage that you want to link to. A # symbol can be used when referencing a cell within the active workbook.

Friendly Name: This is the text used as the hyperlink text that users will click on. This information is optional, and if omitted the text used by the links destination will be used.

HYPERLINK Function Example

If you are creating a static hyperlink to another file you should use the Hyperlink button on the Insert tab. The HYPERLINK function is here for impressive dynamic links.

In this example we want to create a hyperlink in column C of our table of contents. The user can then click the hyperlink to jump to the sheet of the country entered in column B.

Dynamic links using the Hyperlink function

The formula below uses the CONCATENATE function to join together the hash symbol, the countries name from cell B3, and A1 preceded by the exclamation mark. By referencing cell B3, when copied, this function would take the user to the correct sheet.

=HYPERLINK(CONCATENATE(“#”,B3,”!A1″),”Go to Page”)

Watch the Video

4 thoughts on “Using the Hyperlink Function in Excel

  1. Hi , Great video on Dynamic Hyper links. I was wondering if you could help me with a different issue. I wish to create a hyperlink in excell to point to a word document, which is a simple task, but I wish to make it dynamic, so it points to a specific Heading or title of my choice.
    Hope to hear back form you

  2. 100000000 thanks for you… you are an angel… because of you I preserved a lot of time and effort… thank you my friend

Leave a Reply

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