Pivot Table - Need to Lock Formatting

fangfacekitty

Board Regular
Joined
Jul 28, 2010
Messages
63
Every time I refresh my pivot table the formatting changes. I have gone into Table Options and unchecked "Auto Format Table" and checked "Preserve Formatting"....but it still changes formatting every single time.

What is going on here? How do I stop the PT from reformatting on every refresh?

My PT is based on one a coworker uses...that does not change formatting every single time. Is there a way to tell what table/report my coworker's PT is based on? I'm assuming that may be why mine keeps reformatting, if my PT is not based on the same table / report.

Thanks in advance for your help!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try this:
Right click on numbers you want to format> Value field Settings>Number Format> select desired format>OK>OK and Done!
 
Upvote 0
Thanks for the reply but I already know how to format. My problem is that every time I refresh the formatting changes and I am tired of having to re-do formatting for every update.



Try this:
Right click on numbers you want to format> Value field Settings>Number Format> select desired format>OK>OK and Done!
 
Upvote 0
what formatting specifically are you losing when you refresh ??

The column widths ? , fonts, background shading ? decimal settings etc.. please provide more detail on the before refresh vs after refresh results
 
Upvote 0
forgot to mention, this is done on the numbers on the pivot table. Once you follow this steps theformat will not change after refreshing.
 
Upvote 0
I have the same issue as fangfacekitty. I am using Microsoft 365, Windows 10, with Excel 2016. There is no "value field settings" that I can see and the "field settings" window doesn't give me any facility to "lock" my formatting. I have tried this on the whole pivot table, individual numeric fields and columns, and individual alpha fields and columns. Can anyone advise? Thanks in advance.
 
Upvote 0
In the pivot table, right click and select PivotTable Option, The bottom option is "Preserve cell formatting on update." That should keep the same formatting if you update the source table.
 
Upvote 0
Thanks, Blind Alley, but that option is already selected. I should say that it is just the column centering that keeps reverting to the default setting for the cell type.
 
Upvote 0
Pivot Table formatting is frustrating. Period. I love Pivot Tables, just not this aspect.
For Alignment, you should try and select the entire column and select the Left/Right/Center alignment.
For the Number Formatting in the Values area, Right click any number in that area and Choose the "Number Format..." option and NOT Cell Formatting.
The Border formatting, or Custom Styes, are the worst to deal with. I've used a little Macro recording editing, to save a preferred style and be able to add it to any Workbook. The screen to do the customization is bad, awful, terrible, sucks! (and I still love Pivot Tables.)
Even if you want just a better default font/size for your Pivot Tables, it has been a real pain. Only Excel 2016-O365 subscribers have it a little better.
 
Upvote 0
I have had the same problem. I refresh pivot tables and loose my color formatting. I have the autoformat box unchecked and the Preserve formatting upon edit checked. It also rechecks the autoformat button each time. I have to reformat every single time. The only fields I can get to keep its formatting is the number format. Nothing else. Wish MS would fix this.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top