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?

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.

--
Regards

Roger Govier


"Neostand" wrote in message
...
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?

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" wrote:

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.

--
Regards

Roger Govier


"Neostand" wrote in message
...
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?

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.

--
Regards

Roger Govier


"Neostand" wrote in message
...
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" wrote:

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.

--
Regards

Roger Govier


"Neostand" wrote in message
...
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?

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?

"Roger Govier" wrote:

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.

--
Regards

Roger Govier


"Neostand" wrote in message
...
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" wrote:

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.

--
Regards

Roger Govier


"Neostand" wrote in message
...
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?

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.

Neostand wrote:
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?

"Roger Govier" wrote:


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.

--
Regards

Roger Govier


"Neostand" wrote in message
...

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" wrote:


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.

--
Regards

Roger Govier


"Neostand" wrote in message
...

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?








--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html