Microsoft Office Tutorials and References

In Depth Information

**Conditional formatting formula examples**

Notice that the formula entered in Step 4 contains a relative reference to the upper-left cell

in the selected range.

Generally, when entering a conditional formatting formula for a range of cells, you’fill use a

reference 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 speciﬁ c 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)

19

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

Highlighting a row based on a value

Figure 19.23 shows a worksheet that contains a conditional formula in the range A3:G28.

If a name entered in cell B1 is found in the ﬁ rst column, the entire row for that name is

highlighted.