Search Contextures Sites
Custom Search

Excel Pivot Table VBA - Pivot Items

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.

Reset Pivot Item Caption

Download the Sample File

More Pivot Table Tutorials

Reset 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 macro, you can select a pivot item label, and reset the caption, so it shows the source name again

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"
  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

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.

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search Contextures

Search Contextures Sites

More Pivot Table Resources



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





Privacy Policy


Contextures Inc., Copyright 2016
All rights reserved.


Last updated: August 5, 2016 9:31 AM