How do I keep my pivot tables format from changing?

[AndrewEdmunds]

In my pivot table, whenever I select a new item through the drop down box of
my field my formatting gets screwed up. I have unchecked auto format table
and I have checked preserve formatting.

For example, I have a balance sheet and I use the drop down box to select
different facilities in order to see their balance sheet values. Whenever I
select a different facility, all of the row headings go from being left
justified to centered and bottom justified to centered.

I appreciate any advice you could give

 

[Daniel.C]

As far as I know, you can't avoid it. Record a macro when formatting,
and play it after each update.
Regards.
Daniel

 

[Tom Hutchins]

You have to apply your formatting to the fields in the pivot table rather
than the rows & columns in the worksheet. If you format a whole column that
includes part of your pivot table, for example, the formatting will be lost
when the pivot table is refreshed.

To format a field in the pivot table, put the cursor right over the heading
of the desired field, then slowly move the cursor upwards. Left-click when it
changes to a downward-pointing arrow. All the data for that field will be
selected. Now select Format >> Cells >> select your desired formatting >> OK.
Formatting applied this way will persist when the pivot table is refreshed
(such as when a new item is selected).

Hope this helps,

Hutch

 

[Argy]

Hi Tom;
I think that Hutch has part of the answer. However, it all depends in what
Excel you are on. So it is this simple:
Office 2003:
1- Righ click your mouse any where in the pivot table.
2- Choose the option, Select Entire Table (You will see all highlighted)
3- Right click again and this time click on Select Data (Only the data
section should be highlighted)
4- Right cliclk again and then click on Format cells and choose the format
you wish to show the data

One of the issues of the 2003 is that it will not keep the other formats
such as the width and the height of the rows and columns; however, the format
of the values would be kept the the Pivot Table.

Office 2007:
Office 2007 makes it easier; however, I am not happy about other things.
1- Define the pivot Table
2- By hand, highligh the area in the PV that you wish to control the format
3- Go straight to the Cells format option using the Menu, or the right click
of the mouse and go ahead and change the format. This should do it.

In the 2007 yo can even have different formats for each cell of the PV if
you wish. In other words, the control of the cells format is done outside of
the PV. I guess that this is possible since Microsoft moved to used now the
XML format.

Good Luck and whether you have any issues, reply and I would try to see what
happen.

 

[AndrewEdmunds]

I really appreciate all of the comments and help with this problem. I tried
Tom's way where I waited for the down arrow and then reformatted it to
justify to the left and bottom and it still went back to center and center
when I changed the item. I also tried right clicking and selecting entire
sheet, and then right clicking and selecting labels (as it is the labels I am
struggling with keeping format). After doing that when I switched items the
format reverted back to center. I ended up writing a macro to format after I
swich items. I really do appreciate the help and please feel free to let me
know if you think I didn't follow your advice correcly.

Thanks again

 

[AndrewEdmunds]

I think I just found the problem. All of the advice I received was correct,
the reason it didn't work for me at the time was because I had merge labels
check in the pivot table options. Once again thanks for all of the help