Pivot Table showing labels that do not exist in source data

Question

I inherited an existing data table but with zero data, with pivot tables that have already been created beforehand that have also been refreshed to be empty (Only kept the headers and formatting). Now when I key in "A" into the table, the pivot table will show record for "B" label instead. When I double click on the value on that row to show the source, it is showing as "A". There are two records that show up like that, while other data are okay.

I know you are able to rename pivot item labels, but does it somehow "store" that information even after you've wiped the table out and refreshed the pivot table? This is something very new to me in the 12+ years I have been using Excel.

Some things I've tried:

• Reset the source data and refresh = The same problem, "A" still shows up as "B"
• Number of items to retain per field: None = Still the same problem after refreshing
• Create a new pivot table = This cures the problem, "A" finally shows up as "A"

Answer 1

When you create a pivot table, you can then rename the labels in the pivot table, and they will be kept with the new name.

Say you have A,B,C in you data, you can rename C to D in the pivot table, and from now on, the value of C will appear as D, even if you refresh the data, even if you modify the data or delete all rows. But not if you modify the column names in your data, see below.

This is a nice feature: for instance when you have a table with codes, you can rename codes to labels in the pivot table, without a VLOOKUP.

---

Here is how to reset the modified labels, from here.

1. Rename the column header/name in the source
2. Refresh the pivot. This will drop all forced labels and drop field from the pivot.
3. Rename the column name back to what it was
4. Refresh pivot again
5. Add the field back into your pivot.

Note you can't do that with SSAS pivot tables.

Answer 2

On request, here's an example to find such renamed pivotfields via VBA.

This is intended for pivottables where SourceData is a Range (not an OLAP cube).
The error skipping is included for pivottables, where "Values" is shown as column field, as such a column field doesn't have a SourceName.

Private Sub ResetPivotfieldNames()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim test As String

    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            For Each pf In pt.PivotFields
                On Error Resume Next
                test = pf.SourceName
                If Err.Number = 0 Then
                    If pf.Name <> pf.SourceName Then
                        If MsgBox("On worksheet: " & ws.Name & vbCrLf & _
                            "in pivottable: " & pt.Name & vbCrLf & _
                            "the pivotfield: " & pf.SourceName & vbCrLf & _
                            "was renamed to: " & pf.Name, _
                            vbOKCancel, "Try to rename?") = vbOK Then
                                pf.Name = pf.SourceName
                        End If
                    End If
                End If
                On Error GoTo 0
            Next pf
        Next pt
    Next ws

End Sub