Deciphering the Excel 2003 Custom Number Formats
For the most part, the different number categories in the Category list box and their types are pretty easy - if not a breeze - to comprehend. For most people, that self-assured feeling goes right out the window as soon as they click the Custom category and get a load of its
accompanying Type list box, shown in Figure 1. It starts off with the nice word General, then 0, then 0.00, and after that, all hell breaks loose! Codes with 0s and #s (and other junk) start to appear, and it only goes downhill from there.
Figure 1: Selecting Custom in the Category list box of the Number tab.
As you move down the list, the longer codes are divided into sections separated by semicolons and enclosed within square brackets. Although at first glance these codes appear as so much gibberish, you’ll actually find that they’re quite understandable (well, would you believe useful, then?).
And these codes can be useful, especially after you understand them. You can use them to create number formats of your own design. The basic keys to understanding number format codes are as follows:
* Excel number formats use a combination of 0, ?, and # symbols with such punctuation as dollar signs, percent signs, and commas to stand for the formatted digits in the numbers that you format.
* The 0 is used to indicate how many decimal places (if any) are allowed in the format. The format code 0.00 indicates that two decimal places are used in the number. The format code 0 alone indicates that no decimal places appear (the display of all values is rounded up to whole numbers).
* The ? is used like the 0, except that it inserts spaces at the end as needed to make sure that values line up on the decimal point. For example, by selecting the number format 0.??, such values as 10.5 and 24.71 line up with each other in their cells because Excel adds an extra space after the 5 to push it over to the left so that it’s in line with the 7 of 71. If you used the number format 0.00 instead, these two values would not line up on the decimal point when they are right-aligned in their cells.
* The # symbol is used with a comma to indicate that you want thousands, hundred thousands, millions, zillions, and so on in your numbers, with each group of three digits to be separated with a comma.
* The $ (dollar sign) symbol is added to the beginning of a number format if you want dollar signs to appear at the beginning of every formatted number.
* The % (percent sign) symbol is added to the end of the number format if you want Excel to actually transform the value into a percentage (multiplying it by 100 and adding a percent sign).
Number formats can specify one format for positive values, another for negative values, a third for zero values, and even a fourth format for text in the cells. In such complex formats, the format codes for positive values come first, followed by the codes for negative values, and a semicolon separates each group of codes. Any format codes for how to handle zeros and text in a cell come third and fourth, respectively, in the number format, again separated by semicolons. If the number format doesn’t specify special formatting for negative or zero values, these values are automatically formatted like positive values. If the number format doesn’t specify what to do with text, text is formatted according to Excel’s default values. For example, look at the following number format:
#,##0_);(#,##0)
This particular number format specifies how to format positive values (the codes in front of the semicolon) and negative values (the codes after the semicolon). Because no further groups of codes exist, zeros are formatted like positive values, and no special formatting is applied to text.
If a number format puts negative values inside parentheses, the positive number format portion often pads the positive values with a space that is the same width as a right parenthesis. To indicate this, you add an underscore (by pressing Shift and the hyphen key) followed immediately by a closed parenthesis symbol. By padding positive numbers with a space equivalent to a right parenthesis, you ensure that digits of both positive and negative values line up in a column of cells.
You can assign different colors to a number format. For example, you can create a format that displays the values in green (the color of money!) by adding the code [GREEN] at the beginning of the format. A more common use of color is to display just the negative numbers in red (ergo the saying “in the red”) by inserting the code [RED] right after the semicolon separating the format for positive numbers from the one for negative numbers. Color codes include [BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW], and [COLOR n] where n is the number of the Standard Color between 1 and 16 that you want to select from the Excel color palette. (To see the palette and its colors, choose Tools –> Options and then select the Color tab. When assigning a number to a color, count from left to right across each row: 1 to 8 across the top row and 9 to 16 across the second row, and so on.)
Date number formats use a series of abbreviations for month, day, and year that are separated by characters, such as a dash (-) or a slash (/). The code m inserts the month as a number: mmm inserts the month as three-letter abbreviation, such as Apr or Oct, and mmmm spells out the entire month, such as April or October. The code d inserts the date as a number: dd inserts the date as a number with a leading zero, such as 04 or 07; ddd inserts the date as a three-letter abbreviation of the day of the week, such as Mon or Tue; and dddd inserts the full name of the day of the week, such as Monday or Tuesday. The code yy inserts the last two digits of the year, such as 05 or 07; yyyy inserts all four digits of the year, such as 2005, 2007, and so on.
Time number formats use a series of abbreviations for the hour, minutes, and seconds. The code h inserts the number of the hour; hh inserts the number of the hour with leading zeros, such as 02 or 06. The code m inserts the minutes; the code mm inserts the minutes with leading zeros, such as 01 or 09. The code s inserts the number of seconds; ss inserts the seconds with leading zeros, such as 03 or 08. Add AM/PM or am/pm to have Excel tell time on a 12-hour clock, and add either AM (or am) or PM (or pm) to the time number depending upon whether the date is before or after noon. Without these AM/PM codes, Excel displays the time number on a 24-hour clock, just like the military does. (For example, 2:00 PM on a 12-hour clock is expressed as 1400 on a 24-hour clock.)
So that’s all you really need to know about making some sense of all those strange format codes that you see when you select the Custom category on the Number tab of the Format Cells dialog box.
Article : Deciphering the Excel 2003 Custom Number Formats posted on AllBusiness.com
From the Excel 2003 All-in-One Desk Reference For Dummies.
Technorati Tags: Excel, Formatting
