Home

Pivot Table formatting after refresh

%3CLINGO-SUB%20id%3D%22lingo-sub-510256%22%20slang%3D%22en-US%22%3EPivot%20Table%20formatting%20after%20refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510256%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20pivot%20table%20set%20up%2C%20and%20have%20selected%20%22Preserve%20cell%20formatting%20on%20update%22%20in%20PivotTable%20Option.%20However%2C%20when%20I%20select%20a%20different%20slicer%20or%20refresh%20the%20data%2C%20the%20cell%20formats%20change%20dramatically%20and%20seemingly%20randomly.%20I%20cannot%20get%20the%20table%20to%20save%20the%20cell%20format%20consistently.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExtremely%20frustrating%2C%20and%20a%20seemingly%20known%20issue%20with%20no%20good%20fix.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-510256%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPivotTable%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510305%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20formatting%20after%20refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510305%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Andrew%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20about%20cleaning%20up%20pivot%20source%20data%20and%20try%20again.%20Pivot%20usually%20change%20formats%20based%20on%20source%20data.%20If%20pivot%20forces%20format%2C%20actually%20that%20is%20the%20best%20way%20to%20go.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EJ%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510338%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20formatting%20after%20refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F331684%22%20target%3D%22_blank%22%3E%40getbest%3C%2FA%3E%26nbsp%3BSo%2C%20the%20data%20is%20a%20table%20that%20is%20formatted%20as%20a%20table.%20I%20don't%20change%20the%20formatting%20between%20refreshes.%20But%20on%20pivot%20refresh%2C%20the%20pivot%20formats%20to%20%22random%22%20formatting.%20Sometimes%2C%20the%20formats%20I%20set%20are%20kept%2C%20sometimes%20not.%20I%20can't%20figure%20out%20a%20pattern%20for%20the%20formats%20it%20keeps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510365%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20formatting%20after%20refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510365%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F73639%22%20target%3D%22_blank%22%3E%40Andrew%20Knott%3C%2FA%3E%26nbsp%3BI%20believe%20you%20can%20specify%20cell%20number%20formatting%20by%20value%20field%20setting.%3C%2FP%3E%3CP%3EIf%20you%20load%20a%20file.%20I%20may%20be%20able%20to%20do%20it%20for%20you.%3C%2FP%3E%3CP%3EJ%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-519508%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20formatting%20after%20refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-519508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F331684%22%20target%3D%22_blank%22%3E%40getbest%3C%2FA%3E%26nbsp%3BThe%20formatting%20issue%20is%20related%20to%20borders%20and%20centering.%20This%20clip%20is%20from%20the%20table.%20The%20data%20is%20below%20the%20grey%20headers.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20679px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F111873i922F30F4C6AD3685%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-05-03%2008_57_31-Clipboard.png%22%20title%3D%222019-05-03%2008_57_31-Clipboard.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-519513%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20formatting%20after%20refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-519513%22%20slang%3D%22en-US%22%3ESorry--can't%20upload%20the%20whole%20file.%20Screenshot%20below.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-522592%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20formatting%20after%20refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-522592%22%20slang%3D%22en-US%22%3EAndrew%2C%3CBR%20%2F%3E%3CBR%20%2F%3EWhich%20version%20of%20Excel%20are%20you%20using.%20I%20am%20using%20Excel%202013%20and%20it's%20pivot%20stays%20same%20even%20after%20regular%20refresh%20or%20slices.%3CBR%20%2F%3EJ%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-533045%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20formatting%20after%20refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-533045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F331684%22%20target%3D%22_blank%22%3E%40getbest%3C%2FA%3E%26nbsp%3BI'm%20on%20the%20latest%20O365%20version%20(1903%20build%2011425.20204).%20The%20updating%20seems%20random--i.e.%20some%20of%20the%20formats%20remain%2C%20some%20don't.%20Would%20be%20nice%20to%20be%20able%20to%20understand%20what%20the%20issue%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Andrew Knott
Occasional 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!!!

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
17 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies