Microsoft Office Tutorials and References
In Depth Information
You can specify the following color names in your formats: Black, Blue, Cyan, Green,
Magenta, Red, White, and Yellow. You can also specify a color as COLOR n , where n is a
number in the range 1 through 16. Excel selects the corresponding color from your
worksheet’s current 16-color palette.
If you define colors that are not among your system’s repertoire of solids, Excel
produces them by mixing dots from solid colors. Such blended colors, which are said to
be dithered, work well for shading. But for text and lines, Excel always uses the nearest
solid color rather than a dithered color.
Decimal points in my Currency formats don’t line up
Sometimes when you use Currency formats with trailing characters, such as the French
Canadian dollar (23.45 $), you want to use the GAAP practice of using currency symbols
only at the top and bottom of a column of numbers. The numbers between should
not display any currency symbols, so how do you make all the decimal points line up
You can create a custom format code to apply to the noncurrency format numbers in
the middle of the column. An underscore character (_) in the format code tells Excel
to leave a space that is equal in width to the character that follows it. For example, the
code _$ leaves a space equal to the width of the dollar sign. Thus, the following code
does the trick for you:
#,##0.00 _$;[Red]#,##0.00 _$
Make sure you add a space between the zeros and the underscores to properly line the
numbers up with the built-in French Canadian dollar format.
Using custom format conditional operators You can create custom formats that are
variable. To do so, you can add a conditional operator to the first two parts of the standard
four-part custom format. This, in effect, replaces the positive/negative formats with either/
or formats. The third format becomes the default format for values that don’t match the
other two conditions (the “else” format). You can use the conditional operators <, >, =, <=,
>=, and <> with any number to define a format.