This is causing me a lot of unnecessary work.

Whenever I format cells and do a refresh
the formatting reverts back to default.

Eg. I format date as Jan-05 when it shows 05/01
to Jan 2005. On entering new data and refreshing
it reverts back to 05/01.

I have selected keep formatting option in Table Option.
Is there something else I have to do to get the
formatting I want?

Help appreciated and thanks

Set the pivot table to preserve formatting:
On the pivot toolbar, choose PivotTableTable Options
Add a check mark to 'Preserve formatting', click OK
Remove the check mark from AutoFormat Table
Click OK

You may have to enable selection (From the Pivot toolbar, choose
PivotTableSelect, and click on Enable Selection)

Before formatting cells, use the selection feature to select the
cells. For example, move the pointer to the top of a column in the pivot
table (just above the column's heading cell). When the black arrow
appears (like the one that appears when the pointer is over a column
button), click to select the column in the pivot table. Then apply the
formatting.

If the above suggestions don't work, you could record a macro as you
refresh and reformat the pivot table. Then, run that when you want
to update.


wrote:
This is causing me a lot of unnecessary work.

Whenever I format cells and do a refresh
the formatting reverts back to default.

Eg. I format date as Jan-05 when it shows 05/01
to Jan 2005. On entering new data and refreshing
it reverts back to 05/01.

I have selected keep formatting option in Table Option.
Is there something else I have to do to get the
formatting I want?

Help appreciated and thanks


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Another option...Click on the field you wish to format, select field settings
from the pivot table toolbar, click on number and select your choice of
formats.

"Debra Dalgleish" wrote:

Set the pivot table to preserve formatting:
On the pivot toolbar, choose PivotTableTable Options
Add a check mark to 'Preserve formatting', click OK
Remove the check mark from AutoFormat Table
Click OK

You may have to enable selection (From the Pivot toolbar, choose
PivotTableSelect, and click on Enable Selection)

Before formatting cells, use the selection feature to select the
cells. For example, move the pointer to the top of a column in the pivot
table (just above the column's heading cell). When the black arrow
appears (like the one that appears when the pointer is over a column
button), click to select the column in the pivot table. Then apply the
formatting.

If the above suggestions don't work, you could record a macro as you
refresh and reformat the pivot table. Then, run that when you want
to update.


wrote:
This is causing me a lot of unnecessary work.

Whenever I format cells and do a refresh
the formatting reverts back to default.

Eg. I format date as Jan-05 when it shows 05/01
to Jan 2005. On entering new data and refreshing
it reverts back to 05/01.

I have selected keep formatting option in Table Option.
Is there something else I have to do to get the
formatting I want?

Help appreciated and thanks


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html