Number formats for PivotTable lists

Office PivotTable Lists

Number formats for PivotTable lists

Some of the content in this topic may not be applicable to some languages.

The regional settings in the Windows Control Panel determine how number, currency, date, and time formats are displayed. For example, these settings determine whether years are displayed with two digits or four digits. See Windows Help for information about these formats.

The following table lists the number formats supported in a PivotTable list:

Format Effects
General Displays a number right-aligned, with no thousands separator, and preceded by a minus sign if negative.

Displays up to 11 digits, or 10 digits with a decimal point, rounding additional digits to the right of the decimal point. Displays numbers with more than 11 digits to the left of the decimal point in scientific format.

Displays text left-aligned.

General Date Displays a number as a date and optional time. For example, 8/31/99 05:54 AM.

The fractional part of the number is the time. If a number has no fractional part, only a date appears. If a number has only a fractional part and no integer part, only a time appears.

Long Date Displays a date according to the Microsoft Windows regional setting for the long date format. For example, Thursday, January 25, 2001.
Medium Date Displays a date using the abbreviated month names and hyphens (-) to separate month, day, and year. For example, Jan-25-2001.
Short Date Displays a date according to the Windows regional setting for the short date format. For example, 1/25/2001.
Long Time Displays a time according to the Windows regional setting for the long time format. For example, 8:45:36 PM.
Medium Time Displays a time in 12-hour format with an AM or PM designator, omitting the seconds. For example, 8:45 PM.
Short Time Displays a time in 24-hour format, omitting the seconds. For example, 20:45.
Currency Displays a number according to the Windows regional setting for currency. For example, with the default United States English regional settings, $2,532.75.
Euro Currency Displays a number as euro currency, with a thousands separator and two decimal places. The placement of the euro sign and the decimal places are based on the regional settings for currency in Windows Control Panel. For example, with the default United States English regional settings, Euro12.35.
Fixed Displays a number with two decimal places. For example, 68.30.
Standard Displays a number according to the Windows regional settings for numbers. For example, with a comma as the thousands separator and a period as the decimal symbol, 1,800.00.
Percent Displays a number multiplied by 100, with two decimal places and a percent sign. For example, the value 0.8914 would be displayed as 89.14%.
Scientific Displays a number in exponential notation. For example, 1.25E+10.
Yes/No Displays No if the number is 0; displays Yes for any other number.
True/False Displays False if the number is 0; displays True for any other number.
On/Off Displays Off if the number is 0; displays On for any other number.

Note  In Microsoft Visual Basic or in a script, you can assign custom number formats to the data. You can also type custom formats available in the PivotTable list object model in the Number box.