Creating Hyperlinks With External Data

September 21, 2012

Pretty often at my job I’m asked to create an ad-hoc report in Excel that grabs some info from our website’s database and presents it with whatever metric my manager asks for.  The data will have information that can be linked to a specific page on the website and more often than not I’m asked to link the info to the page so if they want to look at a particular metric in more detail, they can quickly go to the site where the information is.  So in this post, we’ll look at an easy way to dynamically create a hyperlink using info from an external source. For our example, let’s use the pubs database.  We’ll get some information about book titles and sales.  Here is our query:

SELECT
  t.title_id
  , t.title
  , a.au_lname
  , t.price
  , t.ytd_sales
FROM
  authors a
  INNER JOIN titleauthor ta ON a.au_id = ta.au_id
  INNER JOIN titles t ON t.title_id = ta.title_id

The data looks like this:

pubs data2

If you need help downloading the pubs database, click here.

For the sake of argument, let’s also say that I have a website called www.pubsdb.com (please don’t actually try to go there!  It’s not a real location on the web!) and that there is a section where if I know the title_id, I can create a link to the book that it’s connected to.  The URL would look like:

http://www.pubsdb.com/?title_id=BU1032

Quick Tip

A URL is the full name of the site.  For example, www.google.com is the URL to get to Google’s website.  In our example, notice the ?title_id=BU1032 portion.  This is a parameter, much like the ones we use in Excel formulas, where it tells the website certain information about how the page should load.  In this example, it is going to the website and telling it “hey, here’s the title_id of a book I want to look at” and the website responds accordingly.

Notice that I can use the title_id as part of the URL and it will send me to the book in question.  So we can use this data, along with the knowledge of how to build the URL based on title_id and we can create dynamic hyperlinks.

Connect the Query to Excel

We’re going to put this query in an Excel table.  If you’re not familiar on how to do this, please see how to use a SQL Query to create a PivotTable.  The only difference here is when the Import Data dialogue comes up, you want to select Table instead of PivotTable.

sql excel table

The Excel table comes back like this:

hyperlinks excel table

Does this article help you? If so, please consider supporting me with a coffee ☕️

Buy Me a Coffee at ko-fi.com

Add Hyperlinks

OK, so we have the query that we can refresh whenever new data is entered into the database, and we have it shown as an Excel table so we can reference the data.  This is where the Hyperlink() function comes into play.

Hyperlink() Function

A quick note about how the Hyperlink() function works.  It’s pretty simple.  Here is how the formula looks along with the parameters listed and what they mean.

=Hyperlink(link_location, [friendly_name])
Parameter Notes
link_location The URL where the link is pointing to.  This can be dynamically built by concatenating strings together.
[friendly_name] This is an optional parameter that will be the text displayed for the link.  So instead of seeing a URL, you’ll see whatever name you decide to give it.

Building the Hyperlinks

For our example, we’re going to use the title_id to create our hyperlinks.  We start by adding another column to our Excel table.  We’ll name the column Link and put it in F1.

add link column

Notice that the column is formatted just like our Excel table.  With Excel tables, we can add columns to external data and Excel will realize that we want it to look like it’s part of the data (which we do).  You can even add/remove columns in your SQL query and Excel will go right along with it and not overwrite your data.  Awesome :)

Back to the formula.  We can now create the hyperlinks by starting in F2.  The formula is:

=HYPERLINK("http://www.pubsdb.com/?title_id="&A2,B2)

Notice that I manually put the part of the URL that we know what it needs to be and we stop right after the equal sign so we can tack on the title_id in A2.  When Excel evaluates the function, it will concatenate the static text plus the text found in A2.  For the friendly name, we’re putting the actual title.  Now the title looks clickable, which it is.  Now the table looks like this:

fill link column

(by the way, I really don’t like shrinking the image like this, but I want to illustrate this to you)

Since we don’t need the title_id and the old, non-clickable, title column anymore, we can hide them and put the new Link column in its place.

  1. Right-click on the Link column and select “Cut”
  2. Right-click on column C and select “Insert Cut Cells”
  3. Left-click and drag from columns A to B, then let go of the left-click.  This will select columns A and B together.  Right-click either column A or B and select “Hide”

The table now looks like this:

finished result

And there you have it.  Clickable links from external data.  I hope this helps you in any future project you may have!

To the More Experienced People

I’d like to make a special note to the more experience people about this method of creating hyperlinks using external data.  You may have been thinking I was going to have the query build the hyperlink and then have Excel realize that there is a URL there.  I’m sorry to say that I have not found a way to easily do this.

I could have built the URL inside the SQL query itself, but then Excel would just see it as text.  Even if you go that route, you can click inside the Excel formula bar (or hit F2) and press Enter and it will create a hyperlink at that point.  However, every time you refresh the table it will just become plane old text again (except it still has the format of what looks like a hyperlink).

But seriously, do you really want to create a special VBA method that refreshes the data (or even captures a TableUpdate event) and then process all of the hyperlinks again?  Or would you rather add a few extra columns, add a simple formula and let Excel handle the rest?


Wow, you read the whole article! You know, people who make it this far are true learners. And clearly, you value learning. Would you like to learn more about Excel? Please consider supporting me by buying me a coffee (it takes a lot of coffee to write these articles!).

Buy Me a Coffee at ko-fi.com

Written by Joseph who loves teaching about Excel.

© 2021, Spreadsheets Made Easy