Formatting Columns in Pivot Tables

[Guest]

I want to have certain number formats down a column stay after a refresh but
the cell formats keep reverting back to "General". In PivotTable Options I
have AutoFormat table unchecked and Preserve formatting checked. My headers
keep their formatting but the cells below with numbers keep going back to
general with something like 9 decimal places. Any suggestions? Thanks

 

[Guest]

I assume you have been using Format Cell and tehnd selecting your format. Try
this... Right click on the numbers and select Field Setting and then Number.
Select your format and all of the numbers for that field will be formatted
appropriately...

 

[Guest]

Hi Jim,
Thanks for your help. I tried your suggestion but I actually don't see an
option for "Number" when I select Field Settings. When I open Field Settings
I get a box called PivotTable Field with the Name of the column I am
currently in. Then on the left there is Subtotals with button options of
Automatic, Custom and None. Then inside a box I see Sum, Count, Average, Max,
Min, Product, Count Nums, StdDev, StdDevp, Var, Varp. Along the right I have
OK, Cancel, Hide, Advanced, Layout. I don't have the option of selecting
Number; or am I missing something?
Thanks

 

[Guest]

Hi,

Make sure you right click in any cell of the column you want to format (data
field column) then click field settings and then you will see the number
option.

 

[Guest]

I tried this with mixed results. It would only select lines in the field
that had the same title. I added a line to the bottom of those cells, and
they held thru the refresh. I tried again and added some vertical
lines.....they disappreared when I refreshed the Pivot Table. There doesn't
seem to be any way to consistently select all items in a field, format them,
and have the formatting hold.

 

[Debra Dalgleish]

Some formatting, such as inside and right borders, don't stick very
well. If you need those, you could record a macro as you apply the
formatting, then run the macro after a refresh.

For number formatting, as Jim suggested, use the Field Settings dialog box.

For other formatting, the following may help:
Right-click on a cell in the pivot table, and choose Table Options
Remove the check mark from AutoFormat table
Add a check mark to Preserve formatting
Click OK

Ensure that Enable Selection is turned on (on the PivotTable toolbar,
choose PivotTable>Select>Enable Selection)

Then, click at the top of the heading for the data field you want to format
On the PivotTable toolbar, choose PivotTable>Select>Data
Apply your formatting.
When you refresh the pivot table, the formatting should stick.