Retaining Manual Formatting in Pivot Tables

[Guest]

I use pivot tables a lot and use auto formatting to get the basic shell for
my tables. (I prefer "report 4". After applying this format I do some manual
clean up like changing decimals to % and centering data in the columns and
rows.
The problem is that every time I add new data and refresh is does not retain
the formating that has been applied.
new data shows up as a decimial with 15 points to the right rather then a %.
The data is also aligned to the left of the cell rather then centrered like
the previous stuff that has been manually altered.
Any idea how I can get it to stop doing this?

 

[Roger Govier]

Hi

If you right click on the PT and select Table Options, how have you got
the settings.
Untick Autoformat table
Tick Preserve formatting.

Also, how are you choosing the data to set the % format?
Try hovering the mouse at the top edge of the data filed till it becomes
a solid down arrow, then click.
This should select all of your data, then apply your % format.

 

[Guest]

Thanks Roger,
Can I get it to retain the formats that it applied from the autoformat
option when I turn it off or do I have to manually format every element with
this off?

 

[Roger Govier]

Hi

Unticking the Autoformat feature, doesn't remove the settings you have
applied.
It stops the PT from trying to automatically format column widths etc.
as you do a refresh.

 

[Guest]

Thanks again Roger,
It does maintain the manual formating that I have done but it wipes out the
changes that the autoformatting made.
Do I need to just recreate all of these manually?

 

[Debra Dalgleish]

If you remove the check mark from Autoformat table, it will remove the
Autoformat formatting.

However, you could leave Autoformat checked, if you want to retain that
formatting.
Then, format the fields as Roger suggested, or by right-clicking on a
field heading and choose Field Settings.
If Autoformat is checked, some column widths may automatically adjust
when the pivot table is changed or refreshed.