Search Not Just Numbers

Tuesday 7 October 2014

Excel Tip: Using the HYPERLINK function

This week, I want to introduce you to the HYPERLINK function.

This function allows you to add hyperlinks to your spreadsheet for both navigation, and to link to external websites or files.

And, because it is an Excel function, you can use cell contents elsewhere in the spreadsheet to determine the hyperlink.

The syntax of the HYPERLINK function is as follows:

=HYPERLINK(Link,[Friendly Name])

Link can be a URL or a file reference. So, some possible examples are:

"http://www.notjustnumbers.co.uk"
"C:\My Documents\SalesSpreadsheet.xlsx"
"[C:\My Documents\SalesSpreadsheet.xlsx]Consultancy!A1" (note that this one will go to cell A1 of the Consultancy tab of SalesSpreadsheet.xlsx)

Friendly Name is optional and is the text that you want to use as the hyperlink. So, for example:

=HYPERLINK("[C:\My Documents\SalesSpreadsheet.xlsx]Consultancy!A1","Consultancy Sales")

will show a clickable hyperlink that says Consultancy Sales - and clicking on it will take you to cell A1 of the Consultancy tab of SalesSpreadsheet.xlsx.

If this optional argument is not entered, the Link itself will appear as the hyperlink.

The function really comes into its own though when we use other cell addresses in the arguments. If you have two columns of data being company name and website address, for example, then you could have a third calculated as

=HYPERLINK(B1,A1)

which will show the company name (from column B) as a clickable hyperlink that will take you to the company website (from column A).

Or you could use text functions such as the ampersand to create the links. For example you could have a list of sales transactions and the documents stored as Word documents with the invoice number as the file name.

Say column A contains the invoice number and the directory the invoice documents are in is C:\Invoices\, The following HYPERLINK formula will give you a clickable version of the invoice number that takes you to the document:

=HYPERLINK("C:\Invoices\"&A1&".docx",A1)

If A1 contains the invoice number 324, then the function will return a clickable 324 that takes you to the document C:\Invoices\324.docx.
Learn Excel Dashboard Course

Before I go, I have some great news. Mynda Treacy's Excel Dashboards course is once again available - for a limited time.

Order before 16th October 2014 to get 20% off and, as a special offer from me, enter the code FEECHAN when you order and I will send you my Introduction to Pivot Tables course absolutely free.

Click here to find out more about the Excel Dashboards course and to take advantage of this offer.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

1 comment:

  1. Great stuff, Glen, really got me thinking.
    I am going to see if I can implement this idea to better annotate some of my more complex Excel models.
    At present I have been using Comments but they are severely limited.
    Thanks for sharing
    Dave White

    ReplyDelete