Excel 2010 Bible

Part III: Creating Charts and Graphics

496

FIGURE 20.15

Creating a conditional formatting rule based on a formula.

Generally, when entering a conditional formatting formula for a range of cells, you’ll use a refer-

ence to the active cell, which is typically the upper-left cell in the selected range. One exception is

when you need to refer to a specific cell. For example, suppose that you select range A1:B10, and

you want to apply formatting to all cells in the range that exceed the value in cell C1. Enter this

conditional formatting formula:

=A1>$C$1

In this case, the reference to cell C1 is an absolute reference; it will not be adjusted for the cells in

the selected range. In other words, the conditional formatting formula for cell A2 looks like this:

=A2>$C$1

The relative cell reference is adjusted, but the absolute cell reference is not.

Conditional formatting formula examples

Each of these examples uses a formula entered directly into the New Formatting Rule dialog box,

after selecting the Use a Formula to Determine Which Cells to Format rule type. You decide the

type of formatting that you apply conditionally.

Identifying weekend days

Excel provides a number of conditional formatting rules that deal with dates, but it doesn’t let you

identify dates that fall on a weekend. Use this formula to identify weekend dates:

=OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1)

This formula assumes that a range is selected and that cell A1 is the active cell.