How to Define an Excel Custom Number Format

The main types of Excel custom number format are described separately in the following sections.

Click on one of the listed titles on the left, to go straight to the corresponding section, or work through the page, sequentially.

Excel Custom Number Format Index:
-   Access the Custom Formatting Menu
-   Custom Integer, Decimal & Currency Formatting
-   Custom Percentage Formatting
-   Custom Date & Time Formatting

How to Access the Custom Formatting Menu

The Excel custom formatting menu is found in the Number tab of the Format Cells control box, which can be accessed by either :

Format Cells Control Box

From within the Number tab of the Format Cells control box, select the Custom option from the Category list. A list of pre-defined formatting styles will then appear to the right of the Format Cells control box (see right). You can either select and use these pre-defined formats as they are, or you can edit them to define your own formatting style.

The following sections discuss each of the Excel formatting options that can be used for displaying numbers:

Custom Integer, Decimal & Currency Formatting

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)
. - Defines the position that the decimal place takes
[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 denote positive or negative values, etc. You can even replace the number completely with your chosen characters (eg. 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 number, these formats are applied depending on whether the number is positive, negative or zero, as follows:

  • If a single text format is supplied, this format is applied to all numbers;
  • If two text formats are supplied, and separated by a semi-colon, the first format is applied to positive numbers (and zeros) and the second format is applied to negative numbers
  • 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 zero values.

Decimal & Currency Formatting Examples

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

Excel Custom Percentage Formatting

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.

Percentage Formatting Examples

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%

Excel Custom Date & Time Formatting

When formatting a cell as a date, time or date and time, use the following characters:

d -
Day of the month or day of week
d = one or two digit representation (eg. 1, 12)
dd = 2 digit representation (eg. 01, 12)
ddd = abbreviated day of week (eg. Mon, Tue)
dddd = full name of day of week (eg. Monday, Tuesday)
m -
Month (when used as part of a date)
m = one or two digit representation (eg. 1, 12)
mm = two digit representation (eg. 01, 12)
mmm = abbreviated month name (eg. Jan, Dec)
mmmm = full name of month (eg. January, December)
y -
Year
yy = 2-digit representation of year(eg. 99, 08)
yyyy = 4-digit representation of year(eg. 1999, 2008)
h -
Hour
h = one or two digit representation (eg. 1, 20)
h = two digit representation (eg. 01, 20)
m -
Minute (when used as a part of a time)
m = one or two digit representation (eg. 1, 55)
m = two digit representation (eg. 01, 55)
s -
Second
s = one or two digit representation (eg. 1, 45)
ss = two digit representation (eg. 01, 45)
AM/PM - Indicates that a time should be represented using a 12-hour clock, followed by "AM" or "PM"

Date & Time Formatting Examples

The example below shows different Excel formatting definitions for the date and time '06:00AM on 25th August 2008':

Formatting Definition: Resulting Format of Date / Time
dd/mm/yy 25/08/08
d/m/yyyy 25/8/2008
ddd dd mmmm yy Mon 25 August 08
dd/mm/yyyy hh:mm:ss 25/08/2008 06:00:00
hh:mm:ss 06:00:00
dddd dd mmm yyyy hh:mm:ss AM/PM Monday 25 Aug 2008 06:00:00 AM
Return to the Basic Excel Page

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net