Pivot table - formatting lost - have tried "preserve formatting"

[bg56]

I know there has been a lot of discussion here and in other forums but I have yet to find a solution to my specific problem.

I am using Excel 2010 to create pivot tables and simply cannot get excel to retain my cell formatting. I have set pivot table options as follows:
"Autofit column widths" - UNCHECKED
"Preserve cell formatting on update" - CHECKED

I've also tried other suggestions such as selecting the entire range of cells and used CTRL+1 to do the formatting.

Everytime I refresh the table or add/remove/reorder fields in the table, formatting is lost.

Am I missing something here? REALLY hoping the answer is not to build and run a macro as I have several different pivot tables.

Thanks

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Are you referring to Number Formatting or Cell Fill Formatting such Fill Color?

If it's NumberFormatting, try setting that format in the Field Settings

For Value Fields....
Right-Click on a Cell in the Field > Value Field Settings... > Number Format

For Row or Column Fields
Right-Click on a Cell in the Field > Field Settings... > Number Format

The Number Format button is in the lower left hand corner of the Field Settings Dialog Window.
It will only appear if all the PivotItems in that field are numeric.

 

[bg56]

Jerry,

Thanks for the reply - the fields in question are row fields in the pivot table. I looked at field settings and only see the following:
1st tab - subtotals and filters. (no formatting)
2nd tab - Layout & Print. "Show item labels in out line form", "Show items in tabular form", "Repeat labels", "Insert blank", "Show items with no data" and a the bottom "Insert page break" - no indication of formatting the field.

Am I missing something here?

 

[Jerry Sullivan]

Is all your data for the field numeric?
You might have data that looks numeric but is formatted as text.

If you have just one item in the field that is non-numeric, that Number Format button won't be displayed in the Field Settings Window.

To see what that looks like, you could make a test PivotTable using a field that has numbers 1-10 for its data. Put that field into the Row Labels section, and then you can see what that button looks like when the data is all numeric.

 

[bg56]

You may be on to something... I just built a test sheet with numeric data and "text numbers" and see the "Number Format" option. Going back to my sheets and seeing how the data is sourced.

THANKS

 

[JFCLUK]

Is all your data for the field numeric?
You might have data that looks numeric but is formatted as text.

If you have just one item in the field that is non-numeric, that Number Format button won't be displayed in the Field Settings Window.

To see what that looks like, you could make a test PivotTable using a field that has numbers 1-10 for its data. Put that field into the Row Labels section, and then you can see what that button looks like when the data is all numeric.

THIS IS THE ANSWER YOU'VE BEEN LOOKING FOR! Been trying for hour upon hour upon hour to resolve this. Thank you!