User-defined number formats

To define number formats, select from the Category list and define the format in the User-defined format instruction field.

Formats are defined with format codes. To delete a user-defined format, select it in the Number formats list and click Delete.

User-defined formats can be used with cell graphics.

You can specify [graphic] as a number format in a cell. If the cell is populated with the name of an existing graphic, that graphic is displayed. This supports dynamic changing of the displayed graphic.

Format codes

A user-defined format can have up to 4 parts, separated by semicolons. The parts specify the formats for positive numbers, negative numbers, null values, and for text.

For example, this format displays positive values in blue and negative values in red:

[blue]#,#;[red]#,#

You can also specify conditions. For example, this format displays values less than 5 in red and values less than 100 in blue:

[<5][red]0;[<100][blue]0

If you only specify two parts, the first is used for positive numbers and null values, the second for negative numbers. If you specify only one part, all numbers use that format. If you skip a part, include its closing semicolon.

Decimal places and digits

To format fractions or decimal places, use wildcards. Numbers are rounded to as many decimal places as there are wildcards. If there are more digits in front of the comma than there are wildcards, these are also displayed. If the format only contains the number sign (#) after the decimal place, numbers smaller than 1 begin with a decimal place.


Format Code	Function
#	Displays digits only. Null values are not displayed.
0 (Null)	Displays null values if a number has fewer digits than there are nulls in the format.
?	Inserts spaces for nulls on either side of the comma to align decimal numbers along the decimal comma, if a monospace font (e.g. Courier New) is used for formatting. The ? sign can also be used for fractions with a different number of digits.

Examples:


Displayed format	Format codes
1234.59 as 1234.6	####.#
8.9 as 8.900	#.000
631 as 0.6	0.#,
12 as 12.0 and 1234.568 as 1234.57	#.0#
44.398, 102.65 and 2.8 with aligned decimal places	???.???
5.25 as 5 1/4 and 5.3 as 5 3/10 with aligned division sign	#,???/??

Note: On machines with non-English regional settings, use a comma instead of a full stop as the decimal separator.

The thousands separator

To format multiples of 1000, use these number formats:


Displayed Format	Format Codes
12000 as 12,000	#,###
12000 as 12.000	#\.###
12000 as 12	#,
12200000 as 12.2	0.0,,

Colors

To specify a color for a part of the format, enter the color name in square brackets. The colors you can use are Black, Blue, Cyan, Green, Magenta, Red, White, Yellow. The color code must be the first element of the format definition.