|   Excel comes with built-in number formats, but sometimes you need to use a number format that is not built into Excel. Using the hacks in this section, you can create number formats that you can customize to meet your needs . Before you try these hacks, it helps if you understand how Excel sees cell formats. Excel sees a cell 's format as having the following four sections (from left to right): Positive Numbers, Negative Numbers , Zero Values, and Text Values . Each section is separated by a semicolon (;). When you create a custom number format, you do not have to specify all four sections. In other words, if you include only two sections, the first section will be used for both positive numbers and zero values, while the second section will be used for negative numbers. If you include only one section, all number types will use that one format. Text is affected by custom formats only when you use all four sections; the text will use the last section.  | Don't interpret the word number to mean custom formats applying to numeric data only. Number formats apply to text as well. | |
The custom number format shown in Figure 2-18 is Excel's standard currency format, which shows negative currencies in red. We modified it by adding a separate format for zero values and another one for text. If you enter a positive number as a currency value, Excel will format it automatically so that it includes a comma for the thousands separator, followed by two decimal places. It will do the same for negative values, except they will show up in red. Any zero value will have no currency symbol and will show two decimal places. If you enter text into a cell, Excel will display the words "No Text Please," regardless of the true underlying text. Figure 2-18. Custom number format sections  It is important to note that formatting a cell's value does not affect its underlying true value. For example, type any number into cell A1. Select Format  Cells Number Custom, and using any format as a starting point, type "Hello" (with the quotation marks). Then click OK. Although the cell displays the word Hello , you can see its true value by selecting the cell and looking in the Formula bar, or by pressing F2. If you were to reference this cell in a formulae.g., =A1+20 the result cell would take on the custom format. If you were to reference cell A1 along with many other cells that have any standard Excel formate.g., =SUM(A1:A10) the result cell would still take on the custom format of cell A1. Excel is taking an educated guess that you want the result cell formatted the same way as the referenced cell(s). If the referenced cells contain more than one type of format, any custom format will take precedence. This means you must always remember that Excel uses a cell's true value for calculations, and not its displayed value. This can create surprises when Excel calculates based on cells that are formatted for no decimal places or for few decimal places, for instance. To see this in action, enter 1.4 in cell A1 and 1.4 in cell A2, format both cells to show zero decimal places, and then place =A1+A2 into a cell. The result, of course, is 3 , as Excel rounds.
The default format for any cell is General. If you enter a number into a cell, Excel often will guess the number format that is most appropriate. For example, if you enter 10% into a cell, Excel will format the cell as a percentage. Most of the time, Excel guesses correctly, but sometimes you need to change it. | | When using Format Cells, resist the temptation to force a left, right, or center horizontal format! By default, numbers are right-aligned and text is left-aligned. If you leave this alone, you can tell at a glance whether a cell is text or numeric, as in the case of the earlier example in which cell A1 appears to hold text, when in fact, it holds a number. | |
Each section of a given format uses its own set of formatting codes. These codes force Excel to make data appear how you want it to appear. So, for instance, suppose you want negative numbers to appear inside parentheses, and all numbers, positive, negative, and zero, to show two decimal places. To do this, use this custom format: 0.00_ ;(-0.00) If you also want negatives to show up in red, use this custom format: 0.00_ ;[Red](-0.00) Note the use of the square brackets in the preceding code. The formatting code tells Excel to make the number red. You can use many different formatting codes within sections of a custom format. Table 2-1 through Table 2-5, derived from Microsoft documentation, explain these codes. Table 2-1. Formatting codes | Number code | Description | | General | General number format. | | 0 (zero) | A digit placeholder that pads the value with zeros to fill the format. | | # | A digit placeholder that does not require extra zeros to be displayed. | | ? | A digit placeholder that leaves a space for insignificant zeros but does not display them. | | % | A percentage. Excel multiplies by 100 and displays the % character after the number. | | , (comma) | A thousands separator. A comma followed by a placeholder scales the number by 1,000. | | E+ E- e+ e- | Scientific notation. | Table 2-2. Text codes | Text code | Description | | $ - + / ( ) : and blank space | These characters are displayed in the number. To display any other character, enclose the character in quotation marks or precede it with a backslash. | | \ character | This code displays the character you specify. Note that typing ! , ^ , & , ' , ~ , { , } , = , < , or > automatically places a backslash in front of the character. | | " text " | This code displays the text between the quotes. | | * | This code repeats the next character in the format to fill the column width. Only one asterisk per section of a format is allowed. | | _ ( underscore ) | This code skips the width of the next character. This code is commonly used as _) to leave space for a closing parenthesis in a positive number format when the negative number format includes parentheses. This allows both positive and negative values to line up at the decimal point. | | @ | A placeholder for text. | Table 2-3. Date codes | Date code | Description | | M | A month represented as a number without leading zeros (1-12) | | Mm | A month represented as a number with leading zeros (01-12) | | Mmm | A month given as an abbreviation (Jan-Dec) | | Mmmm | An unabbreviated month (January-December) | | D | A day represented without leading zeros (1-31) | | Dd | A day represented with leading zeros (01-31) | | Ddd | A weekday represented as an abbreviation (Sun-Sat) | | Dddd | An unabbreviated weekday name (Sunday-Saturday) | | Yy | A year given as a two-digit number (for example, 96) | | Yyyy | A year given as a four-digit number (for example, 1996) | Table 2-4. Time codes | Time code | Description | | H | Hours given as a number with no leading zeros (0-23) | | Hh | Hours given as a number with leading zeros (00-23) | | m | Minutes given as a number with no leading zeros (0-59) | | mm | Minutes given as a number with leading zeros (00-59) | | s | Seconds given as a number with no leading zeros (0-59) | | ss | Seconds given as a number with leading zeros (00-59) | | AM/PM am/pm | Time of day based on a 12- hour clock | Table 2-5. Miscellaneous codes | Miscellaneous Code | Description | | [BLACK], [BLUE], [CYAN], [GREEN] , [MAGENTA] , [RED], [WHITE], [YELLOW] , [ COLOR n ] | These codes display characters in the specified colors. Note that n is a value from 1 to 56 and refers to the nth color in the color palette. | | [ Condition value ] | Condition can be < , > , = , >= , <= , or <> , while value can be any number. A number format can contain up to two conditions. | Note in particular the last kind of formatting codes in Table 2-5: the comparison operators. Assume you want the custom number format 0.00_ ;[Red](-0.00) to display negative numbers in a red font and in brackets only if the number is less than -100 . To do this, use the following: 0.00_ ;[Red][<-100](-0.00);0.00 The formatting codes [Red][<-100](-0.00) placed in the section for negative numbers make this possible. Using this method in addition to conditional formatting you can double the number of conditional format conditions available from three to six. Often, users want to display dollar values as words. To do this, use the following custom format: 0 "Dollars and" .00 "Cents" This format will force a number entered as 55.25 to be displayed as 55 Dollars and .25 Cents . If you want to convert numbers to dollars and cents, consult these two custom functions from Microsoft: http://www.ozgrid.com/VBA/ValueToWords.htm and http://www.ozgrid.com/VBA/CurrencyToWords.htm. You can also use a custom format to display the words Low, Average, or High, along with the number entered. Simply use this formatting code: [<11]"Low"* 0;[>20]"High"* 0;"Average"* 0 Note the use of the * . This repeats the next character in the format to fill the column width, meaning that all the Low, Average, or High text will be forced to the right, while the number will be forced to the left. |
