Creating Hyperlinks With External Data

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:

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.

The Excel table comes back like this:

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

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])

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.

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:

(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:

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?