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:
Adding the code
To add the required code, you’re going to need to know two things:
- The name of the file you’re working in. This example is based on a file called “Reconciliation.xlsm”
- 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
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):
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.
8 thoughts on “Making Pivot Table Hyperlinks Clickable”
re: https://excelguru.ca/making-pivot-table-hyperlinks-clickable/
Excellent!!
Can this be made to work where the pivot data item contains the Hyperlink function so the pivot table shows a friendly name rather than the URL?
Hi Liam,
The way the macro is written, it is essentially just saying "when you click the url, treat it as a hyperlink and go there". In order to make this work for a friendly URL, you'd have to do a lot more work as you're "Friendly names" would need to be mapped to a table of destination URLs. (The PivotTable only serves flat text, not metadata in its cells today.)
So ultimately, the answer is yes, but you'd need to set up a mapping table and modify the macro to work as follows: When a URL is clicked, look up the clicked text in the mapping table, get the destination url and launch that as a hyperlink.
Anybody know how to make this across all worksheets?
Hi James,
Try using the following routine in the ThisWorkbook module (with the same code in the body):
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)'body of original routine goes here
End Sub
And make sure you don't have the original in place on any specific worksheets.
Test it carefully though, to make sure it doesn't error out on sheets without pivot tables.
I am trying to use this to get a link in a pivot table to go to a specific cell in another tab. I linked the source cell for the pivot table to the cell I want, but am having trouble modifying this macro to activate that link in the pivot table.
Hi Kate,
What do the cell references look like in your PivotTable? Are they something like Sheet2!A5? If that is the case, I would use this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)Dim ary() As String
Dim ws As Worksheet
If Target.Cells.Count = 1 Then
On Error Resume Next
'Break apart the worksheet/cell reference
ary = Split(Target.Text, "!")
If Left(ary(0), 1) = "'" Then ary(0) = Mid(ary(0), 2, Len(ary(0)) - 2)
'Activate worksheet then cell
Set ws = ActiveWorkbook.Worksheets(ary(0))
ws.Select
ws.Range(ary(1)).Select
On Error GoTo 0
End If
End Sub
This should also work whether you 'enquote' worksheet names with spaces in them within single quotes or not, as well as in cases where relative or absolute references are used.
Hope it helps,
This makes all my cells on the pivot table hyperlinks. I just need three columns not all.
You lost me on this one Sean. Are you talking about the original code sample in the article? For me - if it is a Hyperlink - it will be clickable. If it is not, then standard PivotTable behaviour (like drill-down) kicks in.