The Excel HYPERLINK function creates hyperlinks to to other sheets, webpages or files such as PDF’s on your Excel spreadsheet.
The real power behind this function is that it can be used to create dynamic hyperlinks that are dependent upon cell values or results from formulas.
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 Excel HYPERLINK function is written as;
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.
Excel 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.
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")