CATEGORII DOCUMENTE |
Asp | Autocad | C | Dot net | Excel | Fox pro | Html | Java |
Linux | Mathcad | Photoshop | Php | Sql | Visual studio | Windows | Xml |
DOCUMENTE SIMILARE |
|||||
|
|||||
This lesson's saved the Custom number format for last because it's the most complex and powerful. With it, you can create your own number formatting rules.
First, the basic procedure:
The key to custom number formats is not the simple process just described, of course. The important part is to know how to construct the coding for various custom formats. There are different symbols for numbers, colors, currency symbols, dates, and other uses.
TIP
For a complete education on custom number formats,
look up Number Format Codes in Excel's Help system. We present an abbreviated
lesson on them here.
The following tables show the characters you need for custom number formats. You may want to print these for later reference.
Symbol |
Example |
Purpose |
Shows significant digits but does not show insignificant zeros. For example, if the number were 0234.20, it would show it as 234.2. |
||
Shows all digits, including insignificant ones, up to the number of digits in the format. For example, if the number entered were 000234.200, it would show 0234.20. |
||
Adds spaces for insignificant zeros so that decimal points will align when formatted with a fixed-width font. For example, if the number were 1234.5, it would show it just like that but would leave two blank spaces after the 5. |
Table 4-2: Numeric symbols in custom number formats.
Symbol |
Example |
Purpose |
Color name |
[Black] |
Makes the number appear in the specified color. |
Repeats whatever character follows it until it reaches the right margin of the cell. The example shown here would fill any remaining space in the cell with dashes. |
||
Adds a space that is the same width as the character typed after it would be. The example shown here would leave a space that's the same width as the close-parenthesis character. This might be useful for making numbers align where some of them are negative in parentheses and some are not. |
Table 4-3: Colors and spacing in custom number formats.
Symbol |
Example |
Purpose |
Comma |
Use to indicate the thousands separator |
|
Percent |
Displays the numbers as a percentage of 100. For example, if you enter .04, it would display as 4%. If you enter 2, it would display as 200%. |
|
Alt+0162 |
These alternative currency symbols cannot be typed normally on the keyboard, so you must enter them via the numeric keypad with an ANSI code. To do so, hold down the Alt key, type the number, and then release the Alt key |
Table 4-4: Symbols in custom number formats.
Symbol |
Example |
Purpose |
m |
|
Normally m stands for months, except when
used following an h; then it stands for minutes. |
d |
d |
Days as 1-31 |
y |
yy |
Years as 00-99 |
h |
h |
Hours as 0-23 |
m |
m |
Minutes as 0-59 |
s |
s |
Seconds as 0-59 |
AM/PM |
h:mm AM/PM |
If you don't use this, you'll get a 24-hour clock on the hours and minutes |
Table 4-5: Dates and times in custom number formats.
You can also use math comparison operators in the custom number formats, to set up an 'if-then' condition. For example, you could use this to format a number in red if it is less than or equal to 100, or in blue if it is greater than 100:
[Red][<=100];[Blue][>100]custom number formatting examples
Suppose you want to show numbers with a comma separator and exactly three decimal places, and you want negative numbers to appear in green and to have parentheses around them. You would write a custom number format with this syntax:
Positive numbers;negative numbersFor the positive portion, we'd enter this:
#0.000This tells Excel that we want at least one digit to the left of the decimal point and at least three to the right of it. The 0s stand for those required digits. The # signs stand for digits that will appear only if they're significant. The # signs would not be necessary except that we need them as placeholders to show where the comma goes.
For the negative portion, the same thing but with a color code, from Table 4-3, and the parentheses:
[Green](#,##0.000)If we want the positive and negative numbers to align neatly in the column, we should add a spacer to the positive numbers of the same width as the parenthesis (again see Table 4.3), so the positive number would change to this:
#0.000_)The complete number format would look like this:
#0.000_);[Green](#,##0.000)Now let's try one with a date. Suppose you want the date to appear like this: 08/05/1964, and you want the time to appear as 4:07 AM.
The date code would be:
mm/dd/yyyyThe time code would be:
H:mm AM/PMPutting the two together, we get:
Mm/dd/yyyy h:mm AM/PMThis shows the time even if you do not input a time. If there is no time input, it shows 12:00 AM.
Politica de confidentialitate | Termeni si conditii de utilizare |
Vizualizari: 912
Importanta:
Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved