Microsoft Office Tutorials and References
In Depth Information
Formatting numbers
For example, suppose you are tracking accounts receivable balances. To display accounts
with balances of more than $50,000 in blue, negative values in parentheses and in red, and
all other values in the default color, create this format:
[Blue][>50000]$#,## 0.00_);[Red][<0]($# ,##0.00);$#,##0.00_)
Using these conditional operators can also be a powerful aid if you need to scale numbers.
For example, if your company produces a product that requires a few milliliters of a
compound for each unit and you make thousands of units every day, you need to convert from
milliliters to liters and kiloliters when you budget the use of this compound. Excel can make
this conversion with the following numeric format:
[>999999]#,##0,," kl";[>999]##," L";#" ml"
The following table shows the effects of this format on various worksheet entries:
Entry
Display
72
72 ml
7286957
7 kl
7632
8 L
As you can see, using a combination of conditional formats, the thousands separator, and
text with spaces within quotation marks can improve both the readability and the
effectiveness of your worksheet—and without increasing the number of formulas.
The hidden number format
To hide values in a worksheet, assign a null format to them. To create a null format,
type only the semicolon separator for that portion of the format. For example, to hide
negative and zero values only, use this format:
$#,##0.00;;
To hide all entries in a cell, use this format:
;;;
The null format hides the cell contents in the worksheet, but the entry is still visible in
the formula bar and accessible via reference in formulas. To hide the cell contents so
that they don’t appear in the worksheet or the formula bar, use the worksheet and cell
protection features. For more information, see “Protecting worksheets” in Chapter 6,
“How to work a worksheet.”
Search JabSto ::




Custom Search