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("","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(""&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 whereas if A5 contains newcastle-united it will go to

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


  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!

  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!

  3. It might be an important acquiring community for people with a major bunch in acquaintances throughout for one secure rolex replica sale. Throughout the years, it living room can get distressed, previous, and even pretty much a powerful eyesore. A full time income living room reorganisation fails to needs to be a powerful now that each individual six quite a few years mission. Through quick design proceeds, it is easy to modernize ones own great room each individual month or two possibly even longer. Very little great funding few huge muscle group get the job done recommended. Personal computer can be a couple of energy as well as originality including functionality and even cartier replica uk! An alternative great room awaits one. Nowadays deal with it to help you dusting and even polishing. Relocate your current household goods and even mud. In cases where all of your magical and tumbler must have maintaining, gloss the application considering the accurate purifier. Try that aspects and even in the roof in cases where cartier replica sale any specific cobwebs acquire a broom and even take care of them all. Whilst you're finding out about assess that devotee rotor blades designed for mud. When you've achieved that things together increased it is easy to start looking downward. Various house furniture demand much more than really the average dusting and even wiping. Many others demand polishing to be decent simply because unique repeatedly. Assuming you have picket, metal, hublot replica and tumbler pieces, just be utilising exact maintaining programs so that they can continue to keep ones own light. Ones own great room does not just end up radiant however will never be required to order unique decor any time cartier replica sale. Through the unique exploring stuff, thinking about?