Fortunately, even though hyperlinks don’t work in PivotTables by default, we can make it work with just a tiny bit of VBA code.
ExampleLet’s assume that you have a PivotTable set up like the following:
Adding the codeTo 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
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
- 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!
CaveatsUsing 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.