Comparing Two Ranges in Excel – Part 1 Conditional Formatting

At the PMI Professional Development Day event, our instructors were asked for technology tips for various platforms. We wanted to share one of those questions and three tips for how to complete the action. The question was in regards to comparing two ranges in Excel. There are numerous ways to do this and here in part 1, we’ll explain Conditional Formatting. We’ll post part 2 and 3, Vlookup function and Match function, in the next week.

Please use this Excel template file for the practice exercise below.  This Excel file is for the results.

Using Conditional Formatting
With the first example, name each range of cells. Naming the ranges is not mandatory but it makes it easier to work with. Next we’ll create a formula in Conditional Formatting that counts if a cell is in one list but not the other and add a fill color to that cell. This is needed for both lists. When done, the items that are not in both lists will be filled in with color. For this example, we have a list of cities from a2:a19 and a second list of cities from e2:e20.

1. Select the first range of cells and name it List1.CFCell
a. Highlight a2:a19
b. Click in the Name Box. Type List1 and press Enter. (It is important that you not place a space between List and the number 1 when naming a range.)

2. Select the second range of cells and name it List2.
a. Highlight e2:e20
b. Click in the Name Box. Type List2 and press Enter.

3. Select the cells in List1 and add Conditional Formatting.CFEditFormattingRule
a. Select a2:a19
b. On the Home ribbon tab, in the Styles group, click Conditional Formatting, New Rule.
c. In the New Formatting Rule box, select Use a formula to determine which cells to format.
d. In the Format values where this formula is true box type the following formula:
=countif(list2,a2)=0
e. Click the Format button. Click on the Fill tab and select a color to fill in the cell. Click OK. Click OK again to exit the Edit Formatting Rule box.

4. Select the cells in List2 and add Conditional Formatting.
a. Select e2:e20CFNewFormattingRule
b. On the Home ribbon tab, in the Styles group, click Conditional Formatting, New Rule.
c. In the New Formatting Rule box, select Use a formula to determine which cells to format.
d. In the Format values where this formula is true box type the following formula:
=countif(list1,e2)=0
e. Click the Format button. Click on the Fill tab and select a color to fill in the cell. Click OK. Click OK again to exit the Edit Formatting Rule box.

The items that are NOT in both lists will appear in color.

Note: You can filter the Conditional Formatting to only show the items that are not on bothCFFilter lists. To do this, right-click a cell that has a cell color, select Filter, Filter by Selected Cell’s Color.

Excel is a very powerful tool and there are lots of options for looking at the data the way you need to see it. We have plenty of tips and tricks to show you for this tool and other Microsoft Office products. Check out the Excel courses we offer or view our entire Class Catalog.