Fix Pivot Item Captions in Excel

Why would a pivot table show the wrong information? In the example below, a couple of the product names are wrong. If you run into a similar pivot table problem, how can you fix pivot item captions in Excel, so they display the correct information again?

Wrong Pivot Item Captions

In the screen shot below, the yellow cells have the wrong pivot item captions. We don’t sell and “Bran” bars, or “Cheddar” crackers. How did that information get into the pivot table?

Sometimes, refreshing a pivot table will fix a problem, but it doesn’t help in this case.

Right-click a pivot table cell, and click Refresh
The pivot item captions still show the wrong data

What’s the Correct Name?

If you know that a product name caption is wrong, how can you find out what the correct caption should be?

When you point to a product name cell, a screen tip shows the current caption – Healthy Bran. There’s no mention of the original product name.

Check the Source Data

To see what the pivot item captions should be, you can use the Show Details feature.

Double-click on the Quantity number in the Healthy Bran row
Excel adds a new sheet to the workbook, with the details from the source data, for the number that you double-clicked
In that copy of the source data records, you can see the correct product name – “Bran”

Why is the Pivot Table Wrong?

So, why is the pivot table showing “Healthy Bran”, instead of “Bran”?

Usually, a pivot item caption is wrong because someone typed over the original name in the pivot table. It might have been accidental, or maybe they did it on purpose.

Excel won’t let you type over the numbers in the pivot table Values area, but you can type over the pivot item captions, or the pivot field captions, without any problems or warnings from Excel.

That’s a handy feature sometimes – I usually type a space character, to replace captions that show “(blank)”.

But it’s not a helpful feature when it makes the pivot table confusing, as in this case.

How to Fix Pivot Item Captions

There’s no built-in way to get the original pivot ITEM caption back.

If someone changes a pivot FIELD caption, Excel can show you the original field name, and gives you a box to change the caption.

Right-click on a row or column cell in the pivot table
Click Field Settings
You’ll see the pivot field’s source name, and the caption (custom name), which can be changed.

Unfortunately, there’s nothing similar for a pivot ITEM.

Quick Fix

For a quick fix, you can just type the original caption in the cell, over the existing caption.

If you aren’t sure what the correct caption is, use the Show Details trick (above), to see the records from the source data. The correct name will be in there.

Use a Macro

If you don’t know what the item was, use a caption reset macro from my Contextures site to fix the caption.

There are two macros available:

FixPivotItemCaption – for normal pivot tables only
FixPivotItemCaptionDual – for normal or OLAP (Data Model) pivot tables

This animated screen shot shows one of the macros in action.

Select a pivot item cell, where the caption is wrong
Click the button to reset the selected item caption
A confirmation message appears, showing the source name and the current caption
To change the caption back to the source name, click Yes.

Macro to Fix Pivot Item Captions

Here’s the code for the macro that works with either type of pivot table – normal, or OLAP-based (data model).

You can copy the code to a regular code module, then run the macro when needed.

TIP: Store the code in a workbook that’s always open, such as your Personal Workbook. Then, add an icon to the Quick Access Toolbar, so the macro is easy to run.

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

Get the Sample Workbook

To get the Excel workbook with the macros to fix captions, and two sample pivot tables for testing, go to the Pivot Item Macros page on my Contextures website.

The zipped workbook is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to test the macros.

_________________________