Excel 2010 Bible

Part IV: Using Advanced Excel Features

558

The following is an example of a custom number format that specifies a different format for each of

these types:

[Green]General;[Red]General;[Black]General;[Blue]General

This custom number format example takes advantage of the fact that colors have special codes. A

cell formatted with this custom number format displays its contents in a different color, depending

on the value. When a cell is formatted with this custom number format, a positive number is

green, a negative number is red, a zero is black, and text is blue.

Cross-Reference
If you want to apply cell formatting automatically (such as text or background color) based on the cell’s con-
tents, a much better solution is to use the Excel Conditional Formatting feature. Chapter 20 covers conditional
formatting. n

Custom number format codes

Table 24.3 lists the formatting codes available for custom formats, along with brief descriptions. I

use most of these codes in examples later in this chapter.

TABLE 24.3

Codes Used to Create Custom Number Formats

Code Comments

General Displays the number in General format.

# Digit placeholder. Displays only significant digits, and does not display insignificant zeros.

0 (zero) Digit placeholder. Displays insignificant zeros if a number has fewer digits than there are

zeros in the format.

? Digit placeholder. Adds spaces for insignificant zeros on either side of the decimal point so

that decimal points align when formatted with a fixed-width font. You can also use? for

fractions that have varying numbers of digits.

. Decimal point.
% Percentage.
, Thousands separator.
E- E+ e- e+ Scientific notation.
$ - + / ( ) :
space

Displays this character.

\ Displays the next character in the format.

* Repeats the next character, to fill the column width.

_ (underscore) Leaves a space equal to the width of the next character.