Creating a
Custom Number Format
Excel has several built-in number
formats and you can create your own specialised formats using format codes.
Custom number formats are stored at the
workbook level and you will have to incorporate them into a template or style
to use them in another workbook.
The cell number format never affects the cell's
underlying true value.
Number format codes have up to four sections, each
separated by a semicolon:
Positive Numbers; Negative Numbers; Zero Values; Text
Values
You do not have to include all four
sections, should you use only one section then all number types will use that
format. Text is only affected by a number format when the last section is used.
Use the semicolon as a positional marker
if you wish to leave out particular sections, for example:
Positive Numbers;;; Text Values
Here is an example of a number format
code that uses all four sections: #,##0.00_); (#,##0.00); "-";
"Enter a number"
The code shows positive numbers with a
comma separator for the thousands, two decimal places and aligns the number in
the cell to allow for the width of a closing parenthesis so that positive and
negative numbers in a column will line up precisely at the decimal point.
Negative values have a comma separator,
two decimal places and are enclosed in parentheses. Zero values are shown as a
dash. Any text entered into the cell is displayed as "Enter a
number", regardless of the text entered.
In the illustration, the code being created rounds
the display of all numbers to round millions to one decimal place and places
the text "mil" at the end of the number.
The precision of the number in the cell
is not changed in any way. The value in the cell is five hundred million but it
appears as "500.0 mil".
Excel number formats are very flexible
and it is usually possible to display a value in a cell in any way that is
required by using the format codes in various combinations.
However, numeric values that are
contained in formulas that return a text value to the cell are not affected by
the number format of the cell and in these cases the formatting code has to be
applied in the formula itself.
Number Format Codes.
For Further Course Details Visit Our Homepage
Microsoft Accredited IT Training Provider
Microsoft Certified Training Materials
IT Training Courses delivered by Microsoft Accredited Facilitators
Microsoft MOS Test Centre London
