Excel Number Format

You can use number formats to change the appearance of numbers, including dates and times, without changing the actual number. The number format does not affect the cell value that Excel uses to perform calculations. The actual value is displayed in the formula bar.

Auto Number Formatting

By default, all worksheet cells are formatted with the General number format. With the General format, anything you type into the cell is usually left as-is. For example, if you type 123456 into a cell and then press ENTER, the cell contents are displayed as 123456. This is because the cell remains in the General number format. However, if you first format the cell as a date (for example, m/d/yyyy) and then type the number 44682, the cell displays 5/1/2022.

There are also other situations where Excel leaves the number format as General, but the cell contents are not displayed exactly as they were typed. For example, if you have a narrow column and you type a long string of digits like 123456789, the cell might instead display something like 1.2E+08. If you check the number format in this situation, it remains as General.

Finally, there are scenarios where Excel may automatically change the number format from General to something else, based on the characters that you typed into the cell. This feature saves you from having to manually make the easily recognized number format changes. The following table outlines a few examples where this can occur:

Generally speaking, Excel applies automatic number formatting whenever you type the following types of data into a cell:

• Currency
• Percentage
• Date
• Time
• Fraction
• Scientific

Changing the Number or Date Format

Excel has a large array of built-in number formats from which you can choose. To quickly use one of these formats:

1. Select the cells that you wish to format
2. Click the Down arrow in the Home tab > Number format box
   
3. Click any one of the categories below General that you want for that format.

Also, you can change the number format using the Format Cells dialog box. After selecting the cell or range to format, you can display the Format Cells dialog box by using any of the following methods:

• Press Ctrl+1.
• Click the dialog box launcher in Home > Number. (The dialog box launcher is the small downward-pointing arrow icon displayed to the right of the group name in the Ribbon.)
• Right-click the selected cell or range and choose Format Cells... from the shortcut menu.

After launching the Format Cells dialog box, click Number tab in the Format Cells dialog box and select any of the build-in formatting code, click OK to apply the formatting. Sometimes, you need change the width of the columns to display all data.

Built-in Number Formats

The following table lists all of the available built-in number formats:

Number format	Notes
General	No specific number format.
Number	Options include: the number of decimal places, whether or not the thousands separator is used, and the format to be used for negative numbers.
Currency	Options include: the number of decimal places, the symbol used for the currency, and the format to be used for negative numbers. This format is used for general monetary values.
Accounting	Options include: the number of decimal places, and the symbol used for the currency. This format lines up the currency symbols and decimal points in a column of data.
Date	Date formats display date and time serial numbers as date values. Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified for the operating system. Formats without an asterisk are not affected by operating system settings.
Time	Time formats display date and time serial numbers as date values. Time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specific for the operating system. Formats without an asterisk are not affected by operating system settings.
Percentage	Multiplies the existing cell value by 100 and displays the result with a percent symbol. If you format the cell first and then type the number, only numbers between 0 and 1 are multiplied by 100. The only option is the number of decimal places.
Fraction	Select the style of the fraction from the Type list box. If you do not format the cell as a fraction before typing the value, you may have to type a zero or space before the fractional part. For example, if the cell is formatted as General and you type 1/4 in the cell, Excel treats this as a date. To type it as a fraction, type 0 1/4 in the cell.
Scientific	The Scientific format displays a number in exponential notation, replacing part of the number with E+ n, where E (which stands for Exponent) multiplies the preceding number by 10 to the n th power. For example, a 2-decimal Scientific format displays 12345678901 as 1.23E+10, which is 1.23 times 10 to the 10th power.
Text	Cells formatted as text will treat anything typed into the cell as text, including numbers.
Special	Special formats are useful for tracking list and database values. The following special formats are included: Zip Code, Zip Code + 4, Phone Number, and Social Security Number.

Custom Number Formats

If one of the built-in number formats does not display the data in the format that you require, you can create your own custom number format. You can create these custom number formats by modifying the built-in formats or by combining the formatting symbols into your own combination.

Number Formats Code Rules

Before you create your own custom number format, you need to be aware of a few simple rules governing the syntax for number formats:

• Each format that you create can have up to three sections for numbers and a fourth section for text.
  <POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
• The first section is the format for positive numbers, the second for negative numbers, the third for zero values, and the fourth for text.
• These sections are separated by semicolons.
• If you have only one section, all numbers (positive, negative, and zero) are formatted with that format.
• You can prevent any of the number types (positive, negative, zero) from being displayed by not typing symbols in the corresponding section. For example, the following number format prevents any negative or zero values from being displayed: 0.00;;
• To set the color for any section in the custom format, type the name of one of the following eight colors in the code and enclose the name in square brackets as shown: [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]. For example, the following number format formats positive numbers blue and negative numbers red:[BLUE]#,##0;[RED]#,##0
• Instead of the default positive, negative and zero sections in the format, you can specify custom criteria that must be met for each section. The conditional statements that you specify must be contained within brackets. For example, the following number format formats all numbers greater than 100 as green, all numbers less than or equal to -100 as yellow, and all other numbers as cyan: [>100][GREEN]#,##0;[<=-100][YELLOW]#,##0;[CYAN]#,##0
• For each part of the format, type symbols that represent how you want the number to look.

Create a Custom Format Code

To create a custom number format, click Custom in the Category list on the Number tab in the Format Cells dialog box. Then, type your custom number format in the Type box.

Using Symbols to Create Custom Formats

The following table outlines the different symbols available for use in custom number formats.

Format Symbol	Description/result
0	Digit placeholder. For example, if you type 8.9 and you want it to display as 8.90, then use the format #.00
#	Digit placeholder. Follows the same rules as the 0 symbol except Excel does not display extra zeros when the number you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.## and you type 8.9 in the cell, the number 8.9 is displayed.
?	Digit placeholder. Follows the same rules as the 0 symbol except Excel places a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column.
. (period)	Decimal point.
%	Percentage. If you enter a number between 0 and 1, and you use the custom format 0%, Excel multiplies the number by 100 and adds the % symbol in the cell.
, (comma)	Thousands separator. Excel separates thousands by commas if the format contains a comma surrounded by '#'s or '0's. A comma following a placeholder scales the number by a thousand. For example, if the format is #.0,, and you type 12,300,000 in the cell, the number 12.3 is displayed.
E- E+ e- e+	Scientific format. Excel displays a number to the right of the "E" symbol that corresponds to the number of places the decimal point was moved. For example, if the format is 0.00E+00 and you type 12,300,000 in the cell, the number 1.23E+07 is displayed. If you change the number format to #0.0E+0 the number 12.3E+6 is displayed.
$-+/():space	Displays the symbol. If you want to display a character that is different than one of these symbols, precede the character with a backslash \ or enclose the character in quotation marks (" "). For example, if the number format is (000) and you type 12 in the cell, the number (012) is displayed.
\	Display the next character in the format. Excel does not display the backslash. For example, if the number format is 0\# and you type 5 in the cell, the value 5# is displayed.
*	Repeat the next character in the format enough times to fill the column to its current width. You cannot have more than one asterisk in one section of the format. For example, if the number format is 0*x and you type 6 in the cell, the value 6xxxxxx is displayed. Note, the number of "x" characters displayed in the cell vary based on the width of the column.
_ (underline)	Skip the width of the next character. This is useful for lining up negative and positive values in different cells of the same column. For example, the number format _(0.0_);(0.0) align the numbers 5.7 and -3.8 in the column even though the negative number has parentheses around it.
"text"	Display whatever text is inside the quotation marks. For example, the format 0.00 "dollars" displays "4.56 dollars" (without quotation marks) when you type 4.56 into the cell.
@	Text placeholder. If there is text typed in the cell, the text from the cell is placed in the format where the @ symbol appears. For example, if the number format is "Bob "@" Smith" (including quotation marks) and you type "John" (without quotation marks) in the cell, the value "Bob John Smith" (without quotation marks) is displayed.
DATE FORMATS
m	Display the month as a number without a leading zero.
mm	Display the month as a number with a leading zero when appropriate.
mmm	Display the month as an abbreviation (Jan-Dec).
mmmm	Display the month as a full name (January-December).
mmmmm	Display the month as acronym (J-D).
d	Display the day as a number without a leading zero.
dd	Display the day as a number with a leading zero when appropriate.
ddd	Display the day as an abbreviation (Sun-Sat).
dddd	Display the day as a full name (Sunday-Saturday).
yy	Display the year as a two-digit number.
yyyy	Display the year as a four-digit number.
TIME FORMATS
h	Display the hour as a number without a leading zero.
[h]	Elapsed time, in hours. If you are working with a formula that returns a time where the number of hours exceeds 24, use a number format similar to [h]:mm:ss.
hh	Display the hour as a number with a leading zero when appropriate. If the format contains AM or PM, then the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock.
m	Display the minute as a number without a leading zero.
[m]	Elapsed time, in minutes. If you are working with a formula that returns a time where the number of minutes exceeds 60, use a number format similar to [mm]:ss.
mm	Display the minute as a number with a leading zero when appropriate. The m or mm must appear immediately after the h or hh symbol, or Excel displays the month rather than the minute.
s	Display the second as a number without a leading zero.
[s]	Elapsed time, in seconds. If you are working with a formula that returns a time where the number of seconds exceeds 60, use a number format similar to [ss].
ss	Display the second as a number with a leading zero when appropriate.Note that if you want to display fractions of a second, use a number format similar to h:mm:ss.00.
AM/PM, am/pm, A/P, a/p	Display the hour using a 12-hour clock. Excel am/pm displays AM, am, A, or a for times from midnight A/P until noon, and PM, pm, P, or p for times from noon a/p until midnight.

You don't have to use quotation marks to display the characters listed in the following table:

Character	Name
$	Dollar sign
+	Plus sign
-	Minus sign
/	Forward slash
(	Left parenthesis
)	Right parenthesis
:	Colon
!	Exclamation point
^	Circumflex accent (caret)
&	Ampersand
'	Apostrophe
~	Tilde
{	Left curly bracket
}	Right curly bracket
<	Less than sign
>	Greater than sign
=	Equal sign
	Space character

Examples

Text and Space

To display	As	Use this code
99	POSITIVE	"POSITIVE";"NEGATIVE";"ZERO";"TEXT"
-99	NEGATIVE	"POSITIVE";"NEGATIVE";"ZERO";"TEXT"
0	ZERO	"POSITIVE";"NEGATIVE";"ZERO";"TEXT"
Excel	TEXT	"POSITIVE";"NEGATIVE";"ZERO";"TEXT"
5/28/2022 15:10	©2022	"©"yyyy
123456	12° 34' 56''	###° 00' 00''
123456	ID# 000123456	"ID# "000000000

Decimal Places

To display	As	Use this code	Description
1234.59	1234.6	####.#
8.9	8.900	#.000
0.631	0.6	0.#
12	12.0	#.0#
1234.568	1234.57	#.0#
44.398	44.398	???.???	Decimal points aligned
102.65	102.65	???.???	Decimal points aligned
2.8	2.8	???.???	Decimal points aligned
5.25	5 1/4	# ???/???	Numerators of fractions aligned
5.3	5 3/10	# ???/???	Numerators of fractions aligned

Thousands Separator

To display	As	Use this code
12300000	12,300,000	#,###
12300000	12,300,000.00	0,000.00
12300000	12300	#,
12300000	12,300.0	#,###.0,
12300000	12.3	0.0,,

Leading and Trailing Zeros

To display	As	Use this code
12	00012	00000
123	00123	00000
12	00012	"000"#
123	000123	"000"#
123	0123	"0"#

Colors and Conditions

To display	As	Use this code
101	101	[Green][>100]#,##0;[Red][<=-100]#,##0;[Blue]#,##0
-100	-100	[Green][>100]#,##0;[Red][<=-100]#,##0;[Blue]#,##0
56	56	[Green][>100]#,##0;[Red][<=-100]#,##0;[Blue]#,##0
101	101	[>100]#,000;;;
99		[>100]#,000;;;
Excel		[>100]#,000;;;

Hide Zeros or All Values

To display	As	Use this code
99	99	0;-0;;@
-99	-99	0;-0;;@
0		0;-0;;@
Excel	Excel	0;-0;;@
99		;;;
-99		;;;
0		;;;
Excel		;;;

Number, Currency and Accounting formats

To display	As	Use this code	Description
1234.56	1234.56	0.00	Number - General
1234.56	1,235	#,##0	Number - thousands separator, no decimals
1234.56	1,234.56	#,##0.00	Number - thousands separator, 2 decimals
1234.56	$1,235	$#,##0	Currency - no decimals
1234.56	$1,234.56	$#,##0.00	Currency - 2 decimals
-1234.56	($1,234.56)	$#,##0.00_);($#,##0.00)	Currency - 2 decimals, negative value
1234.56	$ 1,235	$ * #,##0	Accounting - no decimals
1234.56	$ 1,234.56	$ * #,##0.00	Accounting - 2 decimals

Fraction Formats

To display	As	Use this code	Description
4.34	4 1/3	# ?/?	Up to one digit (1/4)
0.34	1/3	# ?/?	Up to one digit (1/4)
4.34	4 17/50	# ??/??	Up to two digits (21/25)
4.331	4 142/429	# ???/???	Up to three digits (312/943)
4.34	4 1/2	# ?/2	As halves (1/2)
4.34	4 1/4	# ?/4	As quarters (2/4)
4.34	4 5/16	# ??/16	As sixteenths (8/16)
4.34	4 3/10	# ?/10	As tenths (3/10)
4.34	4 34/100	# ??/100	As hundreths (30/100)

Time Formats - Hours, Minutes and Seconds

To display	As	Use this code	Description
5/28/2022 16:03:06	16	h	Hours
5/28/2022 16:03:06	16	hh	Hours
5/28/2022 16:03:06	5	m	Minutes, but Excel display Months
5/28/2022 16:03:06	05	mm	Minutes, but Excel display Months
5/28/2022 16:03:06	6	s	Seconds
5/28/2022 16:03:06	06	ss	Seconds
5/28/2022 16:03:06	4 PM	h AM/PM	Time
5/28/2022 16:03:06	4:03 PM	h:mm AM/PM	Time
5/28/2022 16:03:06	4:03:06 P	h:mm:ss A/P	Time
5/28/2022 16:03:06	16:03:06.07	h:mm:ss.00	Time
5/28/2022 16:03:06	1073032:03	[h]:mm	Elapsed Time (hours & minutes)
5/28/2022 16:03:06	64381923:06	[mm]:ss	Elapsed Time (minutes & seconds)
5/28/2022 16:03:06	3862915386.07	[ss].00	Elapsed Time (seconds & hundredths)

Percentage Formats

To display	As	Use this code	Description
0.244840088392962	24%	0%	No decimals
0.244840088392962	24.5%	0.0%	1 decimal
0.244840088392962	24.48%	0.00%	2 decimals

Scientific Notation Formats

To display	As	Use this code	Description
12300000	1.23E+07	0.00E+00	Scientific - 7 places
12300000	12.3E+6	#0.0E+0	Scientific - 6 places

Zip code, Zip +4, Phone number, Social Security number

To display	As	Use this code	Description
12345	12345	00000	Zip Code
123456789	12345-6789	00000-0000	Zip Code + 4
1234567899	(123) 456-7899	[<=9999999]###-####;(###) ###-####	Phone Number
123456789	123-45-6789	000-00-0000	Social Security Number