Formatting a pivot table

[Guest]

Hi all!
I have a pivot table in my worbook with some customized formatting (totals
in bold, borderwidth, ....)
My problem is when I refresh the table I loose the formatting. Can somebody
help me understand why? Is there any way that I can set the fomatting once
for all?

Many thanks

Jean-Marie.

 

[Debra Dalgleish]

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

Depending on your version of Excel, you may also have to enable
selection (From the Pivot toolbar, choose PivotTable>Select, and click
on Enable Selection)

Other things to try -- if they don't work, you could record a macro as
you refresh and reformat the pivot table. Then, run that when you want
to update.

--Instead of selecting cells to format numbers, right-click the field
button, and choose Field Settings. Click Number, and choose your
formatting options there.

--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.

 

[Paulw2k]

Hi Jean-Marie,

You don't say which version of Excel you are using, because the pivot-tables
have change quite a bit between
'97 and 2003. I am using the XP version and you can certainly customize the
formatting for particular cells,such as Totals.
Once you have your table constructed, select the cell(s) you want to format
and right-click with your mouse and select
"Format cells..." from the pop-up menu. Format the cells in the way you
want and that is it. The (total) cells will remain like that
after updating. Note also that you can set the number-formatting for the
whole field the cell is part of, if you choose "Field settings..." from the
same pop-up menu.


Regards

Paul

 

[Guest]

Thank you Debra and Paul for your contributions.

Sorry for not having indicated this in my post, I'm usng Excel 2000 and Win
XP Home.
Anyway I followed one Debra's advices, I recorded a macro and it works great.

Thank you again

Jean-Marie.

 

[Guest]

My experience has been that whichever way you choose to format a pivot table
field, the formatting is lost if someone actually uses the pivot table for
analysis by moving fields around. These formatting options will work only if
the pivot table layout is not changed by the end user.

 

[Debra Dalgleish]

That's not my experience. What version of Excel are you using?

 

[Karen]

Hi Paul,

I did what you described and it worked - for some. The funny part is within
the same table, some of the dates could be changed to the format I wanted
(which is something like 23-Jun, instead of 7/23/2008) but some would turn
back to the original format (mm/dd/yyyy) upon refresh.

Now my column looks like this (see the table below) and I don't know nor
understand why it is so. This will happen after I refresh the table.
PRIMARY_FINALDUEDATE
29-May
30-May
27-Jun
5/29/2008
2-Jun
5/29/2008
5/29/2008

Please help...
Karen