Making Pivot Table Hyperlinks Clickable

If you’ve ever built a PivotTable that contains hyperlinks, you’ll notice that clicking the hyperlinks doesn’t do anything. This can be a bit frustrating as the reason you put that field on the Pivot in the first place is that it’s valuable information you want to use. When you click the hyperlink, you kind of expect it to open!
Fortunately, even though hyperlinks don’t work in PivotTables by default, we can make it work with just a tiny bit of VBA code.

Example

Let’s assume that you have a PivotTable set up like the following:

Click image for larger version. 

Name:	clickablehyperlinks1.png 
Views:	27705 
Size:	53.4 KB 
ID:	1791

Adding the code

To add the required code, you’re going to need to know two things:

  1. The name of the file you’re working in. This example is based on a file called “Reconciliation.xlsm”
  2. The worksheet that your PivotTable lives in. In this case, it’s “Summary”

Now, knowing that, let’s make this work:

  • Press Alt+F11 to open the Visual Basic Editor
  • If the Project Explorer (window 1 in the image below) isn’t showing, press CTRL+R to show it
  • Drill down into your workbook and expand the “Microsoft Excel Objects”
  • Locate the worksheet that holds your PivotTable and double click it

Click image for larger version. 

Name:	clickablehyperlinks2.png 
Views:	27499 
Size:	67.3 KB 
ID:	1792

At this point, it will activate the correct code pane for you to past your code into (marked as 2, above). Simply copy the following code and paste it in there after the last line that start with “Option” (if any exist):

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        On Error Resume Next
        ActiveWorkbook.FollowHyperlink _
            Address:=CStr(Target.Value), _
            NewWindow:=True
        On Error GoTo 0
    End If
End Sub

Finally:

  • Close this window to return to Excel
  • Save the file

Now you can click on any cell in the worksheet. If it has a valid hyperlink, it will open it for you!

Caveats

Using macros blows Excel’s Undo stack. Clicking any cell on this worksheet will cause the Undo actions to clear. It’s nothing serious, just something to be aware of.

Also, keep in mind that this will only work on this specific worksheet. If you’d like to modify the routine to work on any worksheet, our forums are a good place to ask for help.

Share:

Facebook
Twitter
LinkedIn

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts

Excel Fundamentals Boot Camp

COACHED TRAINING: Excel Fundamentals Boot Camp Course Description In the Fundamentals Boot Camp, you will begin with a review core skills for the Excel analyst. This section is geared to

Read More »