Pivot Table Refresh Issue

[Dinesaaa]

Hi All,


I recently noticed that users can overwrite any cell values in the pivot table. Unfortunately those new values are permanently retained even after refreshing the pivot table afterwards.

We already tried various things to remove the impacted column from the pivot table, then refresh (or refresh-all), then add it back.

Or save and close the workbook without the impacted column, open it again, then add it back and refresh.

It seems that the 'manually' replaced value is cached somewhere. But I cannot find any option to clear this cache.



Or is there something else going on? If this is meant to be a 'feature', it's a very horrible feature. I don't see what value it should have to overwrite data manually, when you cannot fix it be simply refreshing it.

 

[RoryA]

I assume you are talking about row/column field values, not data values? If so, that is indeed a feature, intended to allow you to rename values (commonly used if you group items and then want to rename the group ) - which would be pointless if simply refreshing reset it. If you remove the field from the pivot, refresh, and then put it back, the values should reset. Are you using the data model?

 

[Dinesaaa]

Thanks @rory A

yes, you are right. It is a field value. I tried your solution but still the same value is reflecting.

But I tried creating simple pivot with new workbook and tried this solution there it is working perfectly, I am confused.

No, I am using Excel pivot not Data model ( Hope you are referring to Power BI).

Below are the key notes for further insights on the issue: -

1, Data source is coming from a mapping sheet, where If I add new line item in the mapping sheet it is correctly picking in the pivot table but the old name is not changing at al.

2, In mapping sheet we given the for an ID 12345 to Denmark and it is correctly reflecting in the data source file. But we once manually override it to Testing now it is not at al changing back to Denmark.

 

[Dinesaaa]

I assume you are talking about row/column field values, not data values? If so, that is indeed a feature, intended to allow you to rename values (commonly used if you group items and then want to rename the group ) - which would be pointless if simply refreshing reset it. If you remove the field from the pivot, refresh, and then put it back, the values should reset. Are you using the data model?

Thanks @rory A

yes, you are right. It is a field value. I tried your solution but still the same value is reflecting.

But I tried creating simple pivot with new workbook and tried this solution there it is working perfectly, I am confused.

No, I am using Excel pivot not Data model ( Hope you are referring to Power BI).

Below are the key notes for further insights on the issue: -

1, Data source is coming from a mapping sheet, where If I add new line item in the mapping sheet it is correctly picking in the pivot table but the old name is not changing at al.

2, In mapping sheet we given the for an ID 12345 to Denmark and it is correctly reflecting in the data source file. But we once manually override it to Testing now it is not at al changing back to Denmark.

 

[Alex Blakenburg]

No, I am using Excel pivot not Data model ( Hope you are referring to Power BI).

Not necessarily, in the field list do you see an icon that looks like a table ie

Also what version of Excel are you using, can you please update your Profile to show the version.

You can find more information on this as well as a macro to reset the item caption here:

Fix Pivot Item Captions in Excel – Excel Pivot Tables

www.pivot-table.com

 

[Dinesaaa]

Not necessarily, in the field list do you see an icon that looks like a table ie
View attachment 116014

Also what version of Excel are you using, can you please update your Profile to show the version.

You can find more information on this as well as a macro to reset the item caption here:

Fix Pivot Item Captions in Excel – Excel Pivot Tables

www.pivot-table.com

No, I am not seeing the similar table as you have shown.
I am using 365 version, will update it.
Here is my table view for your references.

Via MACRO CODE the caption name is reset and thanks for your guidance.

But still not sure why it is not working as a quick fix in my pivot!

 

[Dinesaaa]

I guess, I have missed to update one thing.

The Data source file name is coming through V-Look up, will that be an issue ?

 

[Alex Blakenburg]

The image would indicate that you are not using the Data Model for the pivot.
To update your Account details you can use this link.
V-Lookups in the data source should not impact the operation of the Pivot.

In 365 these steps work for me on a standard pivot to refresh / restore the Caption names.
• Remove the field with the issue from the pivot table
• Refresh the table (with the field removed)
• Put the field back into the Pivot.