# CP021: How to quickly compare 2 lists in Excel

Posted on October 2nd, 2014 in Chandoo.org Podcast Sessions , Excel Howtos - 13 comments

In the 21st session of Chandoo.org podcast, lets compare lists. Quickly

### What is in this session?

Comparing things is a favorite pastime for analysts all over the world. Sadly, it is also an area where we waste hours. So in this episode, I share my top secret comparison techniques to save you time.

Note: This is a short format podcast. That means you spend less time listening to it, while becoming more awesome.

In this podcast, you will learn,

• Why I sound like I am on a secret mission at a mafia hideout.
• 5 ways to compare 2 lists
• Manual method
• Conditional Formatting
• Row Differences
• LOOKUP formulas
• COUNTIF formulas
• Bonus tip: Removing duplicates
• Conclusions

### Links & Resources mentioned in this session:

Please go thru below links for example workbooks on each type of comparison.

### How do you compare? Tell us quickly

So how do you compare? My favorite methods are conditional formatting & COUNTIFS formulas. What about you? Please share your tips using comments.

 ABC Inventory Analysis using Excel Charmed Price Problem
 Written by Chandoo Tags: comparison, countif(), countifs, Excel 101, goto special, Learn Excel, Microsoft Excel Conditional Formatting, podcast, remove duplicates, vlookup Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 13 Responses to “CP021: How to quickly compare 2 lists in Excel”

1. Trouttrap2 says:

I did not know about using row differences from the Goto Special. That is truly awesome. Thank you for sharing. I tend to compare two rows of dates and this is perfect, and way better than eye-strain.

2. Sal says:

To compare two lists showing differences I use
=ISNA(MATCH(A1,H:H,0)) in conditional format using formula
Column A been the list with the extra data and H been the column to compare

3. Kerel says:

If I have two sets of data, I paste them underneath each other, give a criteria in a variable (e.g.: list 1, list 2). And then pull a pivot table over the whole dataset. Then you can compare both datasets on each criteria in your source data.

4. Scot says:

I have a tip to add: Using the Sort dialog box you can change the "Sort On" from sort by Values (which is the default) to sort by Cell Color, sort by Font Color or sort by Cell Icon.

So here is the awesome application: If you have many many rows and you need to check for duplicate entries in a column, of course you can apply conditional formatting. You still have the problem of scrolling through many many rows to look for the highlight color. But if you sort the data by the cell color or font color after applying conditional formatting, you can get all the duplicates to sort to the top!

5. Hi Chandoo

How a Formula can Start with =+ and what is the use of this formula.

thanks

• PK says:

You could ask this in http://chandoo.org/forum/ where the probability of getting an answer is higher. Blog posts, in general, have limited audience.

6. Doug Glancy says:

Good topic! I recently also posted about data comparison on the Daily Dose of Excel at http://dailydoseofexcel.com/archives/2014/09/22/data-comparison-tricks/.

Here's an array formula from that post for comparing two identically-sized lists. If they match cell-for-cell it returns TRUE:

=AND(A2:C10=E2:G10)

7. vijay says:

Hi Chandoo

Grt listening to your sessions. My favourite method of doing comparison of two data done with Vlookup in excel.

I do use the advanced filter to remove duplicates from the lists.

8. vijay says:

Hi Chandoo
Grt listening to your sessions. My favourite method of doing comparison of two data done with Vlookup in excel.

I do use the advanced filter to remove duplicates from the lists.

9. srikanth says:

Hi Chandoo Sir,

This is srikanth from Andhra Pradesh,
I designed some vba projects in excels,
Then I want a best job in vba, I seeing
For best package like 8 lakh per annum, I am ready to share my projects to you,
Pl suggest me if u have any jobs.

My contact no: 9948199009
My mail I'd : sri09948199009@gmail.com

Thanking you,

10. Rashmi says:

Hi Chandoo,

Thanks a lot for the wonderful blog. I got many useful stuffs from this blog. This blog is too good. One thing if possible try to add some tips on Microsoft Project (MSP), S-Curve etc for project management professional. It would be help full for us.
Here i required some help, i prepared an excel sheet which contain my project status like many activities( which are directly related to my project). I want to put automatic tick when one activities got completed and at same time the status must got changed. I tried through conditional formatting but i didn't get the required result.
Can please guide me in this matter.

11. samuel says:

that is a good masterpiece