Scrigroup - Documente si articole

Username / Parola inexistente      

Home Documente Upload Resurse Alte limbi doc  


AccessAdobe photoshopAlgoritmiAutocadBaze de dateCC sharp
CalculatoareCorel drawDot netExcelFox proFrontpageHardware
HtmlInternetJavaLinuxMatlabMs dosPascal
PhpPower pointRetele calculatoareSqlTutorialsWebdesignWindows
WordXml

AspAutocadCDot netExcelFox proHtmlJava
LinuxMathcadPhotoshopPhpSqlVisual studioWindowsXml

Custom number formats

excel

+ Font mai mare | - Font mai mic



DOCUMENTE SIMILARE

Trimite pe Messenger
Custom number formats
Work with standard number formats
The SUM functions
Advanced formatting features


custom number formats

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:

  1. Select the cell(s), choose Format > Cells, and click the Number tab.
  2. Click Custom as the category, and then edit one of the existing custom formats or create your own.
  3. Click OK.

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]
[Blue]
[Cyan]
[Green]
[Magenta]
[Red]
[White]
[Yellow]

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
Alt+0163
Alt+0165
Alt+0128

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




m
mm
mmm
mmmm
mmmmm

Normally m stands for months, except when used following an h; then it stands for minutes.
Months 1 through 12
Months 01 through 12
Months as Jan-Dec
Months as January-December
Months as the first letter of the month

d

d
dd
ddd
dddd

Days as 1-31
Days as 01-31
Days as Sun-Sat
Days as Sunday-Saturday

y

yy
yyyy

Years as 00-99
Years as 1900-9999

h

h
hh

Hours as 0-23
Hours as 00-23

m

m
mm

Minutes as 0-59
Minutes as 00-59

s

s
ss

Seconds as 0-59
Seconds as 00-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 numbers

For the positive portion, we'd enter this:

#0.000

This 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/yyyy

The time code would be:

H:mm AM/PM

Putting the two together, we get:

Mm/dd/yyyy h:mm AM/PM

This 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



DISTRIBUIE DOCUMENTUL

Comentarii


Vizualizari: 539
Importanta: rank

Comenteaza documentul:

Te rugam sa te autentifici sau sa iti faci cont pentru a putea comenta

Creaza cont nou

Termeni si conditii de utilizare | Contact
© SCRIGROUP 2021 . All rights reserved

Distribuie URL

Adauga cod HTML in site