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:

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.

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?

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!

  • Lisa Smith

    It is nice to see you covering a little bit more Advance topics in your blog! Thanks!

    • Sure thing, Lisa! Feel free to suggest other topics 🙂

      • Ramakin

        Hi Joseph,
        I wonder if you can help me, I have followed your wonderful example above and I now have the =hyperlink text coming out in the query. But for some reason it is staying as text rather than excel converting it to a hyperlink. If I highlight the cell and then in the edit box press return it turns into a hyperlink and works correctly. Would you have any suggestions?
        Many thanks

        • Joseph

          Hi Ramakin,

          Thank you for the kind words.

          In the cells where you are entering the formula, are they already formatted as Text by any chance? Or are you copying/pasting the formula? If so, Excel might get confused when it parses the text you pasted, making it think it’s supposed to be text.

          Also, what version of Excel is this?

          Thanks,
          Joseph

  • Ryan

    This was very interesting, I was dealing with a similar situation and by adding the following lines in my VBA after it pulled and inserted the table which included a concatenation for my hyperlink (it required a few other fields that were not necessary to be displayed to the users to create) it created the hyperlink although it lacked the typical hyperlink formatting.

    Sheets(“Active_Sheet”).Columns(“hyperlink:hyperlink”).Select
    Selection.Replace What:=”h”, Replacement:=”h”, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

    This is not necessary in all situations but was a rather simple solution to creating the hyperlink from a SQL Query

    Additionally using your example the sql line to create the hyperlink language and require only the formatting would be
    concat(‘=HYPERLINK(“http://www.pubsdb.com/?title_id=,trim(t.title_id),'”,’,trim(t.title))
    make sure you have the ” before the , before the trim(t.title)
    Like I said, not necessary but it worked for me.

    • Hi Ryan, that’s pretty interesting and I didn’t think that Excel would process that as a formula in the end when it came back from the query, but rather insert it as plain Text. I’ll have to give it a go next time and check it out 🙂 thanks for the tip!

  • Conchi

    Thanks! This worked well with creating a clickable list with email addresses (you know, mailto:bla@bla.com)!

    • Joseph

      🙂 Glad to help!!