May 01 2019 06:58 PM
May 01 2019 06:58 PM
I have a pivot table set up, and have selected "Preserve cell formatting on update" in PivotTable Option. However, when I select a different slicer or refresh the data, the cell formats change dramatically and seemingly randomly. I cannot get the table to save the cell format consistently.
Extremely frustrating, and a seemingly known issue with no good fix.
May 01 2019 07:18 PM
How about cleaning up pivot source data and try again. Pivot usually change formats based on source data. If pivot forces format, actually that is the best way to go.
May 01 2019 07:31 PM
@getbest So, the data is a table that is formatted as a table. I don't change the formatting between refreshes. But on pivot refresh, the pivot formats to "random" formatting. Sometimes, the formats I set are kept, sometimes not. I can't figure out a pattern for the formats it keeps.
May 02 2019 05:33 PM
Mar 31 2020 08:52 AM
@eah, same issue here.
Annoyin and time wasting, have to change format every time the pivot table updates.
By the way, same happens with Pivot Graphs!!!
Dec 31 2020 09:00 AM
This has been an issue for years. I can only assume there are not enough power users who are pivot table enthusiasts for them to care. It drives me crazy and will probably be the reason i stop using excel. I am already experimenting with Tableau to see how can start to move away.
Apr 13 2021 02:59 PM
Jul 19 2021 02:39 AM
I came across this issue whereby my Filter Labels keep changing format upon refresh, while the table itself keeps its format. Managed to find a solution for this:
PivotTable Tools > Design > Click the dropdown for all the different preset formattings > New Pivot Table Styles.
For my case, I just updated the format for 'Report Filter Value' and 'Report Filter Label' cause that's what I was looking for. Hope it helps.
Jul 30 2022 08:30 AM
Aug 14 2022 03:29 PM
Wrong. 100% wrong.
I have the same issue. So do thousands of other people. My source data is formatted the way I want it. My query properties are the way I want them. I have "Preserve cell formatting on update" selected. It still changes back every time I refresh data. There is no way. Another in the infinite list of Excel failures.