Comparing Lists with Conditional Formatting
Comparing lists is a fairly common task in Excel, and as with anything, there are many ways to approach it. When we compare two lists, we are essentially trying to find out which names appear in both lists, or, which names appear in only one list. Tony posted a blog comment about how to perform list comparisons with conditional formatting, and I’d like to share his tip with you now. Thanks Tony!
I’ve prepared both a short video and full narrative for reference below.
Video
Objective
Before we get too far, let’s just be clear about our objective. We have two lists in Excel. In this illustration, we’ll use a bank rec as the example. One list contains the checks from the check register, and the other list is the bank activity download. These are shown below.
As we compare these lists to do our bank reconciliation, we have a couple of key questions. For example, which checks have cleared the bank? Which checks are still outstanding? Are there any transactions that the bank has recorded that are not in the books? These are all questions that are easily answered when we perform a list comparison. Essentially, we need to figure out which items appear on both lists, and, which items appear on only one list.
As with anything in Excel, there are many ways to accomplish this, for example, we could use formulas or Power Query. But, Tony’s approach uses one of my favorite features in Excel: Conditional Formatting. Check it out.
Conditional Formatting
In summary, conditional formatting formats cells based on rules you define. The rules can be simple, such as any cells greater than 0, or, they can be more complex.
One of the built-in rules is related to duplicate values. We can ask Excel to format duplicate values or unique values. As you can imagine, formatting the duplicate values between the lists will identify which items appear in both lists. And, formatting the unique values will identify which items appear in only one list.
To begin, you must select the list columns you want to compare. In our illustration, it would be just the check number columns and not the amount columns. To select two separate ranges, you select the first range, hold down the Ctrl key on your keyboard, and then select the second range.
At this point, the two columns you want to compare should be selected, as shown below.
Now you are ready for the magic!
To identify the items that appear in both lists, use:
- Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values
This will open the Duplicate Values dialog, where we tell Excel to format cells that contain Duplicate values with Green Fill, as shown below.
When we hit OK … bam! We can easily see which items appear in both lists, that is, which checks have cleared the bank.
But, let’s say that instead, we wanted to identify items that appear in only one list. No problem.
We can simply remove the current rule by selecting:
- Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet
Next, we select our two columns again, and then head back to:
- Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values
This displays the Duplicate Values dialog, where we opt to format the cells that contain Unique values with a Red fill, as shown below.
And, as you suspect, we can easily see which items appear in only one list:
Now, if these lists are short, then, it is easy to see the outstanding checks. But, what if these lists are long? No problem, we can easily filter by color. Here’s how.
Filter by Color
Right-click one of the outstanding checks (with Red fill) and select:
- Filter > Filter by Selected Cell’s Color
Instantly, Excel hides the cleared checks and leaves you with a list of the outstanding checks only, as shown below.
Conclusion
Hopefully this technique will help you save some time. If you want to check out the sample file, use the link below. If you have any other related tips, please share by posting a comment below. Again … thanks Tony!
- Sample File: CompareListsCF.xlsx
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.
That was a great tip! Thank you Jeff! and Tony!
I love using conditional formatting in this way, but I do find that it has quite a significant impact on my file size, and therefore the speed taken to run other queries in the document. Any tips on keeping the file size down would be much appreciated. I regularly use the “clear all” on cells surrounding my tables, because they have a tendency to be remembering formatting from a time when I temporarily added a column, or had more rows.
Jeff – It appears the 2 data ranges need to be on the same worksheet. I sometimes need to compare thousands of rows from 2 different reports. Is there a way to do this if the data is on separate worksheets in the same workbook? Or if in separate workbooks? A search gave no results for how to select cells on different sheets. I’ve tried various key combos with no luck.
I could copy the 2nd set of data to the bottom of the 1st with some rows between. The data headers do not match, so if I can align the data to be compared in the same column then selecting the column for Conditional Formatting would be easy.
Any other suggestions?
This technique works when the lists are on the same worksheet … there are other techniques that we can use when the lists are on separate sheets, including https://www.excel-university.com/articles/cal-cpa/comparing-spreadsheet-lists-with-ease/ and https://www.excel-university.com/get-transform-an-alternative-to-vlookup-list-comparisons/
Thanks
Jeff
Any tips for comparing rows of data in two separate worksheets? I actually have separate workbooks but could put the worksheets in the same workbook if that’s the only way.
This technique works when the lists are on the same worksheet … there are other techniques that we can use when the lists are on separate sheets, including https://www.excel-university.com/articles/cal-cpa/comparing-spreadsheet-lists-with-ease/ and https://www.excel-university.com/get-transform-an-alternative-to-vlookup-list-comparisons/
Thanks
Jeff
Will this compare work with lists from separate worksheets and/or workbooks?
This technique works when the lists are on the same worksheet … there are other techniques that we can use when the lists are on separate sheets, including https://www.excel-university.com/articles/cal-cpa/comparing-spreadsheet-lists-with-ease/ and https://www.excel-university.com/get-transform-an-alternative-to-vlookup-list-comparisons/
Thanks
Jeff
Very nice video sir, i am following your videos and they are very helping in my job.
Thank you very much for your effort.
Hi there,
I am looking to format the decimal place of each cell based on values in cell. For example, if the number is > than 0.5, round off to zero decimal and if the number is in the range from 0.05<n<0.5 one decimal and if the number is range 0.005<n<0.05 two decimal and etc.
Let me know if we can do something about it.
Ganesh