Home > Pivot > Format > Hyperlinks

Pivot Table Hyperlink Examples

Create clickable "hyperlinks" in an Excel pivot table. The cells contain website URLs, email address, or worksheet cell references, formatted with underlined blue font. Select a "hyperlink" cell, and a worksheet macro automatically takes you to that location.

clickable hyperlink in pivot table

Video: Create Fake Hyperlinks in Pivot Table

Hyperlinks won't automatically appear in a pivot table, even if there are hyperlinks in the source data.

In this video, you'll see how to create fake hyperlinks in a pivot table, by using a few lines of Excel VBA code, and cell formatting.

The code is shown below the video, and there are sample files that you can download.

Excel Data With Hyperlinks

When you create a pivot table from Excel data, there might be valid hyperlinks in one or more columns of your source data table. Unfortunately, those hyperlinks will NOT appear in a pivot table that’s built from that data.

Pivot Table Hyperlink Limitations

If hyperlink data is added to a pivot table, it is affected by the following limitations:

  • Pivot tables show any hyperlink URL address as a normal, non-clickable, text string
  • If you click on a cell with one of those pivot table URL text strings, nothing happens
  • Unlike a real hyperlink, those URL addresses are not clickable, so you do NOT go to the website, email, or worksheet location

hyperlinks in pivot table not clickable

Create Fake Hyperlinks in Pivot Table

Fortunately, there’s a workaround that you can use, to create fake clickable hyperlinks in a pivot table. There are two main steps for this technique:

  • A) First, you’ll format the pivot table link text items so they look like real hyperlinks
  • B) Next, you can copy my Excel VBA code, and add it to your workbook, to make each link clickable, almost like a real hyperlink

Clickable Hyperlink Limitations

Before you use this clickable pivot table hyperlink workaround, there are a couple of things to keep in mind:

  • No Friendly Names: The cells must contain a valid hyperlink, email address, or cell reference, such as www.microsoft.com.
    • This technique will NOT work for a "friendly" name, such as "Microsoft Site".
  • Formatting Loss: If you change the pivot table layout, it's possible that the blue underlined formatting could be lost. See the Pivot Table Formatting page for tips and examples.
  • No Pointing Hand: When you point to one of the fake hyperlink cells in the pivot table, the mouse pointer does NOT change to a pointing hand
  • Add a Note: To avoid confusion, you could add a note above the pivot table, to let people know that the pointer shape will not change

no pointing hand over hyperlink cell in pivot table

A) Format Hyperlink Text

Before you add the macro code, follow these steps to format the hyperlink cells in the pivot table.

  • First, be sure that the pivot table is in Outline layout or Tabular report layout. With these layouts, each field is in a separate column.
  • Next, click at the top of the column for the pivot field with the hyperlink text, to select all the pivot item cells
  • Then, on the Excel Ribbon's Home tab, go to the Styles group, and click Cell Styles
  • In the list of Cell Styles, click on a Hyperlink style

B) Pivot Table Hyperlink Macro Code

To create fake hyperlinks in a pivot table, you'll use a few lines of code on the worksheet's code module.

There are two macros below, and there's a sample Excel file for each macro.

  1. Sample Excel code for a single hyperlink pivot field
  2. Sample Excel code for two hyperlink pivot fields

1) Clickable Hyperlinks - One Field

The following code will follow hyperlinks for one specific pivot field in a pivot table.

Copy the code below, and paste it onto the worksheet code module, on the sheet where your pivot table is located. (The code is also in the sample file)

In the code, change the field name to match your pivot field name.

Tip: To get to that code module, right-click the sheet tab, then click View Code.

Private Sub Worksheet_SelectionChange _
  (ByVal Target As Range)
Dim selPF As PivotField
Dim strField As String
Dim strAdd As String
Dim myVal As String
strField = "Site"

On Error Resume Next
Set selPF = Target.PivotField
If Not selPF Is Nothing And _
    selPF.Name = strField Then
  myVal = Target.Value
  If InStr(1, myVal, "@") > 0 Then
    strAdd = "mailto:"
  End If
'2024-01-12 -go to worksheet cell
  If InStr(1, myVal, "!") > 0 Then
    strAdd = "#"
  End If
  ThisWorkbook.FollowHyperlink _
    Address:=strAdd & myVal, _
    NewWindow:=True
End If

End Sub

2) Clickable Hyperlinks - Two Fields

The following code will follow hyperlinks for two specific pivot fields in a pivot table.

Copy the code below, and paste it onto the worksheet code module, on the sheet where your pivot table is located. (The code is also in the sample file)

In the code, change the two field names to match your pivot field names.

Tip: To get to that code module, right-click the sheet tab, then click View Code.

Private Sub Worksheet_SelectionChange _
  (ByVal Target As Range)
'code for 2 pivot fields
Dim selPF As PivotField
Dim strField As String
Dim strField2 As String
Dim strAdd As String
Dim myVal As String
strField = "Site"
strField2 = "Site2"

On Error Resume Next
Set selPF = Target.PivotField
If Not selPF Is Nothing Then
  If selPF.Name = strField _
      Or selPF.Name = strField2 Then
    myVal = Target.Value
    If InStr(1, myVal, "@") > 0 Then
      strAdd = "mailto:"
    End If
    If InStr(1, myVal, "!") > 0 Then
      strAdd = "#"
    End If
  ThisWorkbook.FollowHyperlink _
    Address:=strAdd & myVal, _
    NewWindow:=True
  End If
End If

End Sub

Get Pivot Hyperlink Sample Files

After you download and unzip one of the sample files, copy the code, and paste it into your own workbook. (Paste it onto the Worksheet code module, where your pivot table is located.

  1. One Pivot Field: For the Pivot Table hyperlink code, where only one pivot field has hyperlinks, download this Fake Pivot Table Hyperlinks file. The file is in xlsm format, and zipped. Enable macros so that you can use the sample code.
  2. Two Pivot Fields: If your source data has TWO fields with hyperlinks, download Fake Pivot Table TWO Hyperlinks sample file.

More Tutorials

Excel Hyperlinks

Excel Tables

Pivot Table Formatting

Create a Pivot Table

 

 

 

Last updated: January 12, 2024 7:32 PM