Format Symbol

Description

General

Displays the number in General format.

0

Digit placeholder. If the number contains fewer digits than the format contains placeholders, the number is padded with 0's. If there are more digits to the right of the decimal than there are placeholders, the decimal portion is rounded to the number of places specified by the placeholders. If there are more digits to the left of the decimal than there are placeholders, the extra digits are retained.

#

Digit placeholder. This placeholder functions the same as the 0 placeholder except the number is not padded with 0's if the number contains fewer digits than the format contains placeholders.

?

Digit placeholder. This placeholder functions the same as the 0 placeholder except that spaces are used to pad the digits.

. (period)

Decimal point. Determines how many digits (0's or #'s) are displayed on either side of the decimal point. If the format contains only #'s left of the decimal point, numbers less than 1 begin with a decimal point. If the format contains 0’s left of the decimal point, numbers less than 1 begin with a 0 left of the decimal point.

%

Displays the number as a percentage. The number is multiplied by 100 and the % character is appended.

, (comma)

Thousands separator. If the format contains commas separated by #'s or 0's, the number is displayed with commas separating thousands. A comma following a placeholder scales the number by a thousand. For example, the format 0, scales the number by 1000 (e.g., 10,000 would be displayed as 10).

E- E+ e- e+

Displays the number as scientific notation. If the format contains a scientific notation symbol to the left of a 0 or # placeholder, the number is displayed in scientific notation and an E or an e is added. The number of 0 and # placeholders to the right of the decimal determines the number of digits in the exponent. E- and e- place a minus sign by negative exponents. E+ and e+ place a minus sign by negative exponents and a plus sign by positive exponents.

$ - + / ( ) : space

Displays that character. To display a character other than those listed, precede the character with a back slash (\) or enclose the character in double quotation marks (" "). You can also use the slash (/) for fraction formats.

\

Displays the next character. The backslash is not displayed. You can also display a character or string of characters by surrounding the characters with double quotation marks (" "). The backslash is inserted automatically for the following characters: ! ^ & ` (left quote) ' (right quote) ~ { } = < >

* (asterisk)

Repeats the next character until the width of the column is filled. You cannot have more than one asterisk in each format section.

_ (underline)

Skips the width of the next character. For example, to make negative numbers surrounded by parentheses align with positive numbers, you can include the format _) for positive numbers to skip the width of a parenthesis.

"text"

Displays the text inside the quotation marks.

@

Text placeholder. If there is text in the cell, the text replaces the @ format character.

m

Month number. Displays the month as digits without leading zeros (e.g., 1-12). Can also represent minutes when used with h or hh formats.

mm

Month number. Displays the month as digits with leading zeros (e.g., 01-12). Can also represent minutes when used with the h or hh formats.

mmm

Month abbreviation. Displays the month as an abbreviation (e.g., Jan-Dec).

mmmm

Month name. Displays the month as a full name (e.g., January-December).

d

Day number. Displays the day as digits with no leading zero (e.g., 1-31).

dd

Day number. Displays the day as digits with leading zeros (e.g., 01-31).

ddd

Day abbreviation. Displays the day as an abbreviation (e.g., Sun-Sat).

dddd

Day name. Displays the day as a full name (e.g., Sunday-Saturday).

yy

Year number. Displays the year as a two-digit number (e.g., 00-99).

yyyy

Year number. Displays the year as a four-digit number (e.g., 1900-2078).

g

If you are using a Japanese locale, this displays the Latin letter for an era.

gg

If you are using a Japanese locale, this displays the first character of an era name.

ggg

If you are using a Japanese locale, this displays the full era name.

e

If you are using a Japanese locale, this displays the full era year.

ee

If you are using a Japanese locale, this displays the full era year with a leading 0 if the year is less than 10.

h

Hour number. Displays the hour as a number without leading zeros (e.g., 1-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock.

hh

Hour number. Displays the hour as a number with leading zeros (e.g., 01-23). If the format contains one of the AM or PM formats, the hour is based on a 12-hour clock. Otherwise, it is based on a 24-hour clock.

m

Minute number. Displays the minute as a number without leading zeros (e.g., 0-59). The m format must appear immediately after the h or hh symbol. Otherwise, it is interpreted as a month number.

mm

Minute number. Displays the minute as a number with leading zeros (e.g., 00-59). The mm format must appear immediately after the h or hh symbol. Otherwise, it is interpreted as a month number.

s

Second number. Displays the second as a number without leading zeros (e.g., 0-59).

ss

Second number. Displays the second as a number with leading zeros (e.g., 00-59).

AM/PM am/pm A/P a/p

12-hour time. Displays time using a 12-hour clock. Displays AM, am, A, or a for times between midnight and noon; displays PM, pm, P, or p for times from noon until midnight.

[h]

Outputs total number of hours.

[m]

Outputs total number of minutes.

[s]

Outputs total number of seconds.

s.0, s.00, s.000, ss.0, ss.00, ss.000

Outputs fractional part of second.

[Black]

Displays cell text in black.

[Blue]

Displays cell text in blue.

[Cyan]

Displays cell text in cyan.

[Green]

Displays cell text in green.

[Magenta]

Displays cell text in magenta.

[Red]

Displays cell text in red.

[White]

Displays cell text in white.

[Yellow]

Displays cell text in yellow.

[COLORn]

Displays cell text using the corresponding color in the color palette. n is a color in the color palette.

[conditional value]

Each format can have as many as four sections: one each for positive numbers, negative numbers, zeros, and text. Using the conditional value brackets ([ ]), you can designate a different condition for each section. For example, you might want positive numbers displayed in black, negative numbers in red, and zeros in blue. The following string formats a number for these conditions:
[>0][Black]General; [<0][Red]General; [Blue]General