Home > Pivot > Macros > Pivot Items Excel Pivot Item Macros Change Pivot Items in an Excel Pivot Table, or list all pivot items, with these macros. Get the free workbooks with the macro code.

Introduction

In a pivot table, when you add pivot fields, the individual pivot items in that field are also displayed.

Sometimes, old items appear, that are no longer in the data. Those can be removed by following the instructions and sample code here.

You can also show or hide specific items, either manually or programatically, and there are instructions and sample code here.

If pivot item labels have been changed, there is sample code below, to reset them back to the original source name.

Pivot Item Caption

In a pivot table, you can type over a label (caption), to replace the original text from the source data (source name), with new text. If one label is typed over, all the pivot table labels for that item are changed to the same text. This can be a problem, especially if the change was accidental, and you notice the change weeks later.

If a Pivot Field label is changed, you can see use the Field Settings dialog box to see its source name, and change it back to that text. A similar feature isn't available for Pivot Items, but you can use programming to reset the caption.

With the following macros, you can select a pivot item label, and reset the caption, so it shows the source name again. There are two macros:

---FixPivotItemCaption - for normal pivot tables only

---FixPivotItemCaptionDual - for normal or OLAP (Data Model) pivot tables

In the sample workbook, there are two pivot tables, and buttons to run the caption reset macros.

• Select a pivot item cell that you want to fix, then click the macro button.
• When the confirmation message appears, click Yes to reset the caption

For Normal Pivot Tables

Sub FixPivotItemCaption()
Dim pi As PivotItem
Dim lRsp As Long
Dim strCap As String
Dim strSN As String
  
On Error Resume Next
'Set pt = ActiveCell.PivotTable
Set pi = ActiveCell.PivotItem

If pi Is Nothing Then
  MsgBox "Please select a pivot item label cell"
Else
  strCap = pi.Caption
  strSN = pi.SourceName
  lRsp = MsgBox("Reset this pivot item caption " _
      & "to the source name?" _
      & vbCrLf _
      & "Source Name: " & strSN _
      & vbCrLf _
      & "Caption: " & strCap, _
       vbQuestion + vbYesNo, "Reset Caption")
  If lRsp = vbYes Then
    pi.Caption = pi.SourceName
  End If
End If

End Sub

For Normal or OLAP Pivot Tables

Sub FixPivotItemCaptionDual()
Dim pt As PivotTable
Dim pi As PivotItem
Dim lRsp As Long
Dim strCap As String
Dim strSN As String
  
Dim lNum As Long
Dim lFind As Long
Dim strFind As String
Dim strCap01 As String
Dim strCap02 As String

On Error Resume Next
strFind = "&"
  
On Error Resume Next
Set pi = ActiveCell.PivotItem
Set pt = pi.Parent

If pi Is Nothing Then
  MsgBox "Please select a pivot item label cell"
Else
  strCap = pi.Caption
  strSN = pi.SourceName
  If pt.PivotCache.OLAP Then
    strCap01 = pi.SourceName
    lFind = InStrRev(strCap01, strFind) _
            + Len(strFind) - 1
    strCap02 = Replace(Replace(Replace(strCap01, _
      Left(strCap01, lFind), ""), _
        "[", ""), "]", "")
  Else
    strCap02 = pi.SourceName
  End If
  
  lRsp = MsgBox("Reset this pivot item caption " _
      & "to the source name?" _
      & vbCrLf _
      & "Source Name: " & strCap02 _
      & vbCrLf _
      & "Caption: " & strCap, _
       vbQuestion + vbYesNo, "Reset Caption")
  If lRsp = vbYes Then
    pi.Caption = strCap02
  End If
End If

End Sub

List All Pivot Fields and Items

Select a pivot table cell, and then run the following macro, to get a list of pivot fields and pivot items in the selected pivot table. You will be prompted for a maximum number of items per field to list without confirming.

The macro adds a new sheet in the workbook, and creates a list of all row, column, and report filter fields, and their items. Visible items are marked "Y".

Sub ListPivotFieldsItems()
Dim wsList As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim rngPFList As Range
Dim lRow As Long
Dim lCols As Long
Dim bAll As Boolean
Dim bMult As Boolean
Dim strVis As String
Dim strPF As String
Dim strPI As String
Dim strLoc As String
Dim strMsgMax As String
Dim strMsg As String
Dim lListItems As Long
Dim PFItems As Double
Dim MaxItems As Double
On Error GoTo errHandler

Application.ScreenUpdating = False
Application.EnableEvents = False

lCols = 4 'columns in list

On Error Resume Next
  Set pt = ActiveCell.PivotTable
On Error GoTo errHandler

If pt Is Nothing Then
  MsgBox "Please select a cell in a pivot table"
  GoTo exitHandler
End If

Set wsList = Sheets.Add
lRow = 1
With wsList
  .Range(.Cells(lRow, 1), .Cells(lRow, lCols)).Value _
      = Array("Location", "Field", "Item", "Visible")
End With

lRow = lRow + 1

'set max items to check before listing
strMsgMax = "Enter Max # of pivot items to list automatically"
strMsgMax = strMsgMax & vbCrLf
strMsgMax = strMsgMax & "For higher number, confirmation required"
strMsgMax = strMsgMax & vbCrLf
strMsgMax = strMsgMax & vbCrLf
strMsgMax = strMsgMax & "Enter zero if you don't want a limit"
On Error Resume Next
  MaxItems = InputBox(strMsgMax, "Maximum Items to Confirm", 0)
On Error GoTo errHandler
If MaxItems = 0 Then MaxItems = Rows.Count

For Each pf In pt.PivotFields
  Select Case pf.Orientation
    Case xlPageField: strLoc = "1 - Filter"
    Case xlRowField: strLoc = "2 - Row"
    Case xlColumnField: strLoc = "3 - Column"
    Case Else: strLoc = ""  'only list row, column, filter
  End Select
  
  If strLoc <> "" Then
    strPF = pf.Name
    bAll = False
    If pf.AllItemsVisible Then bAll = True
    
    PFItems = pf.PivotItems.Count
    lListItems = vbYes
    
    If PFItems > MaxItems Then
      strMsg = "List all " & PFItems & " items for field " & strPF
      lListItems = MsgBox(strMsg, vbQuestion + vbYesNo, "List All")
    End If
    
    If lListItems = vbYes Then
      For Each pi In pf.PivotItems
        strVis = ""
        strPI = pi.Name
        Select Case bAll
          Case True
            strVis = "Y"
          Case Else
            If pi.Visible Then strVis = "Y"
        End Select
        With wsList
          .Range(.Cells(lRow, 1), _
            .Cells(lRow, lCols)).Value _
              = Array(strLoc, strPF, strPI, strVis)
        End With
        lRow = lRow + 1
      Next pi
    Else
      strPI = PFItems & " Items"
      Select Case bAll
        Case True: strVis = "Y"
        Case Else: strVis = "N/A"
      End Select
      With wsList
        .Range(.Cells(lRow, 1), _
          .Cells(lRow, lCols)).Value _
            = Array(strLoc, strPF, strPI, strVis)
      End With
      lRow = lRow + 1
    End If
  End If
Next pf

'format list as a named table
With wsList
  .ListObjects.Add(xlSrcRange, _
      .Range("A1").CurrentRegion, , _
          xlYes).Name = "tblFltr_" _
          & Format(Now(), "mmddhhmmss")
  .Range(.Cells(1, 1), .Cells(1, lCols)) _
        .EntireColumn.AutoFit
  .Range("A1").CurrentRegion.Sort _
    Key1:=.Range("A2"), _
      Order1:=xlAscending, _
      Header:=xlYes
End With


exitHandler:
  Set wsList = Nothing
  Set pf = Nothing
  Set pt = Nothing
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub

errHandler:
  GoTo exitHandler

End Sub

Download the Sample File

1. Download the zipped sample file to reset pivot item captions. The file is in xlsm format, and contains macros. When you open the file, enable macros, if you want to test the code.
2. Download the zipped sample file to list all pivot fields and pivot items. The file is in xlsm format, and contains macros. When you open the file, enable macros, if you want to test the code.

More Tutorials:

Clear Old Items in Pivot Table Drop Downs

Show/Hide Pivot Items

Pivot Table Introduction

Grouping Data

Pivot Table from Multiple Sheets

Running Totals

Summary Functions