Pivot table formatting

[DavidR]

I have a pivot table that pulls its source data from another worksheet in
the same workbook. I have some columns that have "x" in them and also some
columns that have dates. I have formatted both the source data and the
pivot table to center the columns with "x" and use date format 1/4/03. When
I refresh the pivot table, it seems to randomize the formatting. Some of my
"x" cells are centered and some are left justified. Also, some of my dates
are 1/4/03 and some are 1/4/2003. How can I get the formatting to stay?
Thanks for any help.

 

[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

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)

 

[DavidR]

I read something of a similar problem in another post which said to uncheck
the "auto format" box. I did that, selected the table, enabled the
selection, and the "preserve formatting" was already checked. As soon as I
hit OK it scrambles everything again. It seems like this should work, but
for some reason it is not. Any other thoughts? Thanks!

 

[Debra Dalgleish]

What version of Excel are you running?

I read something of a similar problem in another post which said to uncheck
the "auto format" box. I did that, selected the table, enabled the
selection, and the "preserve formatting" was already checked. As soon as I
hit OK it scrambles everything again. It seems like this should work, but
for some reason it is not. Any other thoughts? Thanks!




of my

 

[DavidR]

Excel 2002

What version of Excel are you running?

 

[Debra Dalgleish]

A couple of 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 the cells to format the 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 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.

 

[DavidR]

Debra,
Thanks so much for sticking with me! Your suggestion below worked!

"Before formatting cells, use the selection feature to 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."

 

[Debra Dalgleish]

Excellent! Thanks for letting me know.

Debra,
Thanks so much for sticking with me! Your suggestion below worked!

"Before formatting cells, use the selection feature to 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."