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;
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.
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”)