Pivot Table - Need to Lock Formatting

Try this:
Right click on numbers you want to format> Value field Settings>Number Format> select desired format>OK>OK and Done!

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.

atworkexel said:

Try this:
Right click on numbers you want to format> Value field Settings>Number Format> select desired format>OK>OK and Done!

Click to expand...

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

forgot to mention, this is done on the numbers on the pivot table. Once you follow this steps theformat will not change after refreshing.

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.

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.

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.

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.

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.