Page 56 - Excel Data Analysis

04 537547 Ch03.qxd 3/4/03 11:50 AM Page 42

EXCEL DATA ANALYSIS

APPLY CONDITIONAL FORMATTING

ou can have Excel analyze the data values for specific specify whether you want conditional formatting for the cell
criteria, and apply conditional formatting when the value or a formula in the first list box in the Conditional
Y criteria are met. This comes in handy for the analyst Formatting dialog box.
who wants to emphasize data that has reached a critical To create conditional formatting for the value of a cell, you
value. For example, you can highlight net sales loses of over must select the conditional operator you want to use to
$2,000.00 so that they are readily apparent to users who check the cell contents. Following the previous example, to
view your worksheet.
change the cell formatting if the cell's value is greater than
The Conditional Formatting dialog box allows you to create $2,000.00, you select the greater than conditional
up to three different conditional formatting statements for operator.
each range selection. For example, you can create a In the value fields, you must specify the value for the
formatting to apply to cell values between 0 and 1,000, condition. You have three options for these fields. You can
another format for values between 1,001 and 5,000, and enter a constant in the field — any number, alphanumeric
then a third for values over 5,000. The Conditional value, or logical value. Alternatively, you can enter a formula
Formatting dialog box allows you to specify formatting for to return the desired value. You can also select a range that
the cells based upon either the value of a cell or a formula. contains the values by collapsing the dialog box. See
You can only specify conditional formatting using formulas Chapter 1 for more on selecting ranges. See Chapter 4 for
that return a logical value of either True or False.You
more information on formulas.
APPLY CONDITIONAL FORMATTING

Cell Value Is

⁄ Select the range of cells Note: See Chapter 1 for information ■ The Conditional Formatting ‹ Click Cell Value Is from
for the conditional on selecting a range. dialog box displays. the list of options.
formatting.
¤ Click Format ➪
Conditional Formatting.

42