Format a Calculated Field in Pivot Table

[tcalleia]

I need to format a pivot table calculated field to a %. When I right click on the field name and go to field settings the "number" option is not listed as it is on the other fields. I can manually format the whole column but then whenever I refresh the pivot table it reverts back to a #. Is there any fix for this?
Thanks

 

[RoryA]

You should be able to format it exactly the same as other fields. Which version of Excel are you using?

 

[tcalleia]

2003- Is that my problem?

When I click field settings on other fields I see the buttons: OK, Cancel, Hide, Advanced, Layout, & Number on the right side. When I click field settings on the calculated field I see: OK, Cancel, Hide, Advanced, and Layout(which is grayed out). Number does not appear at all

 

[RoryA]

To me that would imply that it is a row or column field that you are clicking, not a data field.

 

[tcalleia]

Yes- it is a row field.

 

[RoryA]

Then the pivot table is treating it as text. What is the formula you are using?

 

[tcalleia]

I'm using Profit/Sales to get Margin%

Profit and Sales are row fields- not data fields. Is that where my problem might be?

Thanks