Microsoft Office Tutorials and References
In Depth Information
Tip 59: Comparing Two Ranges by Using Conditional Formatting
5. Click the Format button and specify the formatting to apply when the condition is true (a
different fill color).
6. Click OK.
Figure 59-3 shows the result. Names that are in the old list but not in the new list are highlighted. In
addition, names in the new list that aren’t in the old list are highlighted in a different color. Names
that aren’t highlighted appear in both lists.
Figure 59-3: Conditional formatting causes differences in the two lists to be highlighted.
Both of these conditional-formatting formulas use the COUNTIF function. This function counts the
number of times a particular value appears in a range. If the formula returns 0, it means that the item
doesn’t appear in the range. Therefore, the conditional formatting kicks in and the cell’s background
color is changed.