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.
Search JabSto ::

Custom Search