Excel Custom Number Formatting Explained
Posted: September 21st, 2011 | Author: miconian | Filed under: Technology | 1 Comment »
For years, I found it daunting to look at the box shown above. It’s not so much the complexity of the character strings, but the complete lack of information displayed about what they mean. The underlying message seems to be that somewhere out there, someone is doing something so complicated and esoteric with Excel that if you don’t intuitively understand what those more complicated formats are for, then there’s no point in trying to explain it to you.
Sure, there are explanations available, both on Microsoft’s official help pages, and on many other sites. But in my research, I have been unable to find one place that actually engages the specific default “custom” number formats and explains what each of them actually do. Most explanations speak briefly and obliquely about certain aspects, letting you know that, say, the # symbol stands for a number, or that a zero is a placeholder for a zero. But neither of those things is really true; it’s just that the real explanation is much longer and more difficult to get across. One might say that the # symbol represents a digit that could be any number 0-9, unless it is a leading or trailing zero. But to spell that out would be to admit that the whole system is actually complicated and strange, a sort of dumbed-down and more poorly documented version of regular expressions. Without further ado, I present a plain-language explanation for every default “custom” number format that comes with Excel 2007.
0
If there is a zero in a cell by itself, it will be shown. Digits to the right of the decimal will not be shown, but if .5 or greater, the digit to the left of the decimal will be rounded up.
0.00
This guarantees that all three digits shown (the one just to the left of the decimal, and the two to the right, will always show some number, even if that number is a zero.
#,##0
Numbers that go into the thousands will be shown with a comma in the appropriate place. If the number is a zero, then a zero is shown. Digits to the right of the decimal will not be shown, but if .5 or greater, the digit to the left of the decimal will be rounded up.
#,##0.00
Numbers that go into the thousands will be shown with a comma in the appropriate place. If the number is a zero, or if whatever is to the right of the decimal can be rounded down to zero, then a zero is shown. Digits to the right of the decimal will be shown up to the hundredths place, even if one or both of those digits is a zero.
#,##0_);(#,##0)
Numbers that go into the thousands will be shown with a comma in the appropriate place. If the number is a zero, or if whatever is to the right of the decimal can be rounded down to zero, then a zero is shown. Digits to the right of the decimal will not be shown. Positive numbers will be pushed left the equivalent of the space taken up by the “)” character, so that they will line up properly with negative numbers that appear in parenthesis. Negative numbers appear in parenthesis, and follow the same rules as positives in terms of commas and decimals.
#,##0_);[Red](#,##0)
Numbers that go into the thousands will be shown with a comma in the appropriate place. If the number is a zero, or if whatever is to the right of the decimal can be rounded down to zero, then a zero is shown. Digits to the right of the decimal will not be shown. Positive numbers will be pushed left the equivalent of the space taken up by the “)” character, so that they will line up properly with negative numbers that appear in parenthesis. Negative numbers appear in parenthesis, appear in red, and follow the same rules as positives in terms of commas and decimals.
#,##0.00_);(#,##0.00)
Numbers that go into the thousands will be shown with a comma in the appropriate place. If the number is zero, then a zero is shown. Digits to the right of the decimal will be shown up to the hundredths place, even if one or both of those digits is a zero. Positive numbers will be pushed left the equivalent of the space taken up by the “)” character, so that they will line up properly with negative numbers that appear in parenthesis. Negative numbers appear in parenthesis and follow the same rules as positives in terms of commas and decimals.
#,##0.00_);[Red](#,##0.00)
Numbers that go into the thousands will be shown with a comma in the appropriate place. If the number is zero, then a zero is shown. Digits to the right of the decimal will be shown up to the hundredths place, even if one or both of those digits is a zero. Positive numbers will be pushed left the equivalent of the space taken up by the “)” character, so that they will line up properly with negative numbers that appear in parenthesis. Negative numbers appear in parenthesis, appear in red, and follow the same rules as positives in terms of commas and decimals.
$#,##0_);($#,##0)
All numbers will be shown with dollar signs. Numbers that go into the thousands will be shown with a comma in the appropriate place. If the number is a zero, or if whatever is to the right of the decimal can be rounded down to zero, then a zero is shown. Digits to the right of the decimal will not be shown. Positive numbers will be pushed left the equivalent of the space taken up by the “)” character, so that they will line up properly with negative numbers that appear in parenthesis. Negative numbers appear in parenthesis, and follow the same rules as positives in terms of commas and decimals.
$#,##0_);[Red]($#,##0)
All numbers will be shown with dollar signs. Numbers that go into the thousands will be shown with a comma in the appropriate place. If the number is a zero, or if whatever is to the right of the decimal can be rounded down to zero, then a zero is shown. Digits to the right of the decimal will not be shown. Positive numbers will be pushed left the equivalent of the space taken up by the “)” character, so that they will line up properly with negative numbers that appear in parenthesis. Negative numbers appear in parenthesis, in red, and follow the same rules as positives in terms of commas and decimals.
$#,##0.00_);($#,##0.00)
All numbers will be shown with dollar signs. Numbers that go into the thousands will be shown with a comma in the appropriate place. If the number is zero, then a zero is shown. Digits to the right of the decimal will be shown up to the hundredths place, even if one or both of those digits is a zero. Positive numbers will be pushed left the equivalent of the space taken up by the “)” character, so that they will line up properly with negative numbers that appear in parenthesis. Negative numbers appear in parenthesis, and follow the same rules as positives in terms of commas and decimals.
$#,##0.00_);[Red]($#,##0.00)
All numbers will be shown with dollar signs. Numbers that go into the thousands will be shown with a comma in the appropriate place. If the number is zero, then a zero is shown. Digits to the right of the decimal will be shown up to the hundredths place, even if one or both of those digits is a zero. Positive numbers will be pushed left the equivalent of the space taken up by the “)” character, so that they will line up properly with negative numbers that appear in parenthesis. Negative numbers appear in parenthesis, in red, and follow the same rules as positives in terms of commas and decimals.
0%
Numbers are multiplied by 100, and the % sign is appended. If the number is zero, then 0% is shown. Numbers to the right of the decimal will not be shown, but if .5 or greater, the number to the left of the decimal will be rounded up.
0.00%
Numbers are multiplied by 100, and the % sign is appended. If the number is zero, then 0% is shown. Digits to the right of the decimal (after the multiplication by 100) will be shown up to the hundredths place, even if one or both of those digits is a zero.
0.00E+00
Numbers are shown in scientific notation. The format “5E+3” means “Five multiplied by ten to the third power,” i.e. five thousand. The number to be multiplied by ten includes digits to the right of the decimal up to the hundredths place, even if both those digits are zero. The number representing the exponent of 10 is shown in two digits, even if the first digit is zero. If the number is zero, then 0.00E+00 will be shown. The number to be multiplied by ten will never have more than one digit to the left of the decimal.
##0.0E+0
Numbers are shown in scientific notation. The format “5E+3” means “Five multiplied by ten to the third power,” i.e. five thousand. The number to be multiplied by ten will never have more than three digits to the left of the decimal , and will always include the tens place, even if that digit is zero. If the total number is zero, then 0.00E+00 will be shown. The number representing the exponent of 10 will never be more than one digit.
# ?/?
Digits to the right of the decimal are shown as fractions, e.g. 2.5 is shown as 2 1/2. Whole numbers (which don’t include digits to the right of the decimal) are pushed left, in order to line up with numbers that include fractions. Fractions always have one digit on top, one digit on the bottom. More complex fractions are reduced and rounded.
# ??/??
Digits to the right of the decimal are shown as fractions, e.g. 0.916666666666667 is shown as 11/12. Whole numbers (which don’t include digits to the right of the decimal) are pushed left, in order to line up with numbers that include fractions. Fractions always have up to two digits on top, two digits on the bottom. More complex fractions are reduced and rounded.
m/d/yyyy
Dates are represented as (month number)/(date number)/(full year number), e.g. 2/5/1971.
d-mmm-yy
Dates are represented as (date number)-(three-letter month abbreviation)-(last two digits of year), i.e. 2-Feb-71.
d-mmm
Dates are represented as (date number)-(three-letter month abbreviation), i.e. 5-Feb.
mmm-yy
Dates are represented as (three-letter month abbreviation)-(last two digits of year), i.e. Feb-71.
h:mm AM/PM
Time is shown as standard 12-hour time like on a digital clock, followed by AM or PM.
h:mm:ss AM/PM
Time is shown as standard 12-hour time like on a digital clock, including seconds, followed by AM or PM.
h:mm
Time is shown as 24-hour time, without AM or PM.
h:mm:ss
Time is shown as 24-hour time, including seconds, without AM or PM.
m/d/yyyy h:mm
Time is shown as (month number)/(date number)/(four digit year) hour:minutes, in 24-hour format, e.g. 2/5/1971 23:15.
mm:ss
Time is shown as minutes:seconds. Both are shown in two digits, even if the first digit is zero, e.g. 05:01.
mm:ss.0
Time is shown as minutes:seconds. Both are shown in two digits, even if the first digit is zero, e.g. 05:01. Tenths-of-a-second are shown, even if the digit is zero.
@
Number is formatted as text.
[h]:mm:ss
This is for showing a length of time, as opposed to pinpointing a specific time. The brackets around the hour symbol indicate that the hours can go above 24.
_($* #,##0_);_($* (#,##0);_($* “-”_);_(@_)
A dollar sign appears at the far left if the value in the cell is positive, negative, or zero (not text). To make this happen, padding spaces are added as necessary between the dollar sign and whatever else there is. If the value is zero, then it is represented as a dash. Negative numbers appear in parentheses.
Positive numbers, zero dashes, and text are pushed left the width of a right paren, in order to line up with negative numbers. All formats (positive, negative, zero, and text) are pushed right the width of a left paren. This effectively prevents the dollar sign from being flush up against the left end of the cell, and makes sure that any text, if it goes that far, lines up with the dollar sign.
Numbers over a thousand will have a comma in the appropriate place. Digits to the right of the decimal will not appear, but if they are .5 or larger, then the number to the left of the decimal will be rounded up.
_(* #,##0_);_(* (#,##0);_(* “-”_);_(@_)
Negative numbers appear in parentheses. Positive numbers, zero dashes, and text are pushed left the width of a right paren, in order to line up with negative numbers. All formats (positive, negative, zero, and text) are pushed right the width of a left paren.
Numbers over a thousand will have a comma in the appropriate place. Digits to the right of the decimal will not appear, but if they are .5 or larger, then the number to the left of the decimal will be rounded up.
_($* #,##0.00_);_($* (#,##0.00);_($* “-”??_);_(@_)
A dollar sign appears at the far left if the value in the cell is positive, negative, or zero (not text). To make this happen, padding spaces are added as necessary between the dollar sign and whatever else there is. If the value is zero, then it is represented as a dash, followed by two empty spaces. Negative numbers appear in parentheses.
Positive numbers, zero dashes, and text are pushed left the width of a right paren, in order to line up with negative numbers. All formats (positive, negative, zero, and text) are pushed right the width of a left paren. This effectively prevents the dollar sign from being flush up against the left end of the cell, and makes sure that any text, if it goes that far, lines up with the dollar sign.
Numbers over a thousand will have a comma in the appropriate place. Digits to the right of the decimal will appear up to the hundredths place.
_(* #,##0.00_);_(* (#,##0.00);_(* “-”??_);_(@_)
If the value is zero, then it is represented as a dash, followed by two empty spaces. Negative numbers appear in parentheses. Positive numbers, zero dashes, and text are pushed left the width of a right paren, in order to line up with negative numbers. All formats (positive, negative, zero, and text) are pushed right the width of a left paren. Numbers over a thousand will have a comma in the appropriate place. Digits to the right of the decimal will appear up to the hundredths place.
