Microsoft Office Tutorials and References
In Depth Information
Comparing Two Ranges by Using Conditional Formatting
Comparing Two Ranges by Using
A common task is comparing two lists of items. Doing it manually is far too tedious and
errorprone, but Excel can make it easy. This tip describes a method that uses conditional formatting.
Figure 164-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 164-1: You can use conditional formatting to highlight the differences in these two ranges.
The first list is in A2:B31, and this range is named OldList. The second list is in D2:E31, 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