Pivot Table formatting after refresh

Copper Contributor

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!!!

21 Replies

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

@getbest The formatting issue is related to borders and centering. This clip is from the table. The data is below the grey headers.2019-05-03 08_57_31-Clipboard.png

Sorry--can't upload the whole file. Screenshot below.
Andrew,

Which version of Excel are you using. I am using Excel 2013 and it's pivot stays same even after regular refresh or slices.
J

@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.

@Andrew Knott Curious if you ever found a solution for this?
Am struggling with it every day and would love to find a fix!

@Andrew Knott 

 

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.

   

Exactly same issue (Office 365 Personal)

@LuizCL 

 

Yes, pivot table cells don't preserve cell formatting on update.

@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!!!

@User1400 

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.  

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.

ctst25_1-1626687484476.png

 

ctst25_0-1626687350121.png

 

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 

 

Many thanks! Simple fix that worked for me!

@getbest 

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.

@MNMullinix 

 

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.