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.
Excel provides several built-in number formats. You can use these built-in formats as is, or you can use them as a basis for creating your own custom number formats. When you create custom number formats, you can specify up to four sections of format code. These sections of code define the formats for positive numbers, negative numbers, zero values, and text, in that order. The sections of code must be separated by semicolons (;).
The following example shows the four types of format code sections.
If you specify only one section of format code, the code in that section is used for all numbers. If you specify two sections of format code, the first section of code is used for positive numbers and zeros, and the second section of code is used for negative numbers. When you skip code sections in your number format, you must include a semicolon for each of the missing sections of code. You can use the ampersand (&) text operator to join, or concatenate, two values.
Create a custom format code
-
On the Home tab, click Number Format
-
In the Format Cells dialog box, in the Category box, click Custom.
-
In the Type list, select the number format that you want to customize.
The number format that you select appears in the Type box at the top of the list.
-
In the Type box, make the necessary changes to the selected number format.
Format code guidelines
To display both text and numbers in a cell, enclose the text characters in double quotation marks (" ") or precede a single character with a backslash (\). Include the characters in the appropriate section of the format codes. For example, you could type the format $0.00" Surplus";$–0.00" Shortage" to display a positive amount as "$125.74 Surplus" and a negative amount as "$–125.74 Shortage."
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 |
To create a number format that includes text that is typed in a cell, insert an "at" sign (@) in the text section of the number format code section at the point where you want the typed text to be displayed in the cell. If the @ character is not included in the text section of the number format, any text that you type in the cell is not displayed; only numbers are displayed. You can also create a number format that combines specific text characters with the text that is typed in the cell. To do this, enter the specific text characters that you want before the @ character, after the @ character, or both. Then, enclose the text characters that you entered in double quotation marks (" "). For example, to include text before the text that's typed in the cell, enter "gross receipts for "@ in the text section of the number format code.
To create a space that is the width of a character in a number format, insert an underscore (_) followed by the character. For example, if you want positive numbers to line up correctly with negative numbers that are enclosed in parentheses, insert an underscore at the end of the positive number format followed by a right parenthesis character.
To repeat a character in the number format so that the width of the number fills the column, precede the character with an asterisk (*) in the format code. For example, you can type 0*– to include enough dashes after a number to fill the cell, or you can type *0 before any format to include leading zeros.
You can use number format codes to control the display of digits before and after the decimal place. Use the number sign (#) if you want to display only the significant digits in a number. This sign does not allow the display non-significant zeros. Use the numerical character for zero (0) if you want to display non-significant zeros when a number might have fewer digits than have been specified in the format code. Use a question mark (?) if you want to add spaces for non-significant zeros on either side of the decimal point so that the decimal points align when they are formatted with a fixed-width font, such as Courier New. You can also use the question mark (?) to display fractions that have varying numbers of digits in the numerator and denominator.
If a number has more digits to the left of the decimal point than there are placeholders in the format code, the extra digits are displayed in the cell. However, if a number has more digits to the right of the decimal point than there are placeholders in the format code, the number is rounded off to the same number of decimal places as there are placeholders. If the format code contains only number signs (#) to the left of the decimal point, numbers with a value of less than 1 begin with the decimal point, not with a zero followed by a decimal point.
|
To display |
As |
Use this code |
|
1234.59 |
1234.6 |
####.# |
|
8.9 |
8.900 |
#.000 |
|
.631 |
0.6 |
0.# |
|
12 1234.568 |
12.0 1234.57 |
#.0# |
|
Number: 44.398 102.65 2.8 |
Decimal points aligned: 44.398 102.65 2.8 |
???.??? |
|
Number: 5.25 5.3 |
Numerators of fractions aligned: 5 1/4 5 3/10 |
# ???/??? |
To display a comma as a thousands separator or to scale a number by a multiple of 1000, include a comma (,) in the code for the number format.
|
To display |
As |
Use this code |
|
12000 |
12,000 |
#,### |
|
12000 |
12 |
#, |
|
12200000 |
12.2 |
0.0,, |
To display leading and trailing zeros prior to or after a whole number, use the codes in the following table.
|
To display |
As |
Use this code |
|
12 123 |
00012 00123 |
00000 |
|
12 123 |
00012 000123 |
"000"# |
|
123 |
0123 |
"0"# |
To specify the color for a section in the format code, type the name of one of the following eight colors in the code and enclose the name in square brackets as shown. The color code must be the first item in the code section.
[Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]
To indicate that a number format will be applied only if the number meets a condition that you have specified, enclose the condition in square brackets. The condition consists of a comparison operator and a value. For example, the following number format will display numbers that are less than or equal to 100 in a red font and numbers that are greater than 100 in a blue font.
[Red][<=100];[Blue][>100]
To hide zeros or to hide all values in cells, create a custom format by using the codes below. The hidden values appear only in the formula bar. The values are not printed when you print your sheet. To display the hidden values again, change the format to the General number format or to an appropriate date or time format.
|
To hide |
Use this code |
|
Zero values |
0;–0;;@ |
|
All values |
;;; (three semicolons) |
Use the following keyboard shortcuts to enter the following currency symbols in the Type box.
|
To enter |
Press these keys |
|
¢ (cents) |
OPTION + 4 |
|
£ (pounds) |
OPTION + 3 |
|
¥ (yen) |
OPTION + Y |
|
€ (euro) |
OPTION + SHIFT + 2 |
The regional settings for currency determine the position of the currency symbol (that is, whether the symbol appears before or after the number and whether a space separates the symbol and the number). The regional settings also determine the decimal symbol and the thousands separator. You can control these settings by using the Mac OS X International system preferences.
To display numbers as a percentage of 100 — for example, to display .08 as 8% or 2.8 as 280% — include the percent sign (%) in the number format.
To display numbers in scientific notation, use one of the exponent codes in the number format code — for example, E–, E+, e–, or e+. If a number format code section contains a zero (0) or number sign (#) to the right of an exponent code, Excel displays the number in scientific notation and inserts an "E" or "e". The number of zeros or number signs to the right of a code determines the number of digits in the exponent. The codes "E–" or "e–" place a minus sign (-) by negative exponents. The codes "E+" or "e+" place a minus sign (-) by negative exponents and a plus sign (+) by positive exponents.
To format dates and times, use the following codes.
: If you use the "m" or "mm" code immediately after the "h" or "hh" code (for hours) or immediately before the "ss" code (for seconds), Excel displays minutes instead of the month.
|
To display |
As |
Use this code |
|
Years |
00-99 |
yy |
|
Years |
1900-9999 |
yyyy |
|
Months |
1-12 |
m |
|
Months |
01-12 |
mm |
|
Months |
Jan-Dec |
mmm |
|
Months |
January-December |
mmmm |
|
Months |
J-D |
mmmmm |
|
Days |
1-31 |
d |
|
Days |
01-31 |
dd |
|
Days |
Sun-Sat |
ddd |
|
Days |
Sunday-Saturday |
dddd |
|
Hours |
0-23 |
h |
|
Hours |
00-23 |
hh |
|
Minutes |
0-59 |
m |
|
Minutes |
00-59 |
mm |
|
Seconds |
0-59 |
s |
|
Seconds |
00-59 |
ss |
|
Time |
4 AM |
h AM/PM |
|
Time |
4:36 PM |
h:mm AM/PM |
|
Time |
4:36:03 PM |
h:mm:ss A/P |
|
Time |
4:36:03.75 PM |
h:mm:ss.00 |
|
Elapsed time (hours and minutes) |
1:02 |
[h]:mm |
|
Elapsed time (minutes and seconds) |
62:16 |
[mm]:ss |
|
Elapsed time (seconds and hundredths) |
3735.80 |
[ss].00 |
: If the format contains AM or PM, the hour is based on the 12-hour clock, where "AM" or "A" indicates times from midnight until noon and "PM" or "P" indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock.
See also
Create and apply a custom number format
Display numbers as postal codes, Social Security numbers, or phone numbers
Display dates, times, currency, fractions, or percentages
