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.