Forum Discussion
Pivot Table formatting after refresh
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!!!
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.
@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!!!
- This method is working for me. To move your mouse over column header in pivot table until it changes to black arrow, then click column header to select whole column. Once the whole column is selected, you may proceed to format cell. This method is also working perfectly even you use conditional formatting. Please take note that in some cases, you may need to select more than one column for this method to work.
Ericxsim yes this solved the problem... awesome
kraigbMicrosoft
I've struggled with this same issue and found a possible solution: be sure to apply formatting to cells and not entire columns. When I, say, select columns and format to currency, refreshing removes the formatting. When I selecting just the cells with data in those columns and formatting to currency, then the formatting stays. (Before doing this, I also unchecked and rechecked the keep formatting option.)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.
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
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.
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
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.
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.
Here is what I did
I cleared the pivot format from pivot design
Next, selected the row/cells for which i wanted formatting, right client, and select format cells
Formatted the Font, cell color and Number formats
Check on Pivot table Options> Layout a& Format> Checked True on the "Preserve Cell on Update " OptionFormatting didnt change after refresh and was retained
- Hi I am also facing the same issue and I followed the exact steps as you mentioned but I lose the formatting I saved once I refresh the Pivot table. Can you please suggest?
