pivot table label adding "2" to the end of a label name

[Guest]

Hi,

I have a pivot table and sometimes it adds a "2" to the end of a row lable ie.
"joes pizza" is displayed as "joes pizza2".
the sorce data certanly doesn't include the "2" and it seems like it is
trying to tell me some thing . It might be hapening after i do a "replace
command" in the source data (replace "sams pizza" with joes Pizza"). any one
experienced this?

Cheers,

 

[Dave Peterson]

The only time I've seen this is when I had multiple headers with the same name.

If I were you, I'd check once more--don't forget to check in hidden columns,
too.

 

[schnide]

I've come across this same bug, and it happened when trying to add new rows to the data source. I've had it in several different workbooks - I use Excel 2010, for reference.

After several days of trying to fix it, I've figured out how. Not all of these steps may be necessary and it's not ideal - but this works if you don't want to rebuild your pivot from scratch, for whatever reason.

1) Right click your pivot table -> Pivot table options -> Data -> Change "Number of items to retain per field" to NONE

2) Wipe all rows in your data source except for the headers

3) Refresh the pivot table

4) Save, and close all instances of Excel

5) Reopen the file, and paste your data

6) Refresh the pivot table

You should find that you may have to do a little reformatting, but not for the most part and if your sheet has multiple pivots, which mine does, this is better than starting the whole thing again.

HTH.

schnide

 

[Inet Kemp]

Here is what worked for me. It was less cumbersome than deleting my data.
I changed the area of the source data in the picot table from say 'MASTER (3)'!$A$5:$EQ$600to 'MASTER (3)'!$A$5:$EQ$6 and then back to 'MASTER (3)'!$A$5:$EQ$600

 

[IvanB]

Here is what worked for me. I did not want to delete my data neither.

First I have changed the option in pivot table:
Right click your pivot table -> Pivot table options -> Data -> Change "Number of items to retain per field" to NONE.

Then I have replaced using “Find/Replace” the problematic field name in the data table to some other name (for example: “Surname” -> “Sxxxxx”).

After refreshing the pivot table I have replaced using “Find/Replace” the problematic field name in the data table again to its old value (for example: “Sxxxxx” -> “Surname”.

The refresh of the pivot table has finally removed problem with 2.

 

[Jimmy_H]

This is actually the best way:

1. Change data source to the first two rows.
2. Temporarily change the problematic field value to something else.
3. Refresh pivot table.
4. Head back to the table and revert the field value to what it was originally.
5. Change the data source back to the whole table.
6. Refresh the pivot table.

No faffing about, no nonsense. Simple.

 

[PcDvs]

Years later, 2025, still taking advantage of your tips. Thanks for the help, even across the pond. (We could use a lot more than Excel help, let me tell you! :-{

 

[Jerome Smith-Uldall]

This is actually the best way:

1. Change data source to the first two rows.
2. Temporarily change the problematic field value to something else.
3. Refresh pivot table.
4. Head back to the table and revert the field value to what it was originally.
5. Change the data source back to the whole table.
6. Refresh the pivot table.

No faffing about, no nonsense. Simple.

In fact, I found that it's even simpler.

1. Change data source to the first two rows.
2. Change the data source back to the whole table. (I always select the entire columns, with no limits on the rows).

Presto! The annoying label2 vanishes with this.