Tip 59: Comparing Two Ranges by Using Conditional Formatting
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...