Number Formats in Microsoft Excel
Number Formats are little codes that help you control the appearance of numbers in Excel. They are not difficult, and can also be used to help with a few tricks. I'm using Excel charts in many of these examples, but these number formats also apply to values in cells.
You can view or edit a number format by double clicking on a text element, then clicking on the Number tab in the dialog, shown below. There are a number of built in categories, and you will have to experiment with different ones to learn all the options you have. Most likely you will find an appropriate number format within the choices provided, but it is also very easy to add your own. If your desired number format is similar to one you can find in the dialog, select it, then click on the Custom category. The chosen number format will be displayed in an edit box, where you can adjust it as needed.
Number Format Dialog
|
A number format consists of up to 4 items, separated by semicolons. Each of the items is an individual number format. The first by default applies to positive numbers, the second to negative numbers, the third to zeros, and the fourth to text. If you don't apply any special formatting to text, Excel uses the General number format, which basically means anything that will fit. That means Excel will show as many decimal places that the text item has room for, it won't use a thousands separator, and if the number can't fit, perhaps due to a narrow column width, Excel uses a scientific number format. If only one format is specified, Excel uses it for any contents of the cell.
The Y axis tick labels above and the column of numbers to the right use the General number format.
|
|
You can make long numbers easier to read by inserting a thousands separator (a comma for those working in the US). The #,##0 number format is used below; the comma in the format tells Excel to use commas, the octothorpe (pound sign) is merely a placeholder for the comma, and tells Excel not to put digits there if none are needed, and there are no decimal digits. This works well for the axis labels below left, but the decimal fractions have all been truncated to their integer values, zero. Also, this format doesn't allow Excel to use the shorter scientific format, so the longest numbers merely show #######, signifying that they don't fit in their cells. There is only one format specified, so negative numbers use the same format with the comma thousands separator. Without anything different specified, the negative numbers are signified with a minus sign.
Make the labels easier to read: Insert a thousands separator with the #,##0 number format.
|
|
Excel does not have a number format that provides for a given number of significant digits. The best you can do in this case is retain the General number format, and use a formula to round the value to the appropriate number of digits. The column of numbers to the right use a formula of this form to maintain three significant digits:
=ROUND(B1,2-INT(LOG(B1)))
The formula, of course, processes the number in cell B1.
If your values require only a few different decimal digit variations, there's a trick below that uses the number format and no intermediate formulas.
|
|
Defining Colors in Number Formats
The chart below shows how to make negative numbers appear different, something accountants need to know at a glance. The number format used for the Y axis labels is $#,##0;[Red]$#,##0. The first part of this two part format tells Excel to use a dollar sign and a comma thousands separator for positive numbers. The second part tells Excel to make negative numbers red, then use the dollar sign and thousands separator (but no minus sign, since it isn't explicitly stated). Since there is no third number format, Excel uses the first for zero values.
Negative numbers stand out with a number format like $#,##0;[Red]$#,##0.
|
Let's use the three part number format [Blue]$#,##0;[Red]$#,##0;$#,##0, so we can color positive numbers blue, negatives red, and keep zeros in their default black.
The colors that are available in number formats are [Black], [Blue], [Cyan], [Green], [Magenta], [Red], [White], and [Yellow].
The [Blue]$#,##0;[Red]$#,##0;$#,##0 number format produces tricolored axis labels.
|
Changing the Default Number Format Conditions
By default, the first three items in the number format are applied to positive, negative, and zero values. But you can assign your own simple conditions. For example, in this chart, numbers equal to or greater than 3000 are colored blue, with the [Blue][>=3000]$#,##0;[Red][<0]$#,##0;$#,##0 number format.
The [Blue][>=3000]$#,##0;[Red][<0]$#,##0;$#,##0 number format provides a variation on the tricolored axis labels.
|
You can format just a single value in blue by replacing ">=" with "=" in the [Blue][=3000]$#,##0;[Red][<0]$#,##0;$#,##0 number format.
The [Blue][=3000]$#,##0;[Red][<0]$#,##0;$#,##0 number format shows yet another variation on the theme.
|
With this level of control over the display of numbers, number formats are like a poor man's conditional formatting.
Varying Decimal Digits in Number Formats
The following chart uses a logarithmic X axis to show the variation in its data more uniformly. By default (General) the data labels would have many digits after their decimal points, but I only wanted to show two significant digits to reduce clutter. I used the graduated number format [Blue][<0.1]0.000;[<1]0.00;[Red]0.0, which has three, two, or one decimal digits, depending on the value being displayed. I also colored the data labels for the three ranges differently to help you spot the difference; the axis labels are all black ([<0.1]0.000;[<1]0.00;0.0).
Use [Blue][<0.1]0.000;[<1]0.00;[Red]0.0 for a staggered number of decimal digits.
|
Hide Zeros in Data Labels
One of the commonly asked questions in the on-line forums is, "How do I hide data labels that show a zero value?" One easy way is to use a custom number format that provides no format for zeros. The two pie charts below have General and 0% number formats for the values and percentages of each pie slice. Notice that each has an entry for item C in the legend. Item C was zero, so it doesn't appear, but its data label (0 or 0%) shows up in between slices B and D.
The General number format shows all labels, including C's big fat zero.
| 
The 0% number format shows all five labels, including C's zero percent.
|
You can hide the zero values with a number format that has a place for a zero number format, but no actual format. I've used General;;; and 0%;;; for the same two charts to hide the zeros. Since negative numbers make no sense in a pie chart, either, I have left them out.
This chart's General;;; number format only allows display of positive numbers.
| 
The 0%;;; number format in this chart only shows positive percentages.
|
Other Number Formats
Scientific Notation
Scientific Notation is a shorthand used to keep short the number of characters in a value no matter how large or small the number is. It looks like 1.23x103, or 1.23E+3, where the number 1.23 is multiplied by 10 raised to the power 3. 1.23x103 or 1.23E+3 equals 1230. We didn't save any characters with this number, but when you consider that it's 9.3x107 miles from here to the sun, well, now we're saving some digits. Excel has no native way to write a number with superscripted notation and retain its numerical value, but neither did FORTRAN back in the good old days, so we'll use the E notation. Essentially, the E notation consists of a single digit, a decimal point, any number of decimal digits, the letter E, a plus or minus sign and an exponent. This table shows a few examples:
| #,##0 |
1,234,500
|
| 0.000E+00 |
1.235E+06
|
| 0.00E+00 |
1.23E+06
|
| 0.0E+00 |
1.2E+06
|
| 0.000E+0 |
1.235E+6
|
| 0.00E+0 |
1.23E+6
|
| 0.0E+0 |
1.2E+6
|
A variation on Scientific Notation is Engineering Notation. It uses a similar format, but its exponents are always powers of three. In this way, it shows at a glance the number of thousands (103), millions (106), billions (103), thousandths (10-3), and so on. The following table shows Scientific Notation and the "standard" Engineering Notation which increments by powers of three. It also shows how Excel can vary Engineering Notation to increment by powers of two (or other numbers), depending on how many octothorpes (I love that word!) are used in the number format.
No Format
|
Scientific
|
Engineering
| |
| 0.000E+00 | ###.000E+00 | ##.000E+00 | |
1234500000
|
1.235E+09
|
1.235E+09
|
12.345E+08
|
123450000
|
1.235E+08
|
123.450E+06
|
1.235E+08
|
12345000
|
1.235E+07
|
12.345E+06
|
12.345E+06
|
1234500
|
1.235E+06
|
1.235E+06
|
1.235E+06
|
123450
|
1.235E+05
|
123.450E+03
|
12.345E+04
|
12345
|
1.235E+04
|
12.345E+03
|
1.235E+04
|
1234.5
|
1.235E+03
|
1.235E+03
|
12.345E+02
|
123.45
|
1.235E+02
|
123.450E+00
|
1.235E+02
|
12.345
|
1.235E+01
|
12.345E+00
|
12.345E+00
|
1.2345
|
1.235E+00
|
1.235E+00
|
1.235E+00
|
0.12345
|
1.235E-01
|
123.450E-03
|
12.345E-02
|
0.012345
|
1.235E-02
|
12.345E-03
|
1.235E-02
|
0.0012345
|
1.235E-03
|
1.235E-03
|
12.345E-04
|
0.00012345
|
1.235E-04
|
123.450E-06
|
1.235E-04
|
0.000012345
|
1.235E-05
|
12.345E-06
|
12.345E-06
|
0.0000012345
|
1.235E-06
|
1.235E-06
|
1.235E-06
|
I show a technique to apply scientific notation to a fake chart axis elsewhere on this web site.
Dates and Times
Internally, Excel stores dates as a number representing the elapsed days since January 1, 1900 (actually, since December 31, 1899, because the Lotus programmers thought 1900 was a leap year, and Microsoft kept the error either because they also thought it was a leap year, or for compatibility with Lotus 1-2-3, then the spreadsheet market leader). Excel stores times as the fraction of a day since midnight. Any given date-time is a number whose integer part represents the date and whose fractional part represents the time. I am using 1:30 in the afternoon of July 4, 2004 for this illustration.
Just as there are several ways to represent numbers, there are many ways to format dates and times. You construct a date-time format by stringing together smaller building blocks. Fortunately these are pretty easy to remember, and they are not case sensitive. Any punctuation you want in the value is included in the number format.
YYYY is the four-digit year (2004) and YY is the two-digit year (04). MMMM is the month spelled out (July), MMM is the abbreviated month (Jul), MM is the two-digit month, with a leading zero if required (07), and M is the one or two digit month (7). Since M is already reserved for a single numerical digit, so Excel uses MMMMMfor a one-letter abbreviation (in the sequence J-F-M-A-M-J-J-A-S-O-N-D).
DDDD is the day spelled out (Sunday), DDD is the abbreviated day (Sun), DD is the two-digit day, with a leading zero if required (04), and D is the one or two digit day (4). Unlike MMMMM above for one character month abbreviations, there is no DDDDD format that gives just the first character of the day's name. HH is the two-digit hour (01 or 13, depending on whether AM/FM is specified), H is the one-digit hour (1 or 13), MM is the two-digit minute (30), M is the one-digit minute, SS is the two-digit second, and S is the one-digit second. AM/PM is specified for a 12-hour clock, or omitted for a 24-hour clock.
If you are dealing with times only, you can use [H] to increment hours beyond 24, [M] to increment minutes beyond 60, or [S] to increment seconds beyond 60. You wouldn't use all three in a format, you would use a format like [H]:MM:SS.
The table below illustrates how Sunday July 4, 2004 01:30 PM is displayed using various number formats:
| General |
38172.5625
|
| dddd mmmm d, yyyy hh:mm AM/PM |
Sunday July 4, 2004 01:30 PM
|
| dddd mm/dd/yyyy hh:mm |
Sunday 07/04/2004 13:30
|
| ddd dd-mmm-yyyy hh:mm AM/PM |
Sun 04-Jul-2004 01:30 PM
|
| m/d/yyyy h:mm |
7/4/2004 13:30
|
| mmddyy hhmm |
070404 1330
|
| m/d/yyyy |
7/4/2004
|
| h:mm:ss AM/PM |
1:30:00 PM
|
| dddd, mmmm dd, yyyy |
Sunday, July 04, 2004
|
| d-mmm-yy |
4-Jul-04
|
| d |
4
|
| dd |
04
|
| ddd |
Sun
|
| dddd |
Sunday
|
| m |
7
|
| mm |
07
|
| mmm |
Jul
|
| mmmm |
July
|
| mmmmm |
J
|
| y or yy |
06
|
| yyy or yyyy |
2006
|
Leading Zeros
A frequently asked question is "Why does Excel remove the zero in my zip code?" The reason is that the zero is not required to display the value of the number. However, since number formats are not about the value, they are about the appearance, you can use a special format to retain the leading zeros,00000 (use as many zeros in the number format as you want digits in your number). There is even a Zip Code number format listed in the Special category.
What if you don't want to show leading zeros? For example, you want a number to appear as .123 instead of 0.123. We saw above how a number format of#,##0 uses the pound sign to hold places that indicate the comma thousands separator should be used, but if the places only contain leading zeros, they are not used and the comma is not needed. In the same way, the pound sign in the number format #.000 holds the place for the decimal point, but if it is a leading zero, the place contains no zero digit.
You can use the pound sign after the decimal point, to indicate how many digits to show if they are not trailing zeros. The format #.### shows up to three decimal digits, except for trailing zeros. The following table shows how #.### treats various numbers. Notice that the decimal point is displayed for the first two examples even if there are no nonzero decimal digits. Also notice how zero is displayed: since the zero is a leading zero, all that is displayed is a lone decimal point.
Number
|
As displayed
using #.### |
1230
|
1230.
|
123
|
123.
|
12.3
|
12.3
|
1.23
|
1.23
|
0.123
|
.123
|
0.0123
|
.012
|
0
|
.
|
To correct the above to show a zero as a zero, large numbers without a decimal point, and small numbers without a zero preceding the decimal point, use a format like [=0]0;[>99]0;#.###. This gives the following results:
Number
|
As displayed using
[=0]0;[>99]0;#.### |
1230
|
1230
|
123
|
123
|
12.3
|
12.3
|
1.23
|
1.23
|
0.123
|
.123
|
0.0123
|
.012
|
0
|
0
|
Leading and Trailing Zeros and Spaces
Already this article has explained the use of "0" in a number format to indicate required leading zeros and decimal digits. Similarly, "#" is used as a placeholder for permitted digits, while "?" indicates either a digit, if it's a nonzero leading or trailing digit, or a space to help preserve alignment.
Use "0" to preserve leading and trailing zeros
0
|
0.0
|
0.00
|
0.000
|
00
|
000
|
00.00
|
9
|
8.5
|
8.50
|
8.500
|
09
|
009
|
08.50
|
12
|
12.3
|
12.25
|
12.250
|
12
|
012
|
12.25
|
Use "#" to allow but not require digits
#
|
#.#
|
#.##
|
#.###
|
##
|
###
|
##.##
|
9
|
8.5
|
8.5
|
8.5
|
9
|
9
|
8.5
|
12
|
12.3
|
12.25
|
12.25
|
12
|
12
|
12.25
|
Use "?" to preserve alignment (using spaces in place of trailing zeros)
?
|
?.?
|
?.??
|
?.???
|
??
|
???
|
??.??
|
9
|
8.5
|
8.5
|
8.5
|
9
|
9
|
8.5
|
12
|
12.3
|
12.25
|
12.25
|
12
|
12
|
12.25
|
Use "?" to preserve alignment (using spaces in place of leading zeros)
?
|
?.?
|
?.??
|
?.???
|
??
|
???
|
??.??
|
9
|
8.5
|
8.5
|
8.5
|
9
|
9
|
8.5
|
12
|
12.3
|
12.25
|
12.25
|
12
|
12
|
12.25
|
Labels
If you type a number with a label, for example, "15 feet", Excel interprets this entry as text, and cannot use the value in a calculation. However, if you incorporate this label into the number format (0" feet"), then the number is stored in the cell as a number, but displayed with the label. You should enclose the label in double quotes, but if the label cannot be confused with one of the formatting characters, Excel will treat it as a label (for example, M cannot be used as is for a label, since "M" represents Month or Minute in a custom format).
Number Format
|
Value
|
Display
|
| 0" feet" |
15
|
15 feet
|
| "After "d-mmm-yy |
1 August, 2006
|
After 1-Aug-06
|
| h"x"mm"x"ss |
2:34:56 AM
|
2x34x56
|
Thousands, Millions, etc.
If you use a number format like 0, Excel simply writes the number with no decimal point or decimal digits. To truncate the last three digits of a number in the thousands, append a comma to the number format, like this: 0,. Two commas drop off two sets of three digits, etc. To add a character after the number, K for thousands, append it in quotes: 0,"K". Here are a few sample formats for a number in the thousands:
Number Format
|
How 12,345.678 is displayed
|
0
|
12345
|
#,##0
|
12,345
|
0.00
|
12345.68
|
0,
|
12
|
0.00,
|
12.35
|
0,"K"
|
12K
|
0.00,"K"
|
12.35K
|
Here is a conditional number format that shows numbers as millions with an M, thousands with a K, or as numbers:
[>=1000000]0,,"M";[>=1000]0,"K";0
Here is how some numbers appear in this format:
Number
|
As displayed using
[>=1000000]0,,"M";[>=1000]0,"K";0 |
0.1
|
0
|
1
|
1
|
10
|
10
|
100
|
100
|
1,000
|
1K
|
10,000
|
10K
|
100,000
|
100K
|
1,000,000
|
1M
|
10,000,000
|
10M
|
100,000,000
|
100M
|
1,000,000,000
|
1000M
|
Special Number Formats
In addition to the 00000 Zip Code number format, Excel also has 00000-0000 for Zip-Plus-Four, 000-00-0000 for Social Security numbers, and (000)000-0000 for telephone numbers. If you live in a location that needs different codes, you can either design your own custom number formats, or hope that Microsoft has included them in your international version of Excel.
No comments:
Post a Comment