Excel Custom Number Format Index: |
Access the Custom Formatting Menu |
Custom Integer, Decimal & Currency Formatting |
Custom Percentage Formatting |
Custom Date & Time Formatting |
The Excel custom formatting menu is found in the Number tab of the 'Format Cells' dialog box, which can be accessed by either:
or
or
The 'Format Cells' dialog box is shown below:
Make sure that the Number tab at the top of the dialog box is selected. You will then be presented with a Category list on the left hand side of the dialog box.
The Category list contains several pre-defined formats that can be used for numeric values, and you may find your required formatting here. If you do not find your required formatting in this list you can, instead, define your own formatting style.
To define your own custom formatting style this, select the Custom option from the Category list.
A list of sample formatting styles will then appear on the right of the dialog box. You can either select and use these sample formats as they are, or you can edit them to define your own formatting style.
The following sections discuss the Excel formatting options that can be used for displaying different types of numbers.
When defining an excel custom number format for an integer, a decimal or a currency, the following characters are used:
0 | - | Forces the display of a digit in its place |
# | - | Display 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) |
. | - | Specifies the decimal point |
[colour] | - | The name of a colour can be inserted between square brackets to define the font colour |
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. You can even replace a number completely with your chosen characters (e.g. replacing a zero value with the text "NIL")
You can define one, two or three basic Excel formats for any one cell. If the cell contains a numeric value, these formats are applied depending on whether the number is positive, negative or zero, as follows:
The following examples show the effect of different Excel formatting definitions on the numbers 5198.34, -98.66667 and 0
Formatting Definition: | Resulting Formatted Number | ||
---|---|---|---|
5198.34 | -98.66667 | 0 | |
0000.0000 | 5198.3400 | -0098.6667 | 0000.0000 |
0.0## | 5198.34 | -98.667 | 0.0 |
+#,##0.00; -#,##0.00 | +5,198.34 | -98.67 | +0.00 |
+#,##0.0#; (#,##0.0#); "NIL" | +5,198.34 | (98.67) | NIL |
[Blue]+0.0; [Red](0.0#); [Green]"NIL" | +5198.3 | (98.67) | NIL |
$#,##0.00 | $5,198.34 | -$98.67 | $0.00 |
The formatting of percentages in Excel uses the "0", "#" and "." characters in the same way as the formatting of integers and decimals. However, when using the percentage format, Excel displays the number multiplied by 100 and followed by the % sign.
Some examples of the numbers 0.55555, -0.5 and 0, formatted as percentages are shown below:
Formatting Definition: | Resulting Formatted Number | ||
---|---|---|---|
0.55555 | -0.5 | 0 | |
0.00% | 55.56% | -50.00% | 0.00% |
0.0##% | 55.555% | -50.0% | 0.0% |
[Blue]+0.0%; [Red]-0.0%; [Green]0.0% | +55.6% | -50.0% | 0.0% |
When formatting a cell as a date, time or date and time, use the following characters:
d | - |
|
m | - |
|
y | - |
|
h | - |
|
m | - |
|
s | - |
|
AM/PM | - | Indicates that a time should be represented using a 12-hour clock, followed by "AM" or "PM" |
The example below shows different Excel formatting definitions for the date and time '06:00AM on August 25th, 2016':
Formatting Definition: | Resulting Format of Date / Time |
---|---|
mm/dd/yy | 08/25/16 |
m/d/yyyy | 8/25/2016 |
ddd mmmm dd, yyyy | Thu August 25, 2016 |
mm/dd/yyyy hh:mm:ss | 08/25/2016 06:00:00 |
hh:mm:ss | 06:00:00 |
dddd mmm dd yyyy hh:mm:ss AM/PM | Thursday Aug 25 2016 06:00:00 AM |