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.
Help!!!
May 01 2019 07:18 PM
Hi Andrew,
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.
Regards,
J
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 01 2019 07:48 PM
@Andrew Knott I believe you can specify cell number formatting by value field setting.
If you load a file. I may be able to do it for you.
J
May 02 2019 02:02 PM
@getbest The formatting issue is related to borders and centering. This clip is from the table. The data is below the grey headers.
May 02 2019 02:03 PM
May 02 2019 05:33 PM
May 05 2019 07:36 PM
@getbest I'm on the latest O365 version (1903 build 11425.20204). The updating seems random--i.e. some of the formats remain, some don't. Would be nice to be able to understand what the issue is.
Jan 10 2020 06:21 AM - edited Jan 10 2020 06:21 AM
@Andrew Knott Curious if you ever found a solution for this?
Am struggling with it every day and would love to find a fix!
Jan 23 2020 08:57 AM
This is an ongoing issue with Excel for some years now, nothing that Microsoft suggest works and they are not doing anything to fix the problem.
Jan 24 2020 08:56 AM
Jan 24 2020 09:08 AM
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 11 2022 02:16 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.
Aug 14 2022 11:08 PM
You may refer to the solution in my reply on Jul 30 2022.
"Preserve cell formatting on update" alone is not working. I also wonder why Microsoft technical team can't find a solution.