Microsoft Office Tutorials and References
In Depth Information
Tip 59: Comparing Two Ranges by Using Conditional Formatting
Comparing Two Ranges by Using
A common task is comparing two lists of items to identify differences between the two lists. Doing it
manually is far too tedious and error-prone, but Excel can make it easy. This tip describes a method
that uses conditional formatting.
Figure 59-1 shows an example of two multicolumn lists of names. Applying conditional formatting
can make the differences in the lists become immediately apparent. These list examples contain text,
but this technique also works with numeric data.
Figure 59-1: You can use conditional formatting to highlight the differences in these two ranges.
The first list is in A2:A20, and this range is named OldList . The second list is in C2:C20, and the range is
named NewList . The ranges were named by using the Formulas ➜ Defined Names ➜ Define Name
command. Naming the ranges isn’t necessary, but it makes them easier to work with.
Start by adding conditional formatting to the old list:
1. Select the cells in the OldList range.
2. Choose Home ➜ Conditional Formatting ➜ New Rule to display the New Formatting Rule
3. In the New Formatting Rule dialog box, click the option labeled Use a Formula to Determine
Which Cells to Format.