Format a Calculated Field in Pivot Table

tcalleia

New Member
Joined
Feb 7, 2008
Messages
34
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:)
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You should be able to format it exactly the same as other fields. Which version of Excel are you using?
 

tcalleia

New Member
Joined
Feb 7, 2008
Messages
34
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
To me that would imply that it is a row or column field that you are clicking, not a data field.
 

tcalleia

New Member
Joined
Feb 7, 2008
Messages
34

ADVERTISEMENT

Yes- it is a row field.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Then the pivot table is treating it as text. What is the formula you are using?
 

tcalleia

New Member
Joined
Feb 7, 2008
Messages
34
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:)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,921
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top