Specifying a Format String

A format string controls how numbers are displayed and can also specify colors to use. To specify the format string for an item:

Click the Advanced Options button next to the item or in the header of the Rows or Columns box, as appropriate.
See Customizing Pivot Table Items.
Click the Find button next to Format.
The system displays a dialog box that includes the following fields:
Here:
- Positive piece specifies the format to use for positive values.
- Negative piece specifies the format to use for negative values.
- Zero piece specifies the format to use for zero.
- Missing piece specifies the format to use for missing values.
In each of these, Format string specifies the numeric format, and Color specifies the color.
Specify values as needed (see the details after these steps).
Click OK.

The Format string field is a string that includes one of the following base units:

Base Unit	Meaning	Example
#	Display the value without the thousands separator. Do not include any decimal places.	12345
#,#	Display the value with the thousands separator. Do not include any decimal places. This is the default display format for positive numbers.	12,345
#.##	Display the value without the thousands separator. Include two decimal places (or one decimal place for each pound sign after the period). Specify as many pound signs after the period as you need.	12345.67
#,#.##	Display the value with the thousands separator. Include two decimal places (or one decimal place for each pound sign after the period). Specify as many pound signs after the period as you need.	12,345.67

You can include additional characters before or after the base unit.

If you include a percent sign (%), the system displays the value as a percentage. That is, it multiplies the value by 100 and it displays the percent sign (%) in the position you specify.
Any other characters are displayed as given, in the position you specify.

The following table shows some examples:

Example formatString	Logical Value	Display Value
formatString="#,#;(#,#);" Note that this corresponds to the default way in which numbers are displayed.	6608.9431	6,609
	–1,234	(1,234)
formatString="#,#.###;"	6608.9431	6,608.943
formatString="#%;"	6	600%
formatString="$#,#;($#,#);"	2195765	$2,195,765
formatString="$#,#;($#,#);"	–3407228	($3,407,228)

For the Color field, specify either of the following:

A CSS color name such as MediumBlue or SeaGreen. You can find these at https://www.w3.org/TR/css3-color/Opens in a new tab and other locations on the Internet.
A hex color codeOpens in a new tab such as #FF0000 (which is red).
An RGB value such as rgb(255,0,0) (which is red).