Excel allows typing over fields in a PivotTable - bug, right?

[ehans]

I've reported this to MS via Answers, but I'm not crazy am I? You shouldn't be able to type data into a PivotTable at all, right?

I have reproduced this issue in builds 1811 and 1812 of Excel in Office 365. I can type over fields in Rows and it sticks. Refreshing doesn't fix it like when you type over data in Tables that are refreshable. You cannot type over values, but fields can be changed at will here.

 

[Kyle123]

I consider this a feature, I use it all the time - I believe it's by design.

It's especially useful when grouping rows and columns, it allows you to rename the groups and then filter on them.

It's also useful for putting meaningful labels on Values field headings rather than the default Sum of/Count of

I'm also pretty sure it's been in Excel a long time - though I'm not 100% sure and you've just never noticed it. I know for certain it's been there since 2010, I don't know about pre this

 

[ehans]

Thanks Kyle123. So how do you fix this so it goes back to the source data in the pivot table? Once the undo buffer is gone, or the file is closed and reopened, I cannot figure out how to get it to report the actual data.

I'm still having trouble believing this is by design. In using pivot tables for years (decades!) I've never run across this. Sort of shakes my confidence in the results of a Pivot Table someone generates.

 

[Kyle123]

It depends where you've renamed it. Typically removing the field from the rows/columns, then refreshing, then re-adding it will clear it - though it doesn't in some circumstances. "Clear All" will reset the amendments, but forces you to re-create the pivot table.

AFAIK There isn't a reset field type command - though you could probably script it if you were so inclined

There are a lot of posts about this all over google - about how it's a bit unintuitive to remove. Personally, I find it useful that it persists between refreshes as it keeps my amendments

 

[ehans]

Thanks. I need to really investigate this. I don't like it because I want the data to be king, not what someone types in the pivot manually, either on purpose or accidentally.
Removing and re-adding a field isn't a good option as this destroys all manner of formatting, especially column widths and Conditional Formatting.

I'm simply amazed this is possible. I may have to move more stuff to Power BI where you cannot edit the data at all.