01, 12) ddd = abbreviated day of week (e.g. When formatting a cell as a date, time or date and time, use the following characters: dĭay of the month or day of week d = one or two digit representation (e.g. The following examples show the effect of different Excel formatting definitions on the numbers 5198.34, -98.66667 and 0 Formatting Definition: If three text formats are supplied (separated by semi-colons), the first format is applied to positive numbers, the second format is applied to negative numbers and the third format is applied to the value zero.If two text formats are supplied (separated by a semi-colon), the first format is applied to positive numbers (and the value zero) and the second format is applied to negative numbers.If just one text format is supplied, this format is applied to all numbers.If the cell contains a numeric value, these formats are applied depending on whether the number is positive, negative or zero, as follows: You can define one, two or three basic Excel formats for any one cell. replacing a zero value with the text 'NIL')

You can even replace a number completely with your chosen characters (e.g. Other characters, such as the ',', '$', '£', '+', '-', '(' and ')' can be displayed at the start, middle and/or end of numbers, to make the number more readable, denote currency, or to denote positive or negative values, etc.

The name of a colour can be inserted between square brackets to define the font colour When defining an excel custom number format for an integer, a decimal or a currency, the following characters are used: 0įorces the display of a digit in its placeĭisplay digit if it adds to the accuracy of the number (but don't display if a leading zero or a zero at the end of a decimal) Custom Integer, Decimal & Currency Formatting

Excel number format dialog box launcher