Tom’s Tutorials For Excel: Comparing Two Lists and Formatting Differences

Tom’s Tutorials For Excel: Comparing Two Lists and Formatting Differences

Here’s how you can format differences in a long list when comparing it to a shorter list.

In the picture, column A holds a comprehensive list of names, and column B contains a shorter list where some names are found in column A (colored green), and some are not (colored pink).



Step 1 for Excel versions 2003 or before
Select the range holding the long list (A4:A22 in this example).
From your worksheet menu bar, click Format > Conditional Formatting.



Step 2 for Excel versions 2003 or before
Enter your formula rules into the Conditional Formatting dialog.
The formula for common entries between the two lists for green-colored cells is
=COUNTIF($B$4:$B$22,A4)>0
The formula for entries not found in both lists for pink-colored cells is
=COUNTIF($B$4:$B$22,A4)=0
Click the Format button to select a color from the palette (shown later in this tutorial).



For versions 2007 and 2010:
More steps are required to reach the same point as version 2003.

Step 1 for Excel versions 2007 or after
From the Home tab on the Ribbon, Styles section > Conditional Formatting > Manage Rules.



Step 2 for Excel versions 2007 or after
In the Conditional Formatting Rules Manager dialog, select Current Selection from the drop-down menu, and click New Rule.



Step 3 for Excel versions 2007 or after
In the New Formatting Rule dialog, select “Use a formula to determine which cells to format”, and enter your formula rule (same as the formulas in Step 2 for version 2003). Click the Format button to select a color from the palette.



Final step, all versions
Click the Fill tab (called the Patterns tab in versions 2003 or before), select your desired color from the palette and click OK.