Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Even Faster Way to Compare 2 Lists in Excel [Quick Tip]

Posted on July 1st, 2010 in Excel Howtos - 95 comments

Last week we discussed a fun and easy way to compare two lists of data in excel using conditional formatting. In that post, Artem commented,

The quickest way to find all about two lists is to select them both and them click on Conditional Formatting -> Highlight cells rules -> Duplicate Values (Excel 2007). The result is that it highlights in both lists the values that ARE the same. Then in one list non-highlighted are values that are not present in the second list, and opposite for the second list. I think it is sell “geeky”, but it gets job done very very quickly when you don’t want to mess around.

Artem must be an Excel Yoda. I somehow missed this beautiful and dead-simple way to compare lists in Excel. So here, I am documenting that technique so we all remember it and use it.

A Ridiculously easy and fun way to compare 2 lists

Compare 2 lists of Data in Excel - Conditional Formatting Tip[works only Excel 2007+, use the above technique if you are on excel 2003 or earlier]

  1. Select cells in both lists (select first list, then hold CTRL key and then select the second)
  2. Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values
  3. Press ok.
  4. There is nothing do here. Go out and play!

See the screencast aside to see how this works (click here for a detailed demo).

Hats off to Artem for sharing this beautiful tip with us. Thank you :)

Spare a minute to become superawesome at work – Read a Quick Tip.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

95 Responses to “Even Faster Way to Compare 2 Lists in Excel [Quick Tip]”

  1. deepak says:

    Thank you
    I have very useful this formula.
    Thanks again!

  2. Deck says:

    Nice trick but doesn’t work on 2003′

  3. Philip Jones says:

    Great timing! I needed to do something like this just last night, and ended up doing it manually. I’d alpha-sorted two lists of names and I needed to put unique ones from one list into the other. This trick would have helped, but I’d still have had to move the lists to make room for the unique entries from one going to the other, which needed to be updated.

    Any thoughts on a way to automate this merging, so that the unique items from one list can be inserted into the other?

  4. Artem says:

    @Philip,

    Actually there is an idea: after you’re done with conditional formatting you can go in and sort the items by color. By default you will have unique items non-highlighted, so just copy filtered items into the other list to synchronize. It’s again quick and dirty. Macros generally would be much easier to work with.

  5. Bill says:

    This works great, except if there are duplicate names within each column. Excel will display these records as duplicates as well. Conditional formatting in Excel 2007 is a great tool!

    • Arturo says:

      So what do you do in this case? I experienced the same problem, I want to compare data from one list to another but not considering if they are duplicate within the same column… I only need unique (or duplicate) from column vs the other.

  6. cindi says:

    any way to do this when comparing columns from different sheets in the same workbook?

  7. Donal says:

    Now that’s clever!

  8. MiceMustDie says:

    ALT + HLHD for highlighting duplicates
    or
    ALT + HLHDU for highlighting uniques

  9. Bothwell says:

    Useful if you are checking for data in both colums or rows but the previous method of highlighting duplicates is better for a row by row comparison. Either way is quicker than writing a function.

  10. Rony says:

    Hi! you can go here to know how to make conditional lists:
    http://runakay.blogspot.com/2011/03/conditional-lists-on-excel.html

  11. Anthony Martina says:

    MASTER!! Super cool! Thank you very much.

    Kind regards,

    Anthony Martina

  12. Gayathri says:

    Saved my work by hours, very sweet trick, thanks for sharing!

  13. Lynne says:

    Thank you so much for sharing! This is extremely useful.

  14. DD says:

    THANKS FOR THIS INFORMATION, SAVED ALOT OF USEFUL TIME

  15. John D says:

    You are the bomb! Thank you for sharing – it saved me a ton of time!!!

  16. Inara says:

    you saved my day with this hint !!!! thank you very very much.

  17. Sunil Danda says:

    WOW!!!! Soooo Simple and Easy
    Hats off to you(Chandoo.org) and Team Microsoft Office…….

  18. Jason says:

    This worked perfectly!!!! Thanks buddy!

  19. JoeKisonu says:

    After seeing this and playing with it a bit, I was happy to discover that I could use this method to select multiple columns to compare. (I’m using Excel 2010)
    I had to find the rows in a second spreadsheet that were not in an original spreadsheet.
    In the first spreadsheet I selected all the rows and changed the font to red.
    Then I selected all the rows in the second spreadsheet, copied them and pasted them at the end of the rows in the first spreadsheet.
    You should now have one spreadsheet with two different colored rows – red ones from original spreadsheet, black ones from second spreadsheet. (makes them easier to identify later)
    Then I selected 4 columns to use for the comparison… that’s right, 4 columns: date, invoice#, product, qty.

    Now from the Home tab, select Conditional Formatting, ‘New Rule’, ‘Format only unique or duplicate values’. Change Format all: to ‘unique’ Select a highlight color Click OK
    I now had every row highlighted that was NOT in both files, regardless of which file it started in. Next was to sort the data by cell color to group all the ‘missing’ rows together. The color of the font also told me exactly which set of data had that row so that I could make sure I only added the right rows to the original spreadsheet.
    Now I’m going to play with it some more and see if I can get this to work without having to merge the data into one spreadsheet.

  20. Shane says:

    Hats off to your great help, keep sharing and keep helping us

  21. Sundara Murali says:

    This option works. Really smart and quick way to compare two column in EXCEL

  22. [...] Compare 2 lists quickly, Compare 2 lists – detailed [...]

  23. PTC says:

    Awesome trick.

    Would you know if there is a limit this method can handle? I have to compare 175,000 lines of a 578,000 lines list (folder security report)…and the workseet totally freezes now, even if I close Excel then open the workbook again. It seems the conditional formatting is not a ‘one shot deal’, but always a work in progress.

    Thanks,

  24. Manu says:

    You saved a lot of my time. Thanks a Ton buddy.

  25. Sajan Thomas says:

    Here is another simple way to highlight differences between two lists. This approach works with lists that are each comprised of multiple columns.
    Use the COUNTIFS formula to compare the corresponding columns in each list, and check if the total is greater than zero. If the total is non-zero, then the corresponding row is different between the two lists.

    Example:
    Worksheet1: Columns to compare: A, B, C, D
    Worksheet2: Columns to compare: E, F, G, H
    Assuming that columns A and E have similar data, columns B and F have similar data, etc.
    Put the following formula into a cell on row 1 on worksheet2
    “=countifs(WorkSheet1!$A:$A,Worksheet2!$E1, WorkSheet1!$B:$B,Worksheet2!$F1, WorkSheet1!$C:$C,Worksheet2!$G1, WorkSheet1!$D:$D,Worksheet2!$H1)=0″ will return TRUE if the values in row 1 on Worksheet2 are missing from Worksheet1.

    You can set this formula in a conditional format rule to highlight the missing rows on Worksheet2, if desired.

    -Sajan.

  26. Jeremiah Minifield says:

    On a weekly basis I use two excel windows to copy data from one file to a pivot table file. However, I was not aware of the “side-by-side” button on the View ribbon. What I’ve been doing since Excel 97 days is to open both files, then go to Window, then Arrange, then select Vertical or Horizontal. In Excel 2010, the sequence is View, then Arrange All, then select Vertical or Horizontal.

    I will be using the “side-by-side” button in the future. Thanks for the tip.

  27. Kalpesh Soni says:

    Very nice way to compare lists

  28. Ramachendir says:

    Hi

    Very useful information,
    sofar i am doing this by using toggle key
    Thanks of info
    Ramachendir

  29. Atul says:

    awesome….is it possible same in excel 2003?

  30. Sam says:

    I have been looking for hours for this and it’s so simply! THANK YOU! You saved me manually going through 4000 records :)

  31. Jim says:

    Thanks for sharing this tip.  You saved me a lot of trouble!

  32. Afshan says:

    great and thnx

  33. Christine says:

    I am using this brilliant method! However there are some items in my list across different columns that match exactly letter by letter but somehow is picked up and highlighted upon as ‘unique’??? Any help on this?

  34. Jitendra Mohanani says:

    Imagine there are three columns. A few cells match in the first two, a few in the second and the last, and a few in the first and the last. This function would let me know all the duplicates, but would not tell me the duplicates across any of the two columns. How to rectify this issue. any thoughts?

  35. Raj says:

    O Hi ,, 
    Thanks for the cool tip.. I wanted to open a web page inside an excel cell
    how could I do that

    So multiple cells I can use different pages …IS their a way 

  36. Souvik says:

    Excellent one it really helped a lot

  37. Ramboid says:

    Nice one!  I also really like the fast and neat keyboard shortcut version MiceMustDie sneaked in above:

    ___
    ALT + HLHD for highlighting duplicates
    or
    ALT + HLHDU for highlighting uniques

    ___

    For uniques, on my Vista I have to use ALT + HLHD, then U for highlighting uniques, ie without the ALT for the last keystroke.
     

  38. af says:

    OMG thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! I was too tired to understand anyone else’s directions on how to do this.  you just made my night less painful.

  39. Sujit says:

    Really helped, Thanks!

  40. Thanks a lot for this; you opened a wall I was hitting every day….. thanks a looooooooooooooottttttttttt

  41. C says:

    I have two lists, one in column A with over 2000 records, and one in column B with fewer than 200 records.

    When I select column A, and then CTRL select column B, and 

    Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values
    Press ok.

    ALL fields are highlighted in both columns–even the extra fields in Column A where there are no matches in column B. 

    WTFish? Did I misunderstand something? 

  42. C says:

    UPDATE

    That should read, “…column B with fewer than 2000 records.” 

  43. Sandeep says:

    Can someone help me out
    I am using this excellent idea but I need some customization in this. I want to highlight each duplicate value with different colours
    For example my numbers data has three duplicates i.e. 15, 20, 25
    I want to highlight 15 with blue, 20 with red and 25 with yellow

  44. farhad says:

    Gr8 help, thanks a lot for sharing…….
     

  45. frank says:

    I think it is really cool but it also highlights the duplicates in the single list alone… it does not just do cross reference…it does duplicates within the single column… How do we prevent it from highlighting additional duplicates on single column that are not on second list?

  46. csandford says:

    Worked beautifully for what I needed.  Thank you soooo much!

  47. Des says:

    Nice explanation

      

  48. Ravish says:

    this doesnt help much as it doesnt compare two list. it just finds out if the same number is repeated twice in ANY of the column and not in the second column. tried using randbetween() function in two columns. pls enlighten me if i m missing something. office 2003

  49. Ravish says:

    oh, just read it again. works only in office 2007+. apologies

  50. hihak says:

    great! thaNK YOU IT HELPED

  51. Ok WOW that was ridiculously easy. 

  52. petros says:

    Awesome stuff !!!! really appreciate it, now I can be awesome too !

  53. Caitlin says:

    YOU HAVE SAVED MY LIFE!!! THANK YOU!!!!!!!

  54. Jens says:

    damnnnnnnnn sooooo good.. saved me like 2 hours of work now :D thanks mate!

  55. Suresh says:

    Grt technique my friend..

  56. Paul says:

    This is one of the single most useful tricks I have ever seen shared.  Thank you guys!!!!

  57. S.K. Mitchell says:

    Thank you!  This just saved me a lot of time.

  58. Mr. Chu says:

    THANK YOU!!!!!!!

  59. god says:

    Very helpful, in stark contrast to the microsoft page, which just confused me 

  60. SatV says:

    Give that Man a C Gar…!!!

  61. Sanky says:

    That is an awesome tip..Thank you so much!!

  62. shyam says:

    very thank full to all i’m searched more over 100 sites but there is no easy answer to this finally i got it here. thank u so much
    i need the same in ms office 2003 also will u please post me

  63. Khursheed says:

    Hi
    I have 5 Different columns, I want to check each column one text available or not? Example. all the five cells text shows closed.
    Then I need in a separate cell value is closed, if not closed or any other text available there then pending text should show there .
    Can you help me.

    • Hui... says:

      @Khursheed

      What about something like:
      =If(counta(a1:a5)>0,”Available”, “Not Available”)

      But I suspect your problem is more complicated than that
      Can you better define what the problem is?

  64. Arao says:

    really really awesome .Thanks a ton

  65. David says:

    Thank you very much!!!!

  66. Nkululeko Mzamba says:

    Very Great Indeed. Made life easy!!@1

  67. James George says:

    Thank you, just what i was looking for. !!!

  68. Marie says:

    A million times THANK YOU!

  69. Yahia says:

    Great. Thank you for this very useful tip.

  70. STERILOGIX says:

    YOU ARE JUST A REAL DINOSAUR!

    THANKS, BRUNO

  71. santhosh says:

    Thanks alot :-)

  72. Bill G says:

    This is a very easy and quick way to identify (2) lists in Excel.

    Thanks for sharing the simple approach. Beats creating Visual Basic rules or =IF formulas !!

  73. RCR3 says:

    Someone showed me how to compare spreadsheets by using concatenate and v-lookup tools – there were about 20 steps involved – ugh! Using this method (along with concatenate) took only 5 steps – thank you, thank you for this awesome tip!

  74. RD says:

    Is there any way I can force Excel to take into account the casing for the text while comparing? For example; if in column A I have ‘ABC LLC’ and in B I have ‘ABC Llc’ ; then I do not want these two to be marked as dupes. Currently Excel is highlighting them as dupes.

  75. lovely says:

    Hi,
    Am using excel 2010.

    I have 2 lists of emails. Want to know which emails are there in List a that are not in list B and vis-a-versa. So that I in the end have 3 lists.
    1. the ids that are in both lists. 2. The ids that are in list a and not in b
    3. the ids that are in list b and not in a.
    The tip on conditional formatting has not worked.
    Any suggestions?

  76. Amy says:

    Oh my word!! Thank goodness I thought to search “how to compare two lists in Excel”!! I had a 7 page and a 43 page list that I needed to compare and had started off doing it by hand.

    You can also dictate what color you want the font and background colors to be by choosing the “custom format” option in the drop down box that has “red highlight” as the first option. I chose to highlight the unique items and chose an orange background with black text since I print on fast draft and wasn’t sure if the other choices would show up right.

    LIFE SAVER!!! Now that I’ve wasted almost 2 hours doing it the other way, I’m able to print out the lists together and get to work. :)

  77. jel888 says:

    Thanks! Worked like a charm in Excel 2010!

  78. Sally says:

    THANK YOU!!

  79. Ishan says:

    Thanks a lot this made my life much easier.

  80. ravin says:

    This function has a catch…it also selects duplicate values in the same list as well. For example if list one has Amanda mentioned twice than Amanda will be selected irrespective of its existence in 2nd list

  81. Khalid NGO says:

    Hi Chandoo,
    Very nice tip for duplicate values.

    I love to use CF.

  82. Nishad says:

    Thanks , its very useful function, please help me on this i have those values in separate two worksheet, with this same function its work out.

  83. Jasvinder says:

    Just awesome. I used to write Vlookup or match to find. this one is much faster.

  84. birendra says:

    your thoughts are always different…..

    amazing ways !!!

  85. Mukunth says:

    How to do it when the lists are on two different workbooks?

  86. Raghu Prasada Rao says:

    Nice trick, it saves a lot of time. Thanks

Leave a Reply