Formatting each section of Pivot Table

[luirib]

I have a pivot table with different sections and each need to have a different number format. I can easily do it by selecting the section value of each section and changing the number format to the desired one. However, when I refresh the Pivot Table the number formats revert back to the original format.

I read that to make the changes permanent we need to select the whole values section by going Options/Select/Entire Pivot Table/Select/Values. However these changes are applied to the whole values section and, as far as I can see, cannot be narrowed down to one specific section.

Is there a way to achieve what I'm trying to do? (ie change format for each section that won't be lost upon refresing the PT).

I'm running Excel 2010.

 

[Andrew Poulsom]

What do you mean by a "section"? Can you post a small but representative example?

 

[luirib]

Sorry, I don't know how to paste the actual pivot table, but this should give you an idea of what it looks like (I need one format for section "Metrics", another for section "Revenue" and a third format for section "Cost":

Sum of Amt Region
Type Desc. California
Metrics Production Days 26
Strike Days 4
Other days 5
Metrics Total 34
Revenue Sales 1 (55,200)
Sales 2 (651,565)
Sales 3 -
Sales 4 (7,819)
Sales Other (84,375)
Revenue Total (798,958)
Cost Labor 191,730
COGS 30,300
Other Costs 1
Cost Total 440,137

 

[Andrew Poulsom]

You can only format the entire Amt field, sorry.

 

[luirib]

One last question: if I format the dataset via an SQL stmt, will the format carry into the Pivot Table? This may be a good solution.

 

[Andrew Poulsom]

I have no idea, but I very much doubt it.

 

[luirib]

You are correct, the format does not carry into the PT. I ended up writing a small macro selects each section and formats as needed. The format, interstingly enough, remains after refreshing the table (do not have to execute it every time). Thanks for you help.