Search Not Just Numbers

Tuesday 4 February 2014

Excel Tip: Using the HYPERLINK function

Two events of note occurred over the weekend:

1. My local football team (Sunderland AFC) beat our bitter rivals (Newcastle United) 3-0 at their place for the second year in a row!

2. I responded to a blog comment asking how to make a URL in Excel that was the result of an IF statement a clickable hyperlink.

I will endeavour to make this post about the latter, but please forgive me if I find it difficult to avoid mentioning the former!

The HYPERLINK formula in Excel allows returns a clickable hyperlink as its result. It is used as follows:

=HYPERLINK(Link,[Friendly name])

Link can be a URL, a file path, or a reference to a cell or range in this or Excel or Office document and should be included in quotes (unless it's a reference to the text rather than the text itself).

[Friendly name] is an optional argument, that allows you to choose what text appears in the cell. If you don't enter this argument, the link text shows. Again, this should be in quotes (unless it's a reference to the text rather than the text itself).

Some examples:

=HYPERLINK("http://www.safc.com","Sunderland AFC") displays the text Sunderland AFC in the cell and links to the team website when you click on it.

=HYPERLINK("C:\Budgets\2014 Budget.xlsx","Open budget spreadsheet") displays the text Open budget spreadsheet and opens the 2014 Budget spreadsheet from the Budgets folder on the C drive when you click on it.

=HYPERLINK("[2014 Budget.xlsx]Assumptions!E46","Edit inflation") displays the text Edit inflation and goes to cell E46 on the Assumptions worskheet of 2014 Budget.xlsx. This format should be used for linking even within the same spreadsheet.

This function really comes into its own though when you populate these arguments with references to results as the links can then be dynamically generated from the contents of the spreadsheet.

For example:

=HYPERLINK(B5,A5) will display whatever text is held in cell A5 and link to the URL or path contained in cell B5

Or potentially more usefully:

=HYPERLINK("http://www.bbc.co.uk/sport/football/teams/"&A5,A5) which will display the football team name held in cell A5 and link to that team's news page on the BBC website. e.g. if A5 contains the text sunderland, the link will go to http://www.bbc.co.uk/sport/football/teams/sunderland whereas if A5 contains newcastle-united it will go to http://www.bbc.co.uk/sport/football/teams/newastle-united.

See my earlier post about manipulating text in Excel to explain use of the Ampersand (&) character.

You could also use VLOOKUP, IF statements or any other functions to populate the arguments of the HYPERLINK function and create dynamic links.

There, I hardly mentioned the score!

Excel Expert Course

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".

4 comments:

  1. Nice! I worked out a couple of these for a client recently, but you have given me a few ideas for other ways to use links & dynamic links. As usual, thank you!

    ReplyDelete
  2. Good column as ever, Glen. It looks like a really powerful function - I just need to resist the temptation to shoehorn them into spreadsheets where they are not needed just to look smart!

    ReplyDelete